Lookup elements from JSON input based on condition then populate array

Good afternoon,

I have to populate an excel template (that is standardized and can't be altered) via Plumsail's connector

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

BSRM - Template - Community Version.xlsx (38.8 KB)

Hello @atosi,

I'm not sure that I understand the task properly. As I understand the excel template is filled incorrectly? Could you share a screenshot with Plumsail action in Edit mode as well? What should be the correct result?

Best regards,
Petr Bushuev
Plumsail team

Hi Petr,

Yes, 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",
}
]

Hello, @atosi

  1. As I understand, you pass to the "Apply to each (RecrodsToSave)" block the output of the "Parse JSON" action. The latter is an array of objects.

  2. In this block, each iteration uses just one object from the initial array. This means that the "FC" properties in "1.01Foundations...", "1.02Roofs", etc. of the "Compose" action has the same value. Also, notice that you didn't create an array of objects - it should be enclosed in square brackets.

  3. In the "Apply to each" block you used the "Set variable" action and that means that the array variable is rewritten on each iteration instead of accumulating of objects. You should use an "Append to array variable" action and pass to it just a single object which will be an item in the array for Plumsail Documents template data.

  4. Here is an example of passed data and the result that could be:



  5. So first of all check the input that you passed to the Plumsail Documents action. Please be guided by the principle explained in this article.

Hi @Evgeniy,

Thank you for your response, it is very much appreciated! I've incorporated parts of the solutions you've mentioned however i'm still experiencing the repeating issue.

I presume my problem revolves around the unique object name within the "Compose". As you can see in the first image, the values are populated in the template.

The second image shows why I believe i'm getting repeated values. In each instance, my four elements are the exact same ("items('Apply_to_each_(RecordsToSave)')['Functional Control'].

Within the third image, here, the 'apply to each' has 129 instances where it loops (which is correct).

***So far I have no ability to define which element belongs to that specific object.

I need to create a 'Compose' where it looks at "1.01Foundations..." and pulls the four elements of "FC","OM","OM10k" and "Cap10k". Then in the 2nd loop, look at "1.02 Roofs" and pull again the four elements relating to "1.02" roofs and do until 129 objects are filled accordingly. Example shown below:

{
"items":[
{ "lookup into JSON array for first unique name (ex: 1.01Foundations...)":{

  •          "FC": "FC-1.01",*
    
  •          "OM": "OM-1.01",*
    
  •          "OM10k": "OM10k-1.01",*
    
  •          "Cap10k": "Cap10k-1.01"*
    

}
THEN LOOP TO NEXT unique name (1.02, 1.03, 1.04, etc) and fill out the elements accordingly.

What i'm struggling with is how do i define tell the apply to each to lookup 1.01, and bring those unique four elements relating to that number, then loop to the next object and do the next elements accordingly and so on?

Thanks again, @Evgeniy!

Regards,

Anthony

Here is an approach to restructure your array of objects to a metaobject.

  1. I have parsed your sample array of objects:

  2. Then initialized an array variable to collect metaobject properties which will contain objects with the 4 properties:

  3. To an "Apply to each" block, I passed the output of the "Parse JSON" action (an array of objects) and compose a property of the metaobject with a value which is an object containing the 4 properties:

  4. Use this expression to compose a property string as in your original Excel template:

concat(items('Apply_to_each')['Component or Service Number'], items('Apply_to_each')['Component or Service'])
  1. Then I appended the composed property to the array of the metaobject:

  2. Create a metaobject string using this expression (it joins the array items into one string separating them by comas and enclosing by braces):

concat('{', join(variables('objects'), ','), '}')

  1. Here's the result that the variable contains:
{"1.01Foundations/Structural components": {
    "FC": "IO-1.01",
    "OM": "IO-1.01",
    "OM10k": "IO-1.01",
    "Cap10k": "IO-1.01"
},"1.02Roofs": {
    "FC": "IO-1.02",
    "OM": "IO-1.02",
    "OM10k": "IO-1.02",
    "Cap10k": "IO-1.02"
}}
  1. Use it with your original template.

1 Like

Thank you so much @Evgeniy, this works!!

1 Like

I am glad to help. =)

2 Likes