Unanswered

Lookup elements from JSON input based on condition then populate array

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Good afternoon,

I have to populate an excel template (that is standardized and can't be altered) via Plumsail's connector (Create XLSX from Template). Link to Plumsail's service tutorial for create from xlsx template:

https://plumsail.com/docs/documents/v1.x/flow/how-tos/documents/create-xlsx-from-template.html

What I have so far is JSON input from Powerapps, of all the changes I'd like to populate the excel template with. I'm able to populate the template so far, with changes (elements in JSON input), however, I'm only able to populate the values with the repeated elements. I presume this is because I haven't mapped out based on a condition. The JSON input has 129 objects, with 129 unique component numbers and it's corresponding data.

In the excel spreadsheet, there is a matrix that I have to populate these values with. Across 4 common columns, there are 11 "systems", which contain 'x' number of 'components' for a total of '129' unique rows of data. Each component has a unique number (e.g. 1.01, 1.02, 3.01, 6.10, etc.)

I'm looking for a solution where I can map/lookup out these elements (based on if it's a specific component#) and put it in a Compose (formatted as an array - as this is Plumsail's requirement). In the attached spreadsheet, you'll see that I've formatted the values to equate to the compose array names.

I'd appreciate any feedback. If you need more detail, please let me know!

Cheers,

Anthony

Excel Template, formatted cell in yellow (there are 129 rows of data across four columns)Start of Flow, take the input of json from PowerappsUse a select to trim out unnecessary data - may not be actually requiredCompose used to populate Excel template using Plumsail's connector - this format is requiredExample of system 1's components and pulling the 'repeated values' as i don't currently have it mapped to that particular component

This is the output of the template, however as noted, it is repeating returned values as i did not map them corresponding to the unique component number

Categories:

Building flows

I have the same question (0)

All responses (3)

Answers (0)

Sort by

v-alzhan-msft Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @,Anthony_To Sorry for I couldn't see the picture that you attached, I would reply here once I could see the picture and make a test. Best regards, Alice Community Support Team _ Alice Zhang If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-alzhan-msft ,

Thank you for your response!

Please see the images attached.

Excel Template that i need to populate with JSON DataJSON input is parsed (Select may be disregarded as it's not necessary)Get excel template, and initialize variable to store array for Plumsail ConnectorUsing Parsed JSON data, fill out templateHere is where the error occurs. I'm struggling with defining that each particular component(Unique) gets mapped to its corresponding place in the templateMy finished product is it'll repeat the four values across the template. Rather than inputting the correct one into the matrix.

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like (0)

Report
Copy link

Link copied!

Hi @v-alzhan-msft

Just for further clarification...

The excel template is filled incorrectly as I have not mapped each value correctly to the position of the matrix.

With the input JSON data to the Flow, I have 129 objects (similar to the 2 objects shown below) within an array that have 4 unique elements (bolded) corresponding to each of the four columns ("Functional Control", "Operations & Maintenance Resp", "O&M < $10k" and "Capital > $10k", in the excel template).

So far, the "Compose" I've written, points to the correct position in the excel template, but not corresponding to the unique component ID (See their unique position in the second image). If you look in the below JSON array, each object has a unique component number (ID, which I have bolded).
[
{
"B Number": "B17155",
"Building Name": "New Implement Storage",
"Building Usage": "STORAGE B",
"Capital more than 10K": "IO",
"Component or Service": "Foundations/Structural components",
"Component or Service Number": 1.01,
"ComponentNamNum": "1.01 Foundations/Structural components",
"Functional Control": "IO",
"O&M less than 10k": "IO",
"Operations & Maintenance Resp": "IO",
"System Name and Number": "1 - BUILDING OR ENGINEERING STRUCTURES",
},
{
"B Number": "B17155",
"Building Name": "New Implement Storage",
"Building Usage": "STORAGE B",
"Capital more than 10K": "IO",
"Component or Service": "Roofs",
"Component or Service Number": 1.02,
"ComponentNamNum": "1.02 Roofs",
"Functional Control": "IO",
"O&M less than 10k": "IO",
"Operations & Maintenance Resp": "IO",
"System Name and Number": "1 - BUILDING OR ENGINEERING STRUCTURES",
}
]

Excel Template
Compose . Referencing array elements (that aren't mapped to their unique component number - hence the repeating issue

Was this reply helpful? Yes No