Most Efficient way to add a table referenced record?

Bryan

Hello all,

I'm trying to find the most efficient way to setup my database.

I have two tables, one called Contacts and the other called Invoices

Contacts Fields
Contact ID:
Name:

Invoices Fields
Status
Amount
Contact ID (table reference)

I'm getting the data for these two tables from an external API.

My first GET call is to get ALL contacts from this other system and record to the DB. That part is fine

The second part is I want to create the invoices, but what is the best way to pass the Contact ID through during the creation request? Do I have to query each invoice received via the API (the data contains the external Contact ID) against the Contacts table to find the relevant ID? It just seems largely inefficient. 

I know since I did the GET all contacts call, that my contact 100% exists in the Contacts database, so is there an easier way to fill out the Contact ID field in the invoice table?

Sam Bevis

Hi Bryan,

First thing I would do is watch this: https://www.youtube.com/watch?v=hulHk9v3Phk

It might help get you to your next step.

As for your question, I think you are asking about the "input" of a Contact ID?

So in your (1) Inputs hit the plus and go to database link and link to your invoice table. This gives you access to the fields.

Then in (2) you can add a record into your invoice table with a contact ID which will link it to the contact database table?

Not sure if thats what you were asking but hopefully it helps.[image.png]

Ray Deck

Under the covers the "contact ID" is just a number. Pass the "id" of the contact into that field and you should be able to continue on your way. 

If the problem is finding the ID when you already have the list in hand, you can search your array of results either using a filter or setting up a foreach loop to find the correct ID from the in-memory list of contacts. That way you don't have more database calls. 

Finally, I will note that indexed database calls are pretty cheap, so I'd not look for the "most efficient" solution, but rather the one that is "fast enough" so you can move on to the next problem. The question of which solution is "best" probably relates more to what is most intuitive for you so you can maintain it in the future. 

Bryan

Thanks  , that video helps heaps!

 yeap, totally get what you mean! Just wanted to explore if there was some revolutionary way of doing it given how powerful Xano as a product appears to be (still learning but loving every second of it !)

In Bubble, every table you created in the database had a column value called Slug.
Essentially you could toggle between the row's unique ID or the Slug when referencing the table. Because the slug can be set when writing to the database, essentially one would, based on the example above, write contact ID to the slug.

And then when writing the invoices, I would just use the same contact ID instead of an integer to make the table reference. Hence 'avoiding' having to do an individual search query per invoice contact.

However, I'm guessing bubble does in the unseen backend what that video (that Sam shared) does if a user opts to use the Slug as the reference, but little neat tricks like that do help you save on time and goes a long way in pushing no-code tools to the frontier.