Workflow Loop to Retool DB Issue

Workflows
1

Hello all!,

I wanted to see if I could get some help with a workflow loop issue I am having.

I currently have 3 stages in my workflow.

Stage 1 is pulling all the access tokens in my Retool DB:

Query:

select access_token from finance

Response:

{
    "data": [
        {
            "access_token": "access-development-12345"
        },
        {
            "access_token": "access-development-67890"
        },
        {
            "access_token": "access-development-56789"
        }
    ]
}

Stage 2 is looping through each access token and running a POST API query:

Query:

POST https://api.testing.com and the body has the "{{value.access_token}}" from query1

Response:

{
    "data": [
        {
            "accounts": [
                {
                    "account_id": "1o3vmxa",
                    "balances": {
                        "available": 75.42,
                        "current": 27.69,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "7948",
                    "name": "Detroit",
                    "official_name": "Detroit",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "P4y1aqAyQ",
                    "balances": {
                        "available": 24.66,
                        "current": 32.41,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "8378",
                    "name": "Mud",
                    "official_name": "Mud",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "qLNJg93",
                    "balances": {
                        "available": 69.48,
                        "current": 69.48,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "8386",
                    "name": "Crush",
                    "official_name": "Crush",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "BDbKO0",
                    "balances": {
                        "available": 16.15,
                        "current": 33.8,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "9231",
                    "name": "MD",
                    "official_name": "MD",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "3oKmNzJ",
                    "balances": {
                        "available": 56.48,
                        "current": 98.68,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "9274",
                    "name": "KY",
                    "official_name": "KY",
                    "subtype": "checking",
                    "type": "depository"
                }
            ],
            "item": {
                "available_products": [
                    "balance",
                    "signal"
                ],
                "billed_products": [
                    "transactions"
                ],
                "consent_expiration_time": "2024-12-25T04:24:08Z",
                "error": null,
                "institution_id": "ins_1",
                "item_id": "P4y1aqA",
                "products": [
                    "transactions"
                ],
                "update_type": "background",
                "webhook": ""
            },
            "request_id": "Knwx0"
        },
        {
            "accounts": [
                {
                    "account_id": "o8qme6",
                    "balances": {
                        "available": 33.1,
                        "current": 33.1,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "3633",
                    "name": "Business Checking Plus",
                    "official_name": "Business Checking Plus",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "ZwBZV5",
                    "balances": {
                        "available": 16.15,
                        "current": 33.8,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "9231",
                    "name": "Analysis Business Checking",
                    "official_name": "Analysis Business Checking",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "5bxZKz",
                    "balances": {
                        "available": 56.48,
                        "current": 98.68,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "9274",
                    "name": "Analysis Business Checking",
                    "official_name": "Analysis Business Checking",
                    "subtype": "checking",
                    "type": "depository"
                }
            ],
            "item": {
                "available_products": [
                    "balance",
                    "signal"
                ],
                "billed_products": [
                    "transactions"
                ],
                "consent_expiration_time": "2024-12-25T04:26:10Z",
                "error": null,
                "institution_id": "ins_1",
                "item_id": "pZ3BeL8",
                "products": [
                    "transactions"
                ],
                "update_type": "background",
                "webhook": ""
            },
            "request_id": "CZgzl"
        },
        {
            "accounts": [
                {
                    "account_id": "rLmQrK73",
                    "balances": {
                        "available": 20.33,
                        "current": 20.33,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "0094",
                    "name": "CAPITAL",
                    "official_name": "COMMERCIAL ANALYSIS CHECKING",
                    "subtype": "checking",
                    "type": "depository"
                },
                {
                    "account_id": "BaR7xEN",
                    "balances": {
                        "available": 80.61,
                        "current": 80.61,
                        "iso_currency_code": "USD",
                        "limit": null,
                        "unofficial_currency_code": null
                    },
                    "mask": "0250",
                    "name": "CM",
                    "official_name": "COMMERCIAL CHECKING",
                    "subtype": "checking",
                    "type": "depository"
                }
            ],
            "item": {
                "available_products": [
                    "balance",
                    "signal"
                ],
                "billed_products": [
                    "transactions"
                ],
                "consent_expiration_time": null,
                "error": null,
                "institution_id": "ins_129601",
                "item_id": "aYZ4dXb",
                "products": [
                    "transactions"
                ],
                "update_type": "background",
                "webhook": ""
            },
            "request_id": "cXpr"
        }
    ]
}

Stage 3 is where I want to insert into my Retool DB with the responses from stage 2.

I am needing to insert the account_id, available, current, mask and name. I assume I need to use the loop function again to 1-by-1 insert all the records from stage 2 response but I am not sure how to do that and If I should use the value and index keys or do I need to use some JS?

I attached a screenshot of the last stage of the workflow and I need to know what to insert in the key value pairs to get the date inserted.

workflow
workflow1393×500 40.6 KB

Any help would be appreciated.

Thanks,

2

Hello..

I was still having issues and hoping someone could give me some assistance please.

Thanks,

3

Hey @Miotx! Set up a similar workflow, there are a few ways to handle this, but chose to use the loop block, for the third step since you mentioned that.

Screenshot 2024-01-23 at 10.33.48 AM
Screenshot 2024-01-23 at 10.33.48 AM1920×760 70.3 KB

When using the loop block, you should be able to iterate over the array of responses from the previous REST query block.

In your Key value pairs section, utilizing the value key word should give you each individual element, and and insert query would be run for each one.

Keying into that object is going to be specific to your data, but there also should be some autocomplete to help you through the process. Let me know if you are hitting any particular errors with this approach.

4

Hi @joeBumbaca

Thanks for your reply. For some reason, I get the error "selected input data is not iterable". Is it the format of the response?

Thanks,

screenshot-0123
screenshot-01231220×729 88.8 KB

5

Got it, yeah I thought your step 2 was a loop and not a singular API call. In that case, you likely need to have your query5 be a JS code block that navigates the response from the REST API and loops through values to invoke a function that will update the DB with the appropriate values.

6

Got it, thanks again. I originally did have step 2 as a loop but it kept timing out so I had to create a new step for each token and that works faster. Would you be able to help me out with the JS code block and functions please?

Thank you,

7

Hey @Miotx I'm not able to write JS specifically for your data, but let me know what you have set up and we can troubleshoot any errors you are hitting.