data split between three tables, how to join all of them

JonathanL

Hey, I am trying to build a project regarding tracking of expenses, thus far I've done it using google sheets but I'm trying now using Xano and Retool.

my three tables are:

Categories:
id
name 

Locations:
id 
name
category_id

Transactions: 
id
amount
date
location_id

my main flow will look this:

open the app -> input amount -> input location from a list -> input date of transaction -> submit

I will have a page to input a new location and a new category if they are not already there.

and I want to be able to have a few views for the data:

• table view of all transactions (exists - get api call for transactions table)
• table view of amount spent per location (exists - get api call for transactions table joined with location and using eval to add the locations.name to the returned values)
• table view of amount spent per category (doesn't exist - need help here)

I'm sure I'm not doing it correctly but what my thought was to somehow join the transactions and locations tables and include the location.category_id and to join on that with the categories id.

not sure if I even made myself clear so let me know!

Thanks :)

brian

Hi @JonathanL we built a snippet to show you how you could provide the table you are looking for at:

https://community.xano.com/discussion/4178743/categorize-expenses-by-location/

The API results provide:

• Category Name
• Category ID
• Sum of Transaction Amounts

Feel free to reply there with any questions or suggestions to make it as helpful as possible!

nocodetalks

Instead of join , use the Add on.

Checkout in-detail here- https://www.youtube.com/watch?v=t9qXwFVUZZ0

Follow me on twitter.

JonathanL

@nocodetalks First of all thank you for the answer!

But unfortunately for some reason when I tried to use addons retool didn’t know how to properly use that data to create a table from it, I will try again to see if I somehow messed it up, but if addons don’t work for me, is there another way to do it?

nocodetalks

why?

Addons are just adding value into response.

You can use the join too in Xano.

JonathanL

@nocodetalks my bad about it, I'm not trying to create a table but a chart and it can't get the data from the addons for some reason as it's not in the dataset even when it's passed on. maybe it's because it's a list of objects or something I'm not really sure...

as you can see in retool when I query the data after I created my api it returns all the data I want but the addons are a list of objects, and the retool doesn't show the transactions_amount for example as an available dataset

sorry if it doesn't make much sense, I'm sure I'm doing something wrong here.

Edit: when I try to use it at a table it also shows up as the full object and does not split it to it's different values. so no luck there also.

JonathanL

I should've watched the tutorial before asking - in this video (Query all records - filter, search, sort, aggregate, joins & More!) Michael talks about just clicking again on the "edit" button and it will join another table, I guess it was just a bit unintuitive for me that it would add another join but now I know!.

I'm still not sure on how to achieve the result I want but at least now I know I can do multiple joins.

nocodetalks

@JonathanL

Not sure why retool don't show the "transaction_amount" list, but I have integrate on Bubble and its works.

Ray Deck

@JonathanL in the event the query function is not producing data in the exact right shape, you can loop over it and modify the variable before submitting it for output. Imperative data transformation is an area where Xano really shines compared to other backend systems. Just use the + button to add more functions. Useful in this situation will be the for...each under data manipulation (to loop over your records) and update variable (to change each record into a shape more amenable to retool).

JonathanL

@brian Thank you for the snippet! I tried to use the addon option of not creating a name for it to be under so it's the same level as the rest of the data but for some reason that didn't work before. but I'll try again now that I have this great guide.

Really awesome.