Transforming ids to object inside a query

[dbeaulieu007]

In my data model, I have clients and teams. Each client is assigned to a Team.

{
    "count": 188,
    "next": "https://dev.api.npn-purposeadvisor.com/datahub/api/v1/clients/?limit=10&offset=50",
    "previous": "https://dev.api.npn-purposeadvisor.com/datahub/api/v1/clients/?limit=10&offset=30",
    "results": [
        {
            "id": 700042,
            "client_status": "Other Contact",
            "display_name": "Michal Lapaj",
            "advisor_id": 19,
            "team": 1,
        },
        ...
     ]
 }

The return of the get Teams follow a similar syntax:

{
    "count": 23,
    "next": "https://dev.api.npn-purposeadvisor.com/datahub/api/v1/teams/?limit=10&offset=10",
    "previous": null,
    "results": [
        {
            "id": 1,
            "name": "Team One",
        },
        ...
      ]
}

I would like to transform the Team in the client to include the Team info as follow:

{
    "count": 188,
    "next": "https://dev.api.npn-purposeadvisor.com/datahub/api/v1/clients/?limit=10&offset=50",
    "previous": "https://dev.api.npn-purposeadvisor.com/datahub/api/v1/clients/?limit=10&offset=30",
    "results": [
        {
            "id": 700042,
            "client_status": "Other Contact",
            "display_name": "Michal Lapaj",
            "advisor_id": 19,
            "team": {
                id: 1,
                name: "Team One"
            }
        },
        ...
     ]
 }

Can this be done without changing the back-end rest endpoints?

[melohagan]

Hey @dbeaulieu007

As this is a REST API, you will need to make the two API calls regardless. With that in mind, you have two options - match the data in the Design section with nested data providers, or match the data in an Automation.

For best performance I recommend the automation approach:
https://docs.budibase.com/docs/js-scripting

So your automation would look roughly like so:

1. App action trigger
2. Query Rows (teams)
3. Query Rows (clients)
4. JS Scripting block (use JavaScript to aggregate the two responses into one)
5. Use the Collect block to return data to your app
6. Put that response data into App state
7. Use the App State as the custom JSON/CSV source for your Data provider

---

With that said, ideally your API would allow you to get the teams for a given client, or the clients of a given team. Something like:
api/v1/clients/:teamId or api/v1/teams/:clientId