Is it possible to return a delimited string of Column Title and Value for a given Sharepoint List item?

[DizzyAy]

Hi,

I have a sharepoint list with columns similar to the below

ID	Name	Question 1	Qu 2	Qu3
1	Johnny Bravo	1	1	4
2	Barbie	2	1	5

Ultimately I want to get this information into powerapps, without using a gallery. Is it possible to produce a delimited text string which i can them manipulate in PowerApps? A result would be something like:
"ID: 2, Name: Barbie, Question 1: 2, etc" or;

"ID: 2, Name, Barbie, Question 1, 2, etc"

Even two separate strings may work as long as same numbe rof separators.

Thanks

[DizzyAy]

Note: I have tried using the http response action and parsing the JSON but i seem to be getting the backend column names and not the display names

[Expiscornovus]

Hi @DizzyAy,

You could use a Select to only select the columns you want to use (and potentially rename their labels as well). After that you can use the outputs of that action in a Create CSV table action. That should give you comma separated values per item on each line.

Below is an example

[DizzyAy]

Hi @Expiscornovus ,

I assume this solution would always need tweaking if my columns were to change or new columns added? Would it be possible to use something like "apply to each" value to grab every column no matter how many there are?

[Expiscornovus]

Hi @DizzyAy,

Yes, in that case remove the Select action and use the value field of the Get Items action directly in the From field of the Create CSV table action. 

[DizzyAy]

Ahh much simpler than i was expecting, great! Is there a way to retrieve the display names of the columns instead of the actual backend names though?

[Expiscornovus]

Hi @DizzyAy,

Yes, that would be possible but that would make the flow a lot more complex. In that case you probably want to use the Fields method to retrieve the internalname and title of the fields. This way you can use that in a loop to replace in the CSV content. I would use a couple of variables for that.

Below is an example

The replace expression is an compose:

replace(variables('CSVReplaced'), item()['internalname'], item()['title'])

[DizzyAy]

@Expiscornovus 
Could you share the earlier steps of the flow to ensure I understand correctly. Current I am receiving this error from my Apply to Each step:
Unable to process template language expressions for action 'Apply_to_each_2' at line '0' and column '0': 'The template language expression 'outputs('Send_an_HTTP_request_to_SharePoint')['body']['value']' cannot be evaluated because property 'value' doesn't exist, available properties are 'd'. Please see https://aka.ms/logicexpressions for usage details.'.

However if i change it to 'd' then I get an error informing me that 'd' is an object not an array

[Expiscornovus]

Hi @DizzyAy,

Have you used the same Headers in your Send an HTTP request to SharePoint action?

Make sure you used the odata=nometadata ones for both the Accept and Content-Type key values, like in my shared example.

Otherwise the outputs will return the 'd' property instead of the 'value' property.

[DizzyAy]

@ExpiscornovusThat does indeed work, i had a typo in the syntax. However the final output is still pulling the "Odataxxxx" type name. Screenshots to demonstrate:

[Expiscornovus]

Hi @DizzyAy,

You also need to include the set variable action. Otherwise each replace loop result will get lost and basically only the last replace loop will be used. You need to temporarily store the compose outputs somewhere, in this case the variable. Please include the set variable action with the outputs of the compose in the value field as well.

And not all column names will be replaced with a new label by this approach. Some are system columns which have the same displayname as internal name.

[DizzyAy]

Hi @Expiscornovus 

I have added the set variable step (seems obvious now!) but interestingly this spits out an error that wasnt present before:

On your second point - understood. It would be lovely if all columns had the same titles!

[Expiscornovus]

Hi @DizzyAy,

I see your expression is using the body twice. You only need to refer to it once.

Try this instead:

body('Send_an_HTTP_request_to_SharePoint')['value']

[DizzyAy]

@Expiscornovus 

I have made those changes and yet it is still returning the internal Odata title. Could you share all of your flow steps so I can see exactly where I am going wrong?

Thanks

[Expiscornovus]

Hi @DizzyAy,

Apologies, if it was not clear. But like I mentioned, some fields cannot be replaced with the second approach I shared. They don't have a display name because they are system fields.

Below is a screenshot to demonstrate that the odata.etag fields is not listed in the outputs of the Send an HTTP request and it that case also won't be replace with a new displayname.

In that case I would suggest to use the first approach, with the Select action.