Lookup an id from one dataset within the dataset of another query

App Building
1

I'm trying this.

Queries
getStatus returns status_id, status_name
getOrders returns status_id, order_id

I want to get the status_name when running an action on a table that only has the status_id.
i.e.
IF getOrdersTable.data.status_id
EQUALS getStatus.status_id
RETURN getStatus.status_name

Currently, I am manually writing it out like this which I know is not the way!
{{variantsTable.recordUpdates[0].priority_id === 4 ? "Express Order" : variantsTable.recordUpdates[0].priority_id === 5 ? "Internet Order" : variantsTable.recordUpdates[0].priority_id === 6 ? "Wholesale Order" : 'Stock' }}

Thanks!

1 Like
2

Might joining the tables together in just one query do what you want?

select orders.order_id, orders.status_id, status.status_name
from orders as orders
join status as status on status.status_id=orders.status_id

If you want to keep the queries separate you can us e JSON SQL query to do this:

select orders.order_id, orders.status_id, status.status_name
from {{formatDataAsArray(getOrders)}} as orders
join {{formatDataAsArray(getStatus)}} as status on status.status_id=orders.status_id
3

Thanks Brad.
I would normally do a join, but this has come up a few times and I thought there might be a better way, given that I run the getStatus query anyway to use in the dropdown tag mapped data.
I might just do the join if that it's the optimal way forward.
Cheers!

4

Ahh ok, I think I have what you are looking for:

I have been doing less joining lately and doing this if I want to pull out the description from a lookup:

{{getStatus.data.find(el => el.status_id===compareToValue).status_name}}

5

Ahhh, seems like it's not going to work with a join anyway, as the ID field is editable.
So if I change it, the name field is not changing.

I'll try out this find method. Seems like what I was looking for! :smiley:
Thanks again.

Also, that's for the session the other day. It catapulted me to a new level.

6

I am very glad that session was helpful!

7

Hi Jason, we are currently building a building an inventory management solution where we are providing full APIs for typical activities in this domain. This should make the implementation way faster. Would you be open for a short call where we could learn more about the use case you are currently building? Cheers, Akio