how to grab all column names using a string concatenated variable that changes on every loop iteration

[dperez13]

I have a sharepoint online list with a bunch of choice column types. I need to grab all of those that are 'false', (which I dont imagine is too hard to do, but I need to do that step first for the trickier step 2: so,  in addition to that , I need to grab all their corresponding 'explanation' columns (text columns). The one good thing about the data is that each column thats a choice of true or false, also has a corresponding column attached to it with identical wording. So question 1(true or false) has a question 1 explanation column attached to it.

so my spo column field data looks like this

question 1, question 1 explanation, question 2, question 2 explanation

So my idea was to make a for loop in power automate that finds the column name where the value is false/no/whatever and then take that column name, add a " " and a "explanation" after it using some string manipulation, and than find the corresponding column with that variable name

so the psuedo logic would look like this:

For each column where value = whatever

columnToGrab = column name & " explanation"

map?select? : columntoGrab value

The last  line are what im not sure how to do ,what activity would I use to grab that columnToGrab variable each time in a loop?

-thanks

[dperez13]

heres what I have

 xpath(outputs('XML'),'//root/value/*[ends-with(text(),"XX")]')

 im trying to grab all values that end with XX

[grantjenkins]

Ok, so I'm hoping this will get what you're after. The trick was to retrieve both the Internal Name and Display Name (Title) of the columns in SharePoint and match them to get the values we're after.

I added a couple more columns to my list for testing purposes.

field_001 - Question 4

field_002 - Question 4 Explanation

See full flow below. I'll go into each of the actions.

Get items retrieves all the items from the list.

XML converts the outputs to XML so we can use XPath. The expression used is:

xml(json(concat('{"root": { value:', outputs('Get_items')?['body/value'], '}}')))

Get fields uses a Send an HTTP request to SharePoint to retrieve the fields including the Internal Name and Title. The Uri is below. Note that you will need to put the Title of your List.

//You would just need to put the title of your list in place of Questions

/_api/web/lists/getbytitle('Questions')/fields?$select=InternalName,Title

Select fields uses the output from Get fields and two expressions to get the Internal Name and Title. The expressions used are:

//From (input)
body('Get_fields')?['d/results']

InternalName
item()?['InternalName']

//Title
item()?['Title']

XML fields converts the output from Select fields to XML so we can use XPath. The expression used is:

xml(json(concat('{"root": { value:', body('Select_fields'), '}}')))

Apply to each iterates over each of the items returned from Get items.

Names extracts the fields for the current item where the Value is equal to No. I originally had this as Yes, but after reading what you were after again, I think I should have put No. The expression used is:

xpath(outputs('XML'), concat('//value[ID="', items('Apply_to_each')?['ID'], '"]/*[starts-with(./Value, "No")]'))

Select Names uses the output from Names and uses the following expressions to get the Internal Name and Title of the current field, and the Title of the corresponding Explanation field.

//InternalName
xpath(item(), 'name(//*)')

//Title
xpath(outputs('XML_fields'), concat('string(//value[InternalName="', xpath(item(), 'name(//*)'), '"]/Title/text())'))

//Title Explanation
concat(xpath(outputs('XML_fields'), concat('string(//value[InternalName="', xpath(item(), 'name(//*)'), '"]/Title/text())')), ' Explanation')

Finally, Select will output the Internal Name, Title and Value of each Explanation field. It uses the output from Select Names and the following expressions to get the Internal Name, Title and Value.

//Internal Name
xpath(outputs('XML_fields'), concat('string(//value[Title="', item()?['Title Explanation'], '"]/InternalName/text())'))

//Title
concat(item()?['Title'], ' Explanation')

//Value
items('Apply_to_each')?[xpath(outputs('XML_fields'), concat('string(//value[Title="', item()?['Title Explanation'], '"]/InternalName/text())'))]

Some sample output is:

[
  {
    "Internal Name": "Question2Explanation",
    "Title": "Question 2 Explanation",
    "Value": "Test 001 - Question 2 Explanation"
  },
  {
    "Internal Name": "field_002",
    "Title": "Question 4 Explanation",
    "Value": "Test 001 - Question 4 Explanation"
  }
]

[dperez13]

nevermind, thanks, i cant wait to try this out. i was reading over it just to make sure I got most of it.  is there some documentation that explains what 'd' does in  the http request?

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/complete-basic-operations-using-sharepoi... 

https://learn.microsoft.com/en-us/sharepoint/dev/sp-add-ins/get-to-know-the-sharepoint-rest-service?...

https://learn.microsoft.com/en-us/previous-versions/office/developer/sharepoint-rest-reference/jj860...

i tried searching here to no avail. im really curious to see this loop over all the field values

[grantjenkins]

The flow uses both XML and XML fields when using XPath expressions. Names uses XML and Select Names and Select both use XML fields.

[dperez13]

is the purpose of converting to json just so we can covert to xml? when you send an http request to sharepoint? how do you get the data, do you not get it as json format? just wondering why you can directly convert the http request api pull into xml , but I guess the answer is that the http request doesnt GET the data in json format?

[Alucard_0079]

is it possible to do that without loop 'For Each'?  consider there are thousands of items to transform, it will cost long time to loop every items.