Get values from multiple outputs and test if exists against other multiple outputs

Dennis_Gee · July 5, 2024, 12:25pm

I know… quite an ambiguous title but couldn’t think of anything better…!

Basically I have a REST API call to my application (System 1) that contains invoice numbers, and I want to test if any of those invoices exists from another REST query from a third-party vendor’s application (System 2), and if so, list those invoice numbers.

To start with, the REST GET invoices from System 1 returns a single bundle with values in this format:

Each of the collections has a refNo that I will need to test for later against the System 2:

I then do another REST GET of System 2 to get a list of outstanding invoices which returns data in a similar format:

Expanding a collection shows the item invoiceTag that I wish to match against:

I have tried iterators and array aggregators and changing the order of the operations in the scenario and keep getting stuck in loops where I can’t do what I want…

The objective is to test each of the invoices returned from the vendor’s system (invoiceTag) and see if they already exist in my system (refNo), then produce a list of invoice numbers that do not exist in my system to execute against (i.e. create them in my system so at the end of the day everything matches).

Can someone please advise where and how to place array aggregators, iterators and/or filters to perform the above?

samliew · July 5, 2024, 12:40pm

To compare an array of values with another array of values, we cannot use the built-in functions.

You’ll have to Iterate one of the arrays, and create a filter to filter out items that are not in the second array, then aggregate back to an array.

If you need further assistance, please provide the following:

1. Scenario blueprint

Please export the scenario blueprint file to allow others to view the mappings and settings. At the bottom of the scenario editor, you can click on the three dots to find the Export Blueprint menu item.

^{(Note: Exporting your scenario will not include private information or keys to your connections)}

Uploading it here will look like this:

blueprint.json (12.3 KB)

2. And most importantly, Input/Output bundles

Please provide the input and output bundles of all the trigger/iterator/aggregator modules by running the scenario (or get from the scenario History tab), then click the white speech bubble on the top-right of each module and select “Download input/output bundles”.

A.

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

Uploading them here will look like this:

module-1-output-bundle.txt (12.3 KB)

B.

If you are unable to upload files on this forum, alternatively you can paste the formatted bundles 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 input/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!

samliew – request private consultation

Join the Make Fans Discord server to chat with other makers!

Dennis_Gee · July 5, 2024, 1:15pm

Thanks @samliew - I thought so, but I just can’t get my head around what to iterate, and what to aggregate.

PFA the outputs from the REST functions from system 1 and system 2:

system1.txt (5.7 KB)
system2.txt (293.8 KB)

Cheers,

Dennis

samliew · July 6, 2024, 12:00pm

You might not need an aggregator

You just need this filter

Module Export

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

Copy the JSON 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 (paste keyboard shortcut for Windows) to paste directly in the canvas.
Click on each imported module and save it for validation. You may be prompted to remap some variables and connections.

Click to Expand Module Export Code

JSON - Copy and Paste this directly in the scenario editor

{
    "subflows": [
        {
            "flow": [
                {
                    "id": 92,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "[\n    {\n        \"data\": [\n            {\n                \"id\": 6438748163,\n                \"lastModifiedBy\": \"user@mydomain.com\",\n                \"lastModifiedDateTime\": \"2024-07-03T20:43:47.000Z\",\n                \"createdBy\": \"user@mydomain.com\",\n                \"createdDateTime\": \"2024-06-29T15:14:32.000Z\",\n                \"refNo\": \"08DF1637 \",\n                \"terms\": \"NET 30\",\n                \"billNumber\": 15436,\n                \"vendorID\": 3558146051,\n                \"billDate\": \"2024-05-23\",\n                \"dueDate\": \"2024-05-23\",\n                \"paymentDate\": null,\n                \"credit\": false,\n                \"workOrderID\": null,\n                \"markupGLAccountID\": 1579810819,\n                \"markupPercentage\": 0,\n                \"expenseRecoveries\": [],\n                \"amount\": 102.47\n            },\n            {\n                \"id\": 6446448939,\n                \"lastModifiedBy\": \"dgee\",\n                \"lastModifiedDateTime\": \"2024-07-05T03:21:39.000Z\",\n                \"createdBy\": \"user@mydomain.com\",\n                \"createdDateTime\": \"2024-07-03T20:35:42.000Z\",\n                \"refNo\": \"2AB2F87C\",\n                \"terms\": \"NET 30\",\n                \"billNumber\": 15594,\n                \"vendorID\": 3558146051,\n                \"billDate\": \"2024-07-03\",\n                \"dueDate\": \"2024-07-03\",\n                \"paymentDate\": null,\n                \"credit\": false,\n                \"workOrderID\": null,\n                \"markupGLAccountID\": 1579810819,\n                \"markupPercentage\": 0,\n                \"expenseRecoveries\": [],\n                \"amount\": 5848.01\n            },\n            {\n                \"id\": 6448349331,\n                \"lastModifiedBy\": \"dgee\",\n                \"lastModifiedDateTime\": \"2024-07-05T03:23:40.000Z\",\n                \"createdBy\": \"dgee\",\n                \"createdDateTime\": \"2024-07-05T03:21:39.000Z\",\n                \"refNo\": \"4032E98A\",\n                \"terms\": \"NET 30\",\n                \"billNumber\": 15602,\n                \"vendorID\": 3558146051,\n                \"billDate\": \"2024-07-04\",\n                \"dueDate\": \"2024-07-04\",\n                \"paymentDate\": null,\n                \"credit\": false,\n                \"workOrderID\": null,\n                \"markupGLAccountID\": 1579810819,\n                \"markupPercentage\": 0,\n                \"expenseRecoveries\": [],\n                \"amount\": 2000\n            }\n        ],\n        \"fileSize\": 2192\n    }\n]"
                    },
                    "metadata": {
                        "designer": {
                            "x": 963,
                            "y": -844,
                            "name": "System 1"
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 93,
                    "module": "json:ParseJSON",
                    "version": 1,
                    "parameters": {
                        "type": ""
                    },
                    "mapper": {
                        "json": "[\n  {\n      \"data\": {\n          \"requestId\": \"ad359863-3b0a-4a1e-a5ee-4b31c454bd98\",\n          \"customerId\": \"a1b7ba33-f078-4ed9-9d62-7b0115dgr436\",\n          \"data\": [\n              {\n                  \"invoiceId\": \"a393ee5e-ab9e-4850-b57f-86b00ad9409f\",\n                  \"invoiceTag\": \"#4032E98A\"\n              },\n              {\n                  \"invoiceId\": \"d9ded9e6-10a5-4a01-83a8-d213946239ee\",\n                  \"invoiceTag\": \"#2AB2F87C\"\n              },\n              {\n                  \"invoiceId\": \"c9409596-8750-4224-95fd-e7df5ce794ad\",\n                  \"invoiceTag\": \"#0950F48A\"\n              },\n              {\n                  \"invoiceId\": \"a8438113-e526-43ff-9c5a-e66ea6c0a3ae\",\n                  \"invoiceTag\": \"#8463D1C1\"\n              },\n              {\n                  \"invoiceId\": \"aae152a3-1994-4021-9c44-301cd709c8f8\",\n                  \"invoiceTag\": \"#4FA28438\"\n              },\n              {\n                  \"invoiceId\": \"208a59f5-f239-4f55-8f50-96c62d13c0a3\",\n                  \"invoiceTag\": \"#039DC2B3\"\n              },\n              {\n                  \"invoiceId\": \"49c31b80-4269-4179-8ebd-3bca11db7d9a\",\n                  \"invoiceTag\": \"#4D3081E8\"\n              },\n              {\n                  \"invoiceId\": \"96fb2fc6-fffd-40c5-8c0d-7bdcd6205e98\",\n                  \"invoiceTag\": \"#7EB9A6D6\"\n              },\n              {\n                  \"invoiceId\": \"bdb64255-7c40-4c3a-9e1b-31dc11a3eeb9\",\n                  \"invoiceTag\": \"#CEA4ECA6\"\n              },\n              {\n                  \"invoiceId\": \"8a06b299-475a-40f7-bafb-b5d50aa222ee\",\n                  \"invoiceTag\": \"#710EE9BA\"\n              },\n              {\n                  \"invoiceId\": \"bafeb022-e314-4fd2-ab78-929b3a20ec31\",\n                  \"invoiceTag\": \"#A80FFF90\"\n              },\n              {\n                  \"invoiceId\": \"bef13c3d-5cc9-4b69-b3e1-c28631f565f3\",\n                  \"invoiceTag\": \"#363161FB\"\n              },\n              {\n                  \"invoiceId\": \"4f2be1e6-6bea-4d14-aad3-bdf3d95d9d4d\",\n                  \"invoiceTag\": \"#6F9DAE5F\"\n              },\n              {\n                  \"invoiceId\": \"8e38261a-7bc6-4436-8769-6a931950d671\",\n                  \"invoiceTag\": \"#049B65EF\"\n              },\n              {\n                  \"invoiceId\": \"7aca25a2-6f2f-4eb4-8cdc-3c9b6fd575ec\",\n                  \"invoiceTag\": \"#260CE9A4\"\n              },\n              {\n                  \"invoiceId\": \"754de628-7e10-440a-806e-7a5f60c11889\",\n                  \"invoiceTag\": \"#1AED80F6\"\n              },\n              {\n                  \"invoiceId\": \"79a78623-208e-419d-8b85-64e81b9b945a\",\n                  \"invoiceTag\": \"#027603\"\n              },\n              {\n                  \"invoiceId\": \"9b597359-e83d-4bac-834a-eba4ae94a654\",\n                  \"invoiceTag\": \"#BED8F910\"\n              },\n              {\n                  \"invoiceId\": \"460d6221-db87-4939-a8c4-6e5d94d86487\",\n                  \"invoiceTag\": \"#5C836709\"\n              },\n              {\n                  \"invoiceId\": \"a5e1135a-a598-4e30-ae99-f141862ee005\",\n                  \"invoiceTag\": \"#E82DAD29\"\n              },\n              {\n                  \"invoiceId\": \"be660156-49d4-4d42-93bb-07d3102f1750\",\n                  \"invoiceTag\": \"#E934B669\"\n              },\n              {\n                  \"invoiceId\": \"3a0594e2-9d2b-47f1-a08d-7d2bbe17d321\",\n                  \"invoiceTag\": \"#2B5A87B0\"\n              },\n              {\n                  \"invoiceId\": \"30562eb8-fdc2-41a5-9b48-a892bbf67bf2\",\n                  \"invoiceTag\": \"#08DF1637\"\n              },\n              {\n                  \"invoiceId\": \"6913d498-b3fd-41ed-923b-568f511e653e\",\n                  \"invoiceTag\": \"#0C8D39F9\"\n              },\n              {\n                  \"invoiceId\": \"25433cdc-5315-4de6-b305-34ec48696f22\",\n                  \"invoiceTag\": \"#A96297F5\"\n              },\n              {\n                  \"invoiceId\": \"41c595ed-8548-4e92-9c44-ef07ede64017\",\n                  \"invoiceTag\": \"#D79ACB5F\"\n              },\n              {\n                  \"invoiceId\": \"04832d48-0f8d-4d6d-baf7-e647496dec74\",\n                  \"invoiceTag\": \"#D274237A\"\n              },\n              {\n                  \"invoiceId\": \"2d4f0bb7-8f19-42e7-8895-23145496579a\",\n                  \"invoiceTag\": \"#BCA9290F\"\n              },\n              {\n                  \"invoiceId\": \"d1e05815-dc8a-4b19-a043-a672bbb20973\",\n                  \"invoiceTag\": \"#BBF16BF1\"\n              },\n              {\n                  \"invoiceId\": \"8e538fa2-6bcf-4757-b5b3-4773055b3042\",\n                  \"invoiceTag\": \"#B3F43903\"\n              },\n              {\n                  \"invoiceId\": \"7bfff774-9c0b-4d5c-8d76-a7ff9409a1e3\",\n                  \"invoiceTag\": \"#A294E23F\"\n              },\n              {\n                  \"invoiceId\": \"a39b33fe-d067-46ce-9b06-edab1e5d7f55\",\n                  \"invoiceTag\": \"#90241F25\"\n              },\n              {\n                  \"invoiceId\": \"04a8d8bc-d9d5-48cc-b964-51f2dd32cbf5\",\n                  \"invoiceTag\": \"#5BC866F\"\n              },\n              {\n                  \"invoiceId\": \"f3e37b49-6f8d-43c5-bb10-1b026a27ce49\",\n                  \"invoiceTag\": \"#48BD1FFC\"\n              },\n              {\n                  \"invoiceId\": \"61ee0a45-15c5-47e4-af32-6bec1966d3c0\",\n                  \"invoiceTag\": \"#1199D93\"\n              },\n              {\n                  \"invoiceId\": \"85995de4-7243-499b-8028-3aa8801d68e8\",\n                  \"invoiceTag\": \"#0B09E342\"\n              },\n              {\n                  \"invoiceId\": \"3c3c1e74-2903-4d20-9570-246a0b42ea71\",\n                  \"invoiceTag\": \"#0E195D80\"\n              },\n              {\n                  \"invoiceId\": \"06c702e7-3fc7-44d1-9495-09d0baaf6437\",\n                  \"invoiceTag\": \"#41E7CB33\"\n              },\n              {\n                  \"invoiceId\": \"f2025136-a6f7-458e-9302-baf1fccc804d\",\n                  \"invoiceTag\": \"#6E433850\"\n              },\n              {\n                  \"invoiceId\": \"7cfda575-381e-40ed-b430-7c941f95742b\",\n                  \"invoiceTag\": \"#F45DEC58\"\n              },\n              {\n                  \"invoiceId\": \"65750d60-8a6a-4554-a13a-a901aa494918\",\n                  \"invoiceTag\": \"#F1E4BF\"\n              },\n              {\n                  \"invoiceId\": \"1b8bff0d-b28c-46e2-bc80-6d97dc30459f\",\n                  \"invoiceTag\": \"#C840F7FB\"\n              },\n              {\n                  \"invoiceId\": \"ffe8f950-b0fb-45c2-9be2-461433f0b6b4\",\n                  \"invoiceTag\": \"#B94AAFFF\"\n              },\n              {\n                  \"invoiceId\": \"1449c5ab-b20e-4327-bd30-abd432099f47\",\n                  \"invoiceTag\": \"#963B44A1\"\n              },\n              {\n                  \"invoiceId\": \"c3848f1e-fad7-42c8-bdfb-20c77d31278f\",\n                  \"invoiceTag\": \"#8974EB71\"\n              },\n              {\n                  \"invoiceId\": \"351125a8-574d-4385-ba89-366e88f12516\",\n                  \"invoiceTag\": \"#73AE041A\"\n              },\n              {\n                  \"invoiceId\": \"e00d301a-866b-4916-9ee5-30e6bd8ecb1b\",\n                  \"invoiceTag\": \"#705B6E12\"\n              },\n              {\n                  \"invoiceId\": \"fc30f33b-bb15-4163-aee9-30c022f3485c\",\n                  \"invoiceTag\": \"#68749082\"\n              },\n              {\n                  \"invoiceId\": \"779c2ee6-2ae0-4d0a-b725-edd9c5f24c31\",\n                  \"invoiceTag\": \"#61E7B94\"\n              },\n              {\n                  \"invoiceId\": \"f2312afb-ddf1-4b66-8280-857e5186e897\",\n                  \"invoiceTag\": \"#5FCA61DF\"\n              },\n              {\n                  \"invoiceId\": \"dfbfacf9-3fe0-49a1-85da-cdb0a4b3597e\",\n                  \"invoiceTag\": \"#56EC8862\"\n              },\n              {\n                  \"invoiceId\": \"9dfd1c46-2ad0-4e49-967b-076470764a60\",\n                  \"invoiceTag\": \"#54F2104E\"\n              },\n              {\n                  \"invoiceId\": \"b104b164-9f31-462f-b150-72256e698a50\",\n                  \"invoiceTag\": \"#4F36E222\"\n              },\n              {\n                  \"invoiceId\": \"42b65c61-96a2-4cec-9e92-2254554ef654\",\n                  \"invoiceTag\": \"#4D070BEE\"\n              },\n              {\n                  \"invoiceId\": \"d6a3251a-75eb-403d-988c-32e4e31e9d9a\",\n                  \"invoiceTag\": \"#13612689\"\n              },\n              {\n                  \"invoiceId\": \"5ee3058e-9d23-43ba-8c54-7060623c7a5e\",\n                  \"invoiceTag\": \"#C2C0871D\"\n              },\n              {\n                  \"invoiceId\": \"e5c0b5d6-4057-46e1-b8fb-cec4fa27fd20\",\n                  \"invoiceTag\": \"#ED43F2F6\"\n              },\n              {\n                  \"invoiceId\": \"c0fb3cd6-c630-4bf4-94f3-12b0a51bc7f2\",\n                  \"invoiceTag\": \"#67459E1\"\n              },\n              {\n                  \"invoiceId\": \"8556ba7f-9484-47af-ae91-132eb7ad6db5\",\n                  \"invoiceTag\": \"#661C2FC3\"\n              },\n              {\n                  \"invoiceId\": \"6305b06e-739d-4d3d-a6e0-288012496c28\",\n                  \"invoiceTag\": \"#9E6D3284\"\n              },\n              {\n                  \"invoiceId\": \"935d4bc3-6b71-44f0-9565-ad8ce1a55ca5\",\n                  \"invoiceTag\": \"#FC2A3DC6\"\n              },\n              {\n                  \"invoiceId\": \"6ad4daba-83a8-40ac-8393-742297fb00d9\",\n                  \"invoiceTag\": \"#4FE613BE\"\n              },\n              {\n                  \"invoiceId\": \"8c276835-ccde-4fbc-afbe-3bbb8fe22232\",\n                  \"invoiceTag\": \"#9AE815D1\"\n              },\n              {\n                  \"invoiceId\": \"ce99d27a-eb4b-4227-9ce6-e5a3bd992d4e\",\n                  \"invoiceTag\": \"#B7186585\"\n              },\n              {\n                  \"invoiceId\": \"743686dd-7fe8-45bf-9340-b68164c1e839\",\n                  \"invoiceTag\": \"#1615B7A1\"\n              },\n              {\n                  \"invoiceId\": \"5e23749b-6f55-4775-9d9f-47410848e6ce\",\n                  \"invoiceTag\": \"#D992CBAD\"\n              },\n              {\n                  \"invoiceId\": \"0222c217-92dd-412a-a5ea-2a23e1b66bbd\",\n                  \"invoiceTag\": \"#293FFA3B\"\n              },\n              {\n                  \"invoiceId\": \"8f448f4a-26af-471e-a143-d3fca122e985\",\n                  \"invoiceTag\": \"#EA8A17E0\"\n              },\n              {\n                  \"invoiceId\": \"0aa929c4-0da8-4fed-9ac4-5539a4aee39e\",\n                  \"invoiceTag\": \"#476F7747\"\n              },\n              {\n                  \"invoiceId\": \"6ddab8f3-6b95-40be-9b24-afc83ed3da07\",\n                  \"invoiceTag\": \"#FAF83A7F\"\n              },\n              {\n                  \"invoiceId\": \"018286ba-c25d-4a35-9d4d-3bf435034dfb\",\n                  \"invoiceTag\": \"#DA80D441\"\n              },\n              {\n                  \"invoiceId\": \"4229c35d-4c4f-4f98-9315-ddd7f6d4eb36\",\n                  \"invoiceTag\": \"#A1A86DDF\"\n              },\n              {\n                  \"invoiceId\": \"f0e6abf2-88f3-4d65-998b-bd26ff48fb3b\",\n                  \"invoiceTag\": \"#DC2FC2\"\n              },\n              {\n                  \"invoiceId\": \"7b471246-d559-45d1-9946-8da566d13044\",\n                  \"invoiceTag\": \"#13046C28\"\n              },\n              {\n                  \"invoiceId\": \"75603075-5dbe-45a9-9266-ec98615f50fc\",\n                  \"invoiceTag\": \"#E4BA8297\"\n              },\n              {\n                  \"invoiceId\": \"58ea93ab-7b57-425a-88bd-ecccf1a5f155\",\n                  \"invoiceTag\": \"#FF6D0CD4\"\n              },\n              {\n                  \"invoiceId\": \"9a038556-0f32-4ef7-83e3-b03bdeb66e03\",\n                  \"invoiceTag\": \"#654538EA\"\n              }\n          ],\n          \"requestTimeUTC\": \"2024-07-05T11:58:41.509967Z\",\n          \"meta\": {\n              \"currentPage\": 1,\n              \"nextPageUrl\": null,\n              \"prevPageUrl\": null,\n              \"perPage\": 100,\n              \"from\": 1,\n              \"to\": 77,\n              \"total\": 77\n          }\n      },\n      \"fileSize\": 214549\n  }\n]"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1154,
                            "y": -844,
                            "name": "System 2"
                        },
                        "restore": {
                            "parameters": {
                                "type": {
                                    "label": "Choose a data structure"
                                }
                            }
                        },
                        "parameters": [
                            {
                                "name": "type",
                                "type": "udt",
                                "label": "Data structure"
                            }
                        ],
                        "expect": [
                            {
                                "name": "json",
                                "type": "text",
                                "label": "JSON string",
                                "required": true
                            }
                        ]
                    }
                },
                {
                    "id": 94,
                    "module": "builtin:BasicFeeder",
                    "version": 1,
                    "parameters": {},
                    "mapper": {
                        "array": "{{92.data}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1343,
                            "y": -841,
                            "name": "Iterate System 1 Invoices"
                        },
                        "restore": {
                            "expect": {
                                "array": {
                                    "mode": "edit"
                                }
                            }
                        },
                        "expect": [
                            {
                                "name": "array",
                                "type": "array",
                                "label": "Array",
                                "mode": "edit",
                                "spec": []
                            }
                        ]
                    }
                },
                {
                    "id": 96,
                    "module": "util:SetVariable2",
                    "version": 1,
                    "parameters": {},
                    "filter": {
                        "name": "Unknown InvoiceTag",
                        "conditions": [
                            [
                                {
                                    "a": "{{map(93.data.data; \"invoiceTag\")}}",
                                    "o": "array:notcontain",
                                    "b": "#{{94.refNo}}"
                                }
                            ]
                        ]
                    },
                    "mapper": {
                        "name": "invoiceTag",
                        "scope": "roundtrip",
                        "value": "{{94.refNo}}"
                    },
                    "metadata": {
                        "designer": {
                            "x": 1693,
                            "y": -843,
                            "name": "Module to Create InvoiceTag"
                        },
                        "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": "invoiceTag",
                                "label": "invoiceTag",
                                "type": "any"
                            }
                        ]
                    }
                }
            ]
        }
    ],
    "metadata": {
        "version": 1
    }
}