Loop thru array and create row

Queries and Resources
1

Hello all!

I am hoping someone can assist with a solution to an issue I am having displaying a nested array from an API response that creates new row in my table. I tried to format data as an object but I was having issues along with transforming the data.

A sample response from my API is listed below. In the response there are multiple object arrays but one of the key/value pairs in the "Table Items" array has a key named "rows" and sometimes that can have 1 value in the response or multiple values as shown below. Essentially I want each item in the "rows" array to be a separate row in my table. with all the other keys being duplicated.

{
    "data": [
        {
            "body": {
                "objects": [
                    {
                        "name": "Store #",
                        "objectType": "field",
                        "value": "314",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Invoice #",
                        "objectType": "field",
                        "value": "10093230",
                        "pageIndex": 0,
                        "rectangle": [
                            467.625,
                            136.125,
                            133.125,
                            58.125
                        ]
                    },
                    {
                        "name": "Invoice Date",
                        "objectType": "field",
                        "value": "3/22/24",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Order #",
                        "objectType": "field",
                        "value": "1"
                    },
                    {
                        "name": "Customer PO",
                        "objectType": "field",
                        "value": "15006663-1758885",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Vendor",
                        "objectType": "field",
                        "value": "WHIRLPOOL\n               CORPORATION",
                        "pageIndex": 0,
                        "rectangle": [
                            28.125,
                            214.5,
                            323.25,
                            123
                        ]
                    },
                    {
                        "objectType": "table",
                        "name": "Table Items",
                        "rows": [
                            {
                                "no": {
                                    "pageIndex": 0,
                                    "value": "1"
                                },
                                "item": {
                                    "pageIndex": 0,
                                    "value": "MVW4505MW"
                                },
                                "description": {
                                    "pageIndex": 0,
                                    "value": "Top Load Washer with Deep Fill - 4.5 cu. ft. cu. ft."
                                },
                                "quantity": {
                                    "pageIndex": 0,
                                    "value": "2"
                                },
                                "price": {
                                    "pageIndex": 0,
                                    "value": "456"
                                },
                                "amount": {
                                    "pageIndex": 0,
                                    "value": "912.00"
                                }
                            },
                            {
                                "no": {
                                    "pageIndex": 0,
                                    "value": "2"
                                },
                                "item": {
                                    "pageIndex": 0,
                                    "value": "NTW4516FW"
                                },
                                "description": {
                                    "pageIndex": 0,
                                    "value": "AMANA 3.5 CF. HE TOP LOAD 8 CYCLES WHITE CYCLES WHITE"
                                },
                                "quantity": {
                                    "pageIndex": 0,
                                    "value": "4"
                                },
                                "price": {
                                    "pageIndex": 0,
                                    "value": "323"
                                },
                                "amount": {
                                    "pageIndex": 0,
                                    "value": "1,292.00"
                                }
                            }
                        ]
                    },
                    {
                        "name": "Tax",
                        "objectType": "field",
                        "value": "0.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Final Total",
                        "objectType": "field",
                        "value": "2,204.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Discount Amount",
                        "objectType": "field",
                        "value": ""
                    },
                    {
                        "name": "New Total",
                        "objectType": "field",
                        "value": "2,204.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    }
                ],
                "elapsed": 0.0518657,
                "templateName": "Sample Template",
                "templateVersion": "4",
                "timestamp": "2024-05-01T03:13:37"
            },
            "pageCount": 1,
            "error": false,
            "status": 200,
            "name": "64f679fb-4aad-43d2-89dd-3286660c895b_page1.json",
            "credits": 42,
            "remainingCredits": 17813,
            "duration": 111
        },
        {
            "body": {
                "objects": [
                    {
                        "name": "Store #",
                        "objectType": "field",
                        "value": "314",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Invoice #",
                        "objectType": "field",
                        "value": "10093231",
                        "pageIndex": 0,
                        "rectangle": [
                            467.625,
                            136.125,
                            133.125,
                            58.125
                        ]
                    },
                    {
                        "name": "Invoice Date",
                        "objectType": "field",
                        "value": "3/22/24",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Order #",
                        "objectType": "field",
                        "value": "1"
                    },
                    {
                        "name": "Customer PO",
                        "objectType": "field",
                        "value": "15006671-17595007",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Vendor",
                        "objectType": "field",
                        "value": "ASHLEY\n                FURNITURE\n                 INDUSTRIES",
                        "pageIndex": 0,
                        "rectangle": [
                            28.125,
                            214.5,
                            323.25,
                            123
                        ]
                    },
                    {
                        "objectType": "table",
                        "name": "Table Items",
                        "rows": [
                            {
                                "no": {
                                    "pageIndex": 0,
                                    "value": "1"
                                },
                                "item": {
                                    "pageIndex": 0,
                                    "value": "83905U1"
                                },
                                "description": {
                                    "pageIndex": 0,
                                    "value": "Signature Design by Ashley Abinger Sofa and Loveseat- Smoke Sofa and Loveseat- Smoke"
                                },
                                "quantity": {
                                    "pageIndex": 0,
                                    "value": "1"
                                },
                                "price": {
                                    "pageIndex": 0,
                                    "value": "495.42"
                                },
                                "amount": {
                                    "pageIndex": 0,
                                    "value": "495.42"
                                }
                            },
                            {
                                "no": {
                                    "pageIndex": 0,
                                    "value": "2"
                                },
                                "item": {
                                    "pageIndex": 0,
                                    "value": "W446W16"
                                },
                                "description": {
                                    "pageIndex": 0,
                                    "value": "Signature Design by Ashley Trinell 72 TV Stand with Electric Fireplace-B TV Stand with Electric Fireplace-B"
                                },
                                "quantity": {
                                    "pageIndex": 0,
                                    "value": "1"
                                },
                                "price": {
                                    "pageIndex": 0,
                                    "value": "373.82"
                                },
                                "amount": {
                                    "pageIndex": 0,
                                    "value": "373.82"
                                }
                            }
                        ]
                    },
                    {
                        "name": "Tax",
                        "objectType": "field",
                        "value": "0.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Final Total",
                        "objectType": "field",
                        "value": "869.24",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Discount Amount",
                        "objectType": "field",
                        "value": ""
                    },
                    {
                        "name": "New Total",
                        "objectType": "field",
                        "value": "869.24",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    }
                ],
                "elapsed": 0.0526737,
                "templateName": "Sample Template",
                "templateVersion": "4",
                "timestamp": "2024-05-01T03:13:37"
            },
            "pageCount": 1,
            "error": false,
            "status": 200,
            "name": "64f679fb-4aad-43d2-89dd-3286660c895b_page2.json",
            "credits": 42,
            "remainingCredits": 17813,
            "duration": 121
        },
        {
            "body": {
                "objects": [
                    {
                        "name": "Store #",
                        "objectType": "field",
                        "value": "314",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Invoice #",
                        "objectType": "field",
                        "value": "10093231",
                        "pageIndex": 0,
                        "rectangle": [
                            467.625,
                            136.125,
                            133.125,
                            58.125
                        ]
                    },
                    {
                        "name": "Invoice Date",
                        "objectType": "field",
                        "value": "3/22/24",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Order #",
                        "objectType": "field",
                        "value": "1"
                    },
                    {
                        "name": "Customer PO",
                        "objectType": "field",
                        "value": "15006674-17588857",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Vendor",
                        "objectType": "field",
                        "value": "WHIRLPOOL\n               CORPORATION",
                        "pageIndex": 0,
                        "rectangle": [
                            28.125,
                            214.5,
                            323.25,
                            123
                        ]
                    },
                    {
                        "objectType": "table",
                        "name": "Table Items",
                        "rows": [
                            {
                                "no": {
                                    "pageIndex": 0,
                                    "value": "1"
                                },
                                "item": {
                                    "pageIndex": 0,
                                    "value": "WPL6157EBPR"
                                },
                                "description": {
                                    "pageIndex": 0,
                                    "value": "Whirlpool Volcano Black 5.3 Cu. Ft. Top Load Washer and 7.0 Cu. Ft. Electric Dryer Top Load Washer and 7.0 Cu. Ft. Electric Dryer"
                                },
                                "quantity": {
                                    "pageIndex": 0,
                                    "value": "2"
                                },
                                "price": {
                                    "pageIndex": 0,
                                    "value": "1,072"
                                },
                                "amount": {
                                    "pageIndex": 0,
                                    "value": "2,144.00"
                                }
                            }
                        ]
                    },
                    {
                        "name": "Tax",
                        "objectType": "field",
                        "value": "0.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Final Total",
                        "objectType": "field",
                        "value": "2,144.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    },
                    {
                        "name": "Discount Amount",
                        "objectType": "field",
                        "value": ""
                    },
                    {
                        "name": "New Total",
                        "objectType": "field",
                        "value": "2,144.00",
                        "pageIndex": 0,
                        "rectangle": [
                            0,
                            0,
                            0,
                            0
                        ]
                    }
                ],
                "elapsed": 0.049613,
                "templateName": "Sample Template",
                "templateVersion": "4",
                "timestamp": "2024-05-01T03:13:37"
            },
            "pageCount": 1,
            "error": false,
            "status": 200,
            "name": "64f679fb-4aad-43d2-89dd-3286660c895b_page3.json",
            "credits": 42,
            "remainingCredits": 17813,
            "duration": 113
        }
    ],
    "metadata": null,
    "value": null
}

This is what I want my table to look like if its possible:

table_sample
table_sample2462×622 49.1 KB

Here is what I want the JSON to look like:

{
    "data": [
        {
            "Store #": "314",
            "Invoice #": "10093230",
            "Invoice Date": "3/22/2024",
            "Order #": "",
            "Customer PO": "15006663-1758885",
            "Vendor": "WHIRLPOOL\n               CORPORATION",
            "Item No": "MVW4505MW",
            "Descr": "Top Load Washer with Deep Fill - 4.5 cu. ft. cu. ft.",
            "QTY Ordered": "",
            "QTY Shipped": "2",
            "Unit Price": "456",
            "Extension": "912",
            "Tax": "0",
            "Final Total": "2,204.00",
            "Discount Amount": "0",
            "New total": "2,204.00"
        },
        {
            "Store #": "314",
            "Invoice #": "10093230",
            "Invoice Date": "3/22/2024",
            "Order #": "",
            "Customer PO": "15006663-1758885",
            "Vendor": "WHIRLPOOL\n               CORPORATION",
            "Item No": "NTW4516FW",
            "Descr": "AMANA 3.5 CF. HE TOP LOAD 8 CYCLES WHITE CYCLES WHITE",
            "QTY Ordered": "",
            "QTY Shipped": "4",
            "Unit Price": "323",
            "Extension": "1,292.00",
            "Tax": "0",
            "Final Total": "2,204.00",
            "Discount Amount": "0",
            "New total": "2,204.00"
        },
        {
            "Store #": "314",
            "Invoice #": "10093231",
            "Invoice Date": "3/22/2024",
            "Order #": "",
            "Customer PO": "15006674-17588857",
            "Vendor": "WHIRLPOOL\n               CORPORATION",
            "Item No": "WPL6157EBPR",
            "Descr": "Whirlpool Volcano Black 5.3 Cu. Ft. Top Load Washer and 7.0 Cu. Ft. Electric Dryer Top Load Washer and 7.0 Cu. Ft. Electric Dryer",
            "QTY Ordered": "",
            "QTY Shipped": "2",
            "Unit Price": "1,072",
            "Extension": "2,144.00",
            "Tax": "0",
            "Final Total": "2,144.00",
            "Discount Amount": "0",
            "New total": "2,144.00"
        },
        {
            "Store #": "314",
            "Invoice #": "10093231",
            "Invoice Date": "3/22/2024",
            "Order #": "",
            "Customer PO": "15006671-17595007",
            "Vendor": "ASHLEY\n                FURNITURE\n                 INDUSTRIES",
            "Item No": "83905U1",
            "Descr": "Signature Design by Ashley Abinger Sofa and Loveseat- Smoke Sofa and Loveseat- Smoke",
            "QTY Ordered": "",
            "QTY Shipped": "1",
            "Unit Price": "495.42",
            "Extension": "495.42",
            "Tax": "0",
            "Final Total": "869.24",
            "Discount Amount": "0",
            "New total": "869.24"
        },
        {
            "Store #": "314",
            "Invoice #": "10093231",
            "Invoice Date": "3/22/2024",
            "Order #": "",
            "Customer PO": "15006671-17595007",
            "Vendor": "ASHLEY\n                FURNITURE\n                 INDUSTRIES",
            "Item No": "W446W16",
            "Descr": "Signature Design by Ashley Trinell 72 TV Stand with Electric Fireplace-B TV Stand with Electric Fireplace-B",
            "QTY Ordered": "",
            "QTY Shipped": "1",
            "Unit Price": "373.82",
            "Extension": "373.82",
            "Tax": "0",
            "Final Total": "869.24",
            "Discount Amount": "0",
            "New total": "869.24"
        }
    ]
}

Any help would be greatly appreciated!

Thanks,

2

You need to get the values of the fields that you are going to repeat:
data.body.objects.filter(obj => obj.objectType === "field");
and separately the table rows that will be associated with them data.body.objects.filter(obj => obj.objectType === "table");
From there you can create your final array by looping through the tables/rows:

let foo = { "data": [ ... ] } //your data here

// Iterate over each "tblset" object in the data array
let result = foo.data.flatMap(tblset => {
    // Extract the fields that will repeat for each table
    let fields = tblset.body.objects.filter(obj => obj.objectType === "field");
    // Extract the tables from the tblset where we get the "rows"
    let tables = tblset.body.objects.filter(obj => obj.objectType === "table");
    
    // Construct an array of objects, one object for each table in the tblset
    return tables.flatMap(table => {
        // Extract the items in the "rows" array and store them in an object
        return table.rows.map((row, index) => {
            let obj = {};
            //Each row is named by the key name and has a value in the object
            Object.keys(row).forEach(key => {
                obj[key] = row[key].value;
            });
            // Add top-level tblset fields to the object
            fields.forEach(field => {
                obj[field.name] = field.value;
            });
            return obj;
        });
    });
});

return result
1 Like
3

Hi @jg80

Thanks for your reply!

Where exactly do I put data.body.objects.filter(obj => obj.objectType === "field"); and data.body.objects.filter(obj => obj.objectType === "table"); ? In the transform data of the query?

And then do I add a JS with the array?

I can't seem to find "field" anywhere in the API response so I am a little lost

Any additional help would be appreciated.

Regards,

4

The straightforward approach is to add the code I provided as a transformer to your API query. The only change is you just want to start with let foo = data instead of how I originally put it, which was based on pasting in the text you provided. That way, you can set the API as the table source and it should be good to go.

The two data.body.objects... lines are included in the full body of the code; they aren't supposed to be used elsewhere, I was just calling them out as the key components of how the code works.

5

Hi @jg80

Thanks again for your reply and sorry it took me a while to get back to you but I was under the weather.

Thanks for breaking it down for me.

I entered the code to my transformation with "let foo = data" but I get an error and no data appears. I also tried using the code in a separate JS query and referenced "let foo = query.data" but I get an error as well.

Screenshot 2024-05-16 at 7.00.21 PM
Screenshot 2024-05-16 at 7.00.21 PM721×822 62.8 KB

Any help would be greatly appreciated.

Thanks,

6

Try to start with let foo = parse_pdf4 or change the next line of code to let result = foo.flatMap(tblset => (... (don't do both)

7

Hi @jg80

I changed foo.flatMap(tblset => (... and it worked like a charm!!

Thank you so much.. YOU ROCK!!! :smiley:

Is there anyway to alter one more thing:

For the column/field "No", if the current value in the specific row is greater than 1 then can I edit the value in column "Final Total" to always be 0?

Regards,

8

hopefully I haven't misunderstood what you're asking, but I think you just need to change

fields.forEach(field => {
    obj[field.name] = field.value;
});

to

fields.forEach(field => {
    var field_value = field.value;
    var field_name = field.name;
    // if we found the field with a value we want to edit
    if(field_name  === 'my_field_name'){
        // if the field we found has a specific value
        if(field_value > 1){
            field_value = 0;
            // uncomment below to rename the field if you want
            // field_name = 'new_name'
        }
    }
    obj[field_name ] = field_value;
});

I went ahead and added what you need to rename the field also if you need it.

2 Likes
10

can I see your code? just that section. looks like some sort of 'off by one error' that ends in a duplicated value.

typing this on my phone so sorry for typeos but heres how i probly would do it, i cant test it but the logic should be right(?) just might get syntax wrong here or there. anyway here you go, so you can compare to something:

// flag for signaling if we need to modify a value for a row
var modify_flag = false;

return tables.flatMap(table => {
  var obj = {}

  // for each row
  Object.keys(row).forEach(key => {
    obj[key] = row[key].value;
  }

  // for each colum
  fields.forEach(field => {
      var field_value = field.value;
      var field_name = field.name;
      
      console.log({before: {'field_name': field_name, 'field_value'}});

      // if we found the 'No' column and its value is greater than 1
      if(field_name  === 'No' && field_value > 1){
         // set the flag
         modify_flag = true;
       }

       // copy field and value to the new object
       obj[field_name] = field_value;
   }

   // if our conditions for modifying the value of another colum have been met
   if(modify_flag){
     // update the `column_name_to_modify` column with our new value
     obj['column_name_to_modify'] = 0;
     // reset the flag.
     modify_flag = false;
   }

    consile.log({'after': table});

    // get the next row
    return obj;
}

I went with this design/pattern or whatever in case the array containing column data doesnt guarantee the order of the columns

you could try doing it all inside the inner most loop, but if you find the 'No' colum before you find the column you want to edit then later on it could be easy to accidently overwrite the value you changed it to.

1 Like
11

I was able to get it working. Thanks for all the help!

let foo = parse_pdf4.data 

let result = foo.flatMap(tblset => { 
    let fields = tblset.body.objects.filter(obj => obj.objectType === "field"); 
    let tables = tblset.body.objects.filter(obj => obj.objectType === "table"); 

    return tables.flatMap(table => { 
        return table.rows.map((row, index) => { 
            let obj = {}; 
            Object.keys(row).forEach(key => { 
                obj[key] = row[key].value; 
            }); 
            fields.forEach(field => { 
                obj[field.name] = field.value; 
            }); 

            // add a check for "no" field value
            if(obj['no'] > 1) { 
                obj['Final Total'] = 0;
            }

            return obj; 
        }); 
    }); 
});

return result;

Regards,

2 Likes