Display SharePoint Multi Value Fields in one Cell

JoshMohr · October 19, 2018, 11:32am

Hey Guys,

I’m trying to create an XLSX File out of a SharePoint List. My Problem here is im trying to get the choice fields in one cell like its displayed in SharePoint.

This is an Example Array Item:

{
  "Product title": "Junior Sagaland",
  "Article Number": "21372 6 ",
  "Format": "Landscape format",
  "Box size": "SG 40",
  "Priority": "Prio Packaging + Content",
  "Technology": [
    {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 0,
      "Value": "no power needed"
    }
  ],
  "Display unit": null,
  "Animation": [],
  "VKK": null,
  "Marketing support": [],
  "New": null,
  "Availability": null,
  "Segment": "Children games",
  "Responsible IPM": "Sugg, Franziska",
  "Subsidiary": "RSV",
  "Availability-Status Packaging": "–",
  "Availability-Status Decoration": [],
  "Dimensions Content": null,
  "Licence note": null,
  "Silent Seller": null,
  "Product focus": null,
  "Status List": "in process",
  "Quarter": null,
  "Delete": false
}

I want the technology field displayed for each item in one cell. There could be multiple values or none.
Can anyone help me how to achieve this?

kirill_sh · October 22, 2018, 4:00pm

Hello, @JoshMohr.

Please try adding all three properties {{technology.@odata.type}}, {{technology.Id}} and {{technology.Value}} in one cell, like described in the example here.

Best regards, Kirill Shaklein

JoshMohr · October 23, 2018, 7:23am

Hey @kirill_sh,
first thanks for taking time to help me. Maybe I wasn’t clear enough. What I want to achieve is that if there are multiple choices selected in one field e.g. the Technology field, I want all the Values in one field. I’m not interested in odata.type or Id just the values. So for example Technology could look like this (Two values checked in a choice field):

"Technology": [
    {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 0,
      "Value": "no power needed"
    },
    {
      "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
      "Id": 1,
      "Value": "sound"
    }
  ],

So now I want it displayed in one cell as “no power needed, sound” just like its displayed in the SharePoint list. I know I can create an String in Flow and display this in the excel but the performance is really bad. So I’m hoping there is a way I can achieve this through the template file. I’m also open for other workarounds which might be more performant.

Thanks,

Josh

kirill_sh · October 29, 2018, 6:34pm

Hello, @JoshMohr.

In that case you can use formaters. Here’s information about them.
For example, like this:
{{values}:join(, )},
where values is an array of values for each item in ‘Technology’.

Best regards, Kirill Shaklein.

Hugh.lawson · April 8, 2021, 4:55pm

Hi I the same issue above and I have tried the join formatter but I get the output
P, r, o, p, e, r, t, y, , c, o, n, t, i, n, u, o, u, s, l, y, , o, c, c, u, p, i, e, d, , d, u, r, i, n, g, , o, p, e, n, i, n, g, , h, o, u, r, s, , s, o, , n, o, , s, p, e, c, i, f, i, c, , t, a, m, p, e, r, , r, e, q, u, i, r, e, m, e, n, t, s

I have a sharepoint choice field which can have multiple values, called RA - Resilience

Using the Create DOCX document from template, I have the line
"RA – Resilience":@{triggerBody()?['RA_x002d_Resillience']}

On the Word Document I have {{RA - Resilience.Value}:join(, )}

The trouble is the output is putting the comma and space between all the letters of the first entry only rather than joining all the different values. It only uses the first option

Here is the sharepoint item it is using -
[
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 0,
"Value": "Property continuously occupied during opening hours so no specific tamper requirements"
},
{
"@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
"Id": 1,
"Value": "Property would remain open during short power outages; consider the use of a UPS for outages greater than 30 minutes"
}
]

Please help. Ive been a this for hours

Petr · April 9, 2021, 3:47am

Hello @Hugh.lawson,

Please try the tag without join formatter. Like this

Best regards,
Petr
Plumsail team

Hugh.lawson · April 9, 2021, 11:26am

It only shows the first value.

I have noticed that it generates a second document on the same word file

Petr · April 9, 2021, 11:42am

Hello @Hugh.lawson,

Could you also share your DOCX template and the whole JSON please? I'll try to reproduce it.

Best regards,
Petr
Plumsail team

Hugh.lawson · April 9, 2021, 12:01pm

I have changed the docx document to remove the field as I am getting no-where with it.
I have been trying to create a form using plumsail forms with limited success
I am beginning to think plumsail isnt for us

Proposal for CCTV System v1-0.docx (124.7 KB)

{

"body": {
    "@odata.etag": "\"1\"",
    "ItemInternalId": "25",
    "ID": 25,
    "Title": "dsfgsdfgsd",
    "Status": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Proposal"
    },
    "Status#Id": 0,
    "SiteName": "fgsdfdgf",
    "SiteAddress": "dfsgdfsgdsafgdsgfdsszagdf",
    "SystemPurpose": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 3,
        "Value": "Recognition of Individuals"
    },
    "SystemPurpose#Id": 3,
    "DesignerName": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 5,
        "Value": "Hugh Lawson"
    },
    "DesignerName#Id": 5,
    "CameraNumber": 12,
    "NumberofDays": 23,
    "LuxMeasurement": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "Moonless, overcast night sky (0.0001 lux)"
    },
    "LuxMeasurement#Id": 0,
    "CameraPower": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "All Cameras will be powered by Power Over Ethernet (POE)"
    },
    "CameraPower#Id": 0,
    "Resolution": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 8,
        "Value": "1080p FULL HD (1920 x 1080 pixels)"
    },
    "Resolution#Id": 8,
    "HardDriveCapacity": 23,
    "Audio": {
        "@odata.type": "#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference",
        "Id": 0,
        "Value": "No audio requirements stated by user. (Cameras will have any audio capability turned off during commissioning)"
    },
    "Audio#Id": 0,
    "Devices": "[{\"Number\":1,\"Name\":\"Front

Hugh.lawson · April 10, 2021, 10:07am

Is there any progress here. I have tried another flow and document.
I am on a tight deadline and I have spent 3 days on this

Data inside Create DOCX from Template
"RA - Risk Assessment":[{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":1,"Value":"Potential assaults against staff"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":2,"Value":"Theft of cash"},{"@odata.type":"#Microsoft.Azure.Connectors.SharePoint.SPListExpandedReference","Id":3,"Value":"Theft of higher value items"}],

Merge link in docx file
{{RA - Risk Assessment.Value}:join(, )}

Output
P, o, t, e, n, t, i, a, l, , a, s, s, a, u, l, t, s, , a, g, a, i, n, s, t, , s, t, a, f, f