I am trying to automate a solution up update a field on our forms tool when data in a table changes. The full plan is when the sql table changes query the table, somehow convert the table into a hierarchy which I then send to the forms tool via HTTP tool. I haven't yet got my gateway setup to work on the sql part but I am hoping it is fairly straight forward. In the mean time I have been working with an excel file of the same data on our sharepoint. I have gotten the HTTP tool to update the form question with whatever data I send to it.
My issue is I need the data in a specific format for the form tool question to properly use it. Currently the sql table/excel file has many columns but I only need 3 of them, region, building and client. So it will be similar to the screenshot below but with more rows and more specific data.
The formatting I need for the form question is kind of 2 parts. As i mentioned in the title I need it in a hierarchy similar to the screenshot below. I have exaggerated it with tabs for easier readability whereas in actual use Regions are not indented, Sites are indented with 1 space and Customers are indented with 2 space. I have to use some specific UTF-8 I think coding to get new lines and spaces.
So the actual end goal would look similar to this. Depending on how I get the data into the hierarchy I think I can manage concats for adding in the other code needed to format it properly. I just can't figure out how to get 1 region with multiple sites under it with multiple customers under them
For now we only have 3 regions and I think it will be ok to populate them manually. For instance I initiated a variable with %0DRegion 1 and then did a for each on the Site with an append to string variable. And that works as expected but isn't the format I want as it lists each site multiple times. And I'm still not sure how to handle the customers either although I suspect once I figure out how to handle the sites, the customers will be handled similarly if not perhaps at the same time. I was thinking maybe a do until on the append to string variable with the until being until site doesn't match site, but I'm not positive how the do until looks at the data. I'd need it to be until previous item site doesn't match current item site and I'm not sure if it can do that. Which reminds me, I did do a select to get each of the rows into its own output.
Here is my current flow. This current excel file I am working with is just Region 1. I was originally using the full data set with all 3 regions but I figured it would be easier to figure out how to do it with one region at a time. If I can figure that out I should be able to append each region to get my final output. As I mentioned previously the output of this flow is something like %0DRegion 1%0DSite 1%0DSite 1(because site 1 has multiple customers it has multiple rows in the data). So I need to get a unique site while also grabbing all the customers for that site.
