Nested Array / collection help please. Should be simple

Mit · April 2, 2024, 2:07pm

Hi all,
I’ve read several similar posts, YouTube, and documentation and still a bit lost with this.

I’m looking to get the value which is highlighted in the red box. In this case 228.80
Its in a large nested array.

I tried various options of the get and map function

But not having luck. I’ve also tried changing id to “VAT Control” and also using value;value;VAT Control

Ideally want to find where value in collection 1 is equal to VAT Control, then return value in Collection 2

Thanks for your help!

Mit · April 2, 2024, 2:20pm

I’ve tried this too.

samliew · April 2, 2024, 4:22pm

Could you please share the output bundle as text so that others can build a solution?

Please provide the output bundles of the module by clicking the white speech bubble on the top-right of the module and select “Download output bundles”.
Screenshot_2023-10-06_141025

A.

Save the bundle contents in your text editor as a bundle.txt file, and upload it here into this discussion thread.

Uploading it here will look like this:

bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted output bundle in this manner:

Either add three backticks ``` before and after the code, like this:

```
^{input/output bundle content goes here}
```
Or use the format code button in the editor:

Providing the output bundles will allow others to replicate what is going on in the scenario even if they do not use the external service.

Following these steps will allow others to assist you here. Thanks!

Mit · April 3, 2024, 9:49am

blueprint.json (7.2 KB)

Maybe uploading the blueprint.json file will help.

samliew · April 3, 2024, 9:54am

The blueprint doesn’t contain the data as shown in your first screenshot. Perhaps you could upload it to Google Drive and get a public readonly share link?

Mit · April 3, 2024, 10:02am

[
    {
        "body": {
            "Header": {
                "Time": "2024-04-02T09:33:50-07:00",
                "ReportName": "BalanceSheet",
                "DateMacro": "this fiscal year-to-date",
                "ReportBasis": "Accrual",
                "StartPeriod": "2024-03-01",
                "EndPeriod": "2024-04-02",
                "SummarizeColumnsBy": "Total",
                "Currency": "GBP",
                "Option": [
                    {
                        "Name": "AccountingStandard",
                        "Value": "UK"
                    },
                    {
                        "Name": "NoReportData",
                        "Value": "false"
                    }
                ]
            },
            "Columns": {
                "Column": [
                    {
                        "ColTitle": "",
                        "ColType": "Account",
                        "MetaData": [
                            {
                                "Name": "ColKey",
                                "Value": "account"
                            }
                        ]
                    },
                    {
                        "ColTitle": "Total",
                        "ColType": "Money",
                        "MetaData": [
                            {
                                "Name": "ColKey",
                                "Value": "total"
                            }
                        ]
                    }
                ]
            },
            "Rows": {
                "Row": [
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Fixed Asset"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "Header": {
                                        "ColData": [
                                            {
                                                "value": "Tangible assets"
                                            },
                                            {
                                                "value": ""
                                            }
                                        ]
                                    },
                                    "Rows": {
                                        "Row": [
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Computer and equipment",
                                                        "id": "52"
                                                    },
                                                    {
                                                        "value": "13259.41"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Computer and equipment - Depreciation",
                                                        "id": "79"
                                                    },
                                                    {
                                                        "value": "-5061.93"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Office Equipment - Cost",
                                                        "id": "77"
                                                    },
                                                    {
                                                        "value": "354.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Office Equipment - Depreciation",
                                                        "id": "78"
                                                    },
                                                    {
                                                        "value": "-247.34"
                                                    }
                                                ],
                                                "type": "Data"
                                            }
                                        ]
                                    },
                                    "Summary": {
                                        "ColData": [
                                            {
                                                "value": "Total Tangible assets"
                                            },
                                            {
                                                "value": "8304.14"
                                            }
                                        ]
                                    },
                                    "type": "Section",
                                    "group": "FixedAssets"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Fixed Asset"
                                },
                                {
                                    "value": "8304.14"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "TotalAssets"
                    },
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Cash at bank and in hand"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "ColData": [
                                        {
                                            "value": "Paid Personally",
                                            "id": "54"
                                        },
                                        {
                                            "value": "-14.95"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Petty Cash",
                                            "id": "93"
                                        },
                                        {
                                            "value": "0.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "XXXX4456 (4456)",
                                            "id": "46"
                                        },
                                        {
                                            "value": "23421.32"
                                        }
                                    ],
                                    "type": "Data"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Cash at bank and in hand"
                                },
                                {
                                    "value": "23406.37"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "BankAccounts"
                    },
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Debtors"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "ColData": [
                                        {
                                            "value": "Debtors",
                                            "id": "48"
                                        },
                                        {
                                            "value": "3388.00"
                                        }
                                    ],
                                    "type": "Data"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Debtors"
                                },
                                {
                                    "value": "3388.00"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "AR"
                    },
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Current Assets"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "ColData": [
                                        {
                                            "value": "Amounts due from company Holdings Ltd",
                                            "id": "75"
                                        },
                                        {
                                            "value": "1860.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Amounts due from company Properties Ltd",
                                            "id": "76"
                                        },
                                        {
                                            "value": "0.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Chairty - Loans",
                                            "id": "100"
                                        },
                                        {
                                            "value": "0.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Prepayments",
                                            "id": "74"
                                        },
                                        {
                                            "value": "1349.35"
                                        }
                                    ],
                                    "type": "Data"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Current Assets"
                                },
                                {
                                    "value": "3209.35"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "OtherCurrentAssets"
                    },
                    {
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Net current assets"
                                },
                                {
                                    "value": "30003.72"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "totCurAsset"
                    },
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Creditors: amounts falling due within one year"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "Header": {
                                        "ColData": [
                                            {
                                                "value": "Trade Creditors"
                                            },
                                            {
                                                "value": ""
                                            }
                                        ]
                                    },
                                    "Rows": {
                                        "Row": [
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Creditors",
                                                        "id": "80"
                                                    },
                                                    {
                                                        "value": "0.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            }
                                        ]
                                    },
                                    "Summary": {
                                        "ColData": [
                                            {
                                                "value": "Total Trade Creditors"
                                            },
                                            {
                                                "value": "0.00"
                                            }
                                        ]
                                    },
                                    "type": "Section",
                                    "group": "AP"
                                },
                                {
                                    "Header": {
                                        "ColData": [
                                            {
                                                "value": "Current Liabilities"
                                            },
                                            {
                                                "value": ""
                                            }
                                        ]
                                    },
                                    "Rows": {
                                        "Row": [
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Accruals",
                                                        "id": "116"
                                                    },
                                                    {
                                                        "value": "1650.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Corporation Tax Liability",
                                                        "id": "109"
                                                    },
                                                    {
                                                        "value": "10.20"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Deferred tax",
                                                        "id": "81"
                                                    },
                                                    {
                                                        "value": "-164.35"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Directors Loan Account",
                                                        "id": "50"
                                                    },
                                                    {
                                                        "value": "34330.89"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Net Wages Control Account",
                                                        "id": "112"
                                                    },
                                                    {
                                                        "value": "0.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "PAYE and NI",
                                                        "id": "111"
                                                    },
                                                    {
                                                        "value": "0.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "Pensions Payable",
                                                        "id": "108"
                                                    },
                                                    {
                                                        "value": "0.00"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "VAT Control",
                                                        "id": "101"
                                                    },
                                                    {
                                                        "value": "265.79"
                                                    }
                                                ],
                                                "type": "Data"
                                            },
                                            {
                                                "ColData": [
                                                    {
                                                        "value": "VAT Suspense",
                                                        "id": "102"
                                                    },
                                                    {
                                                        "value": "1241.92"
                                                    }
                                                ],
                                                "type": "Data"
                                            }
                                        ]
                                    },
                                    "Summary": {
                                        "ColData": [
                                            {
                                                "value": "Total Current Liabilities"
                                            },
                                            {
                                                "value": "37334.45"
                                            }
                                        ]
                                    },
                                    "type": "Section",
                                    "group": "OtherCurrentLiabilities"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Creditors: amounts falling due within one year"
                                },
                                {
                                    "value": "37334.45"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "CurrentLiabilities"
                    },
                    {
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Net current assets (liabilities)"
                                },
                                {
                                    "value": "-7330.73"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "NetCurrentAssets"
                    },
                    {
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total assets less current liabilities"
                                },
                                {
                                    "value": "973.41"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "TotalAssetLessCurrentLiabilities"
                    },
                    {
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total net assets (liabilities)"
                                },
                                {
                                    "value": "973.41"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "NetAssets"
                    },
                    {
                        "Header": {
                            "ColData": [
                                {
                                    "value": "Capital and Reserves"
                                },
                                {
                                    "value": ""
                                }
                            ]
                        },
                        "Rows": {
                            "Row": [
                                {
                                    "ColData": [
                                        {
                                            "value": "Ordinary A Shares",
                                            "id": "83"
                                        },
                                        {
                                            "value": "50.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Ordinary Shares",
                                            "id": "82"
                                        },
                                        {
                                            "value": "50.00"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Retained Earnings",
                                            "id": "36"
                                        },
                                        {
                                            "value": "93.78"
                                        }
                                    ],
                                    "type": "Data"
                                },
                                {
                                    "ColData": [
                                        {
                                            "value": "Profit for the year"
                                        },
                                        {
                                            "value": "779.63"
                                        }
                                    ],
                                    "type": "Data",
                                    "group": "NetIncome"
                                }
                            ]
                        },
                        "Summary": {
                            "ColData": [
                                {
                                    "value": "Total Capital and Reserves"
                                },
                                {
                                    "value": "973.41"
                                }
                            ]
                        },
                        "type": "Section",
                        "group": "Equity"
                    }
                ]
            }
        },
        "headers": {
            "",
            "",
            "",
            "",
            "",
            "",
            "i",
            ""
        }
    }
]

Mit · April 3, 2024, 10:03am

Ok, i’ve tried the above message again for you. I hope this helps. Many thanks.

samliew · April 3, 2024, 12:06pm

1.

To get CurrentLiabilities, the code is

{{first(map(2.body.Rows.Row; "Rows.Row"; "group"; "CurrentLiabilities"))}}

Screenshot_2024-04-03_200410

2.

Then, to get OtherCurrentLiabilities, the code is

{{first(map(CurrentLiabilities; "Rows.Row"; "group"; "OtherCurrentLiabilities"))}}

Screenshot_2024-04-03_200421

3.

Then to get VAT, the code is

{{first(map(OtherCurrentLiabilities; "ColData.2.value"; "ColData.1.value"; "VAT Control"))}}

Modules Export

You can copy and paste this module export into your scenario. This will paste the modules shown in my screenshots above.

Copy the code below by clicking the copy button when you mouseover the top-right of the code block
Enter your scenario editor. Press ESC to close any dialogs. Press CTRLV to paste in the canvas.
Click on each imported module and save it. You may need to remap some variables.

Modules JSON Export

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 4,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "CurrentLiabilities",
                        "scope": "roundtrip",
                        "value": "{{first(map(5.body.Rows.Row; \"Rows.Row\"; \"group\"; \"CurrentLiabilities\"))}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 300,
                            "y": 0,
                            "name": "CurrentLiabilities"
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "CurrentLiabilities",
                                "label": "CurrentLiabilities",
                                "type": "any"
                            }
                        ]
                    }
                },
                {
                    "id": 7,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "OtherCurrentLiabilities",
                        "scope": "roundtrip",
                        "value": "{{first(map(4.CurrentLiabilities; \"Rows.Row\"; \"group\"; \"OtherCurrentLiabilities\"))}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 600,
                            "y": 0,
                            "name": "OtherCurrentLiabilities"
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "OtherCurrentLiabilities",
                                "label": "OtherCurrentLiabilities",
                                "type": "any"
                            }
                        ]
                    }
                },
                {
                    "id": 8,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "name": "VATControl",
                        "scope": "roundtrip",
                        "value": "{{first(map(7.OtherCurrentLiabilities; \"ColData.2.value\"; \"ColData.1.value\"; \"VAT Control\"))}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 900,
                            "y": 0,
                            "name": "VATControl"
                        },
                        "restore": {
                            "expect": {
                                "scope": {
                                    "label": "One cycle"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "name",
                                "type": "text",
                                "label": "Variable name",
                                "required": true
                            },
                            {
                                "name": "scope",
                                "type": "select",
                                "label": "Variable lifetime",
                                "required": true,
                                "validate": {
                                    "enum": [
                                        "roundtrip",
                                        "execution"
                                    ]
                                }
                            },
                            {
                                "name": "value",
                                "type": "any",
                                "label": "Variable value"
                            }
                        ],
                        "interface": [
                            {
                                "name": "VATControl",
                                "label": "VATControl",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}

Mit · April 3, 2024, 1:24pm

Genius!!

Thank you so much. It worked in the first go.

I would like to understand the logic here, could you explain it please?

Might help others in the furture too!

samliew · April 3, 2024, 1:41pm

Basically you need to step through each nested sub-array you encounter. Because you can’t mix array and collections using a single map function.

Mit · April 3, 2024, 2:02pm

Thank you, thought as much.

P.S. Is thier an editor which helps quickly figure out which nested array one belongs too ?

samliew · April 3, 2024, 2:08pm

That’s a great idea! I’m sure others would agree that this is a nice feature to have.

You can submit this suggestion to the Idea exchange, under Platform ideas and improvements.

Don’t forget to search for it first, just in case someone already suggested it, so that you don’t end up creating a duplicate.

Mit · April 3, 2024, 2:37pm

Thanks, I was thinking more of a 3rd party app.

For example, I copied the outbudle in notepad++ and also brackets but wasn’t very easy too, unless you close each array one by one.

Just wondering how you found out which top array the VAT Control belong too.

samliew · April 3, 2024, 2:45pm

See #1: Map function, but filter where group = CurrentLiabilities

Topic		Replies	Views
Navigating collections within arrays within collections Questions & Answers arrays , get-function , get-map-function , collections	2	260	December 11, 2023
[array help] Pick the collection item with the lowest value & return a given value Questions & Answers functions , arrays , collections	5	186	February 16, 2024
Retrieving Values From Nested Collections Questions & Answers filters , mapping , arrays	2	223	December 13, 2023
How can I get an item in a nest of collections & arrays? Questions & Answers functions , mapping , arrays	4	639	September 7, 2023
[array help] get a collection field value containing the sub-collection with the lowest field value from an array Questions & Answers functions , arrays , collections	4	153	February 29, 2024

Nested Array / collection help please. Should be simple

A.

B.

1.

2.

3.

Modules Export

Modules JSON Export

Related Topics