Create Table with Incrementing Numbers in Repeating Column Headers

Ashley Moose

Background: When exporting 1:M fields, programs like Raiser's Edge and BBCRM will automatically add numbers to column headers to indicate different sets of fields, i.e. SolicitorName1, SolicitorID1, SolicitorName2, SolicitorID2. If you are using Power Automate to extract the data, there may be scenarios where you need to produce similar column headers, such as producing files for 3rd party vendors. The exact number of each instance may not be known so individual column values cannot be set. Instead column headers need to increment based on the max number of instances for a single record.

Set-up

Create 4 variables

• 1 array variable - Used as the final table/data holder
• 2 object variables - Used to store the data before adding to the array variable
  • 1 temp (in screen shots below, mine is called objectTemp)
  • 1 main (in screen shots below, mine is called objectInteractions)
• 1 integer variable - To "count" the number of instances of the field that will be used in the column headers

Steps

1. Use a Set variable action to add all non-repeating columns to main object variable, formatted as an array.

2. Add an Apply to each action to get the repeating columns

a. Start by incrementing the integer variable by 1 - this is the number value that will be added to the column header

b. Then use a Set variable action to add the repeating column to the tempObject

Expression = addProperty(variables('objectInteractions'), concat('Solicitor', variables('countSolicitor'), 'Name'), outputs('Get_a_fundraiser')?['body/name'])

• variables('objectInteractions') = main object variable
• concat('Solicitor', variables('countSolicitor'), 'Name') = column header
• outputs('Get_a_fundraiser')?['body/name'] = column value

c. Use another Set variable action to set the main Object variable with the tempObject variable

d. Repeat steps b and c inside the Apply to each action for any other repeating columns

3. Outside of the Apply to each action, use an Append to array variable action to add the mainObject variable to the array variable

4. Still outside of the Apply to each, use a Set variable action to reset the Integer variable back to 0

5. Use the Array variable in a create csv table action (or however else) to create the table

And Voila! The column headers increment up to the max number of instances for a single record!

Heather McLean

@Ashley Moose - just making sure I'm following here. This is Power Automate producing parity with a file export method where simply renaming the column headers would cause disruption to a downstream process, like a mailhouse?

Ashley Moose

@Heather McLean
Pretty close, but it's not as much about renaming column headers as producing column headers with the correct names. If I were exporting OOB, the system would add a number to each set of fields to indicate the fields belong together (i.e. Solicitor1Name and Solicitor1ID all belong to Solicitor A, but Solicitor2Name and Solicitor2ID belong to Solicitor .

In this particular use case a file was being exported but then needed to be manually manipulated to get the file in the correct format for the vendor. All of the needed data points were available through the SKY API endpoints though so instead of exporting the file and then using Power Automate to manipulate the file, we decided to just pull everything through Power Automate. When producing the repeating column headers, I could have set a maximum number to pull (i.e. 5) and set Solicitor1Name, Solicitor1ID,….,Solicitor5Name, Solicitor5ID, but if there were more than 5 solicitors, I would have missed those records, and if there were less, than I'm producing unnecessary blank columns (both of which could be fine, but this method allows it to be dynamic so no records are missed and no extra blank columns are created).