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.