Post object with sublist or child items (array)

FJP H

Hi there,

I was trying out a use case posting an invoice with invoice lines. I searched this community and based on the results I watched some tutorial videos which gave me new insights but also confused me about the best approach. So I hope someone can help.

Current approach
I have two tables

Table: invoice
Table: invoice_line (reference field: invoice_id)

Instead of using separate POST API calls for first creating an invoice and then create each invoice line separately. I would like to create a invoice with associated invoice lines in one POST.

I started modifying the original POST invoice endpoint. I tried to add an object (list) named as invoice_lines as input and configured the structure to match it with the properties of the invoice line table

But then I got stuck how to expand the function stack of this call. How can I add all the invoice_lines from the request to the invoice_line table and link it to the invoice record created in the first step in the function stack. I was now thinking to use a loop operation to iterate over the invoice_lines input array to create each invoice line.

But then I saw a video that showed that you can add a reference as a list. So besides using an reference to invoice (single item) on the invoice_line table. I could (also) use a reference to invoice_line (list) on the invoice table. But it seems redundant or sub optimal from a database perspective.

Besides that I have also read something about using add-ons for this, but I only used add-ons in a GET request to add related data to the response, but I don't understand yet how to use add-ons as input on POST requests.

Can someone please guide me to the right direction/best practice performance wise?

Ray Deck

This is exactly what the function stack is good for! The API doesn't have to match the structure of the underlying tables. You can take the inputs and perform logic on them, like:

1) Create the invoice from some of the data points, which returns its ID among other fields; and then
2)  looping over the list of lines to add them to your invoice-lines table, feeding invoice id to each line to maintain reference.

This all takes more work than just using the automatically created endpoints, but not much more, and it's really common.

Add-ons are a little bit of a red herring in this context. They are useful for when you are querying one table and want related data from other tables. For example, if you want to get the invoice lines when you request an invoice. They can be handy in a number of analytic and retrieval circumstances. But here you just need to tease apart your input payload and put the right data into the right tables.

Helping you figure out this kind of function stack logic and helping you implement what you want to create is also what I do professionally. You can schedule a 1-1 session to walk through your needs and hopefully end with your desired workflow implemented. Usually, this is an hour or less. 

FJP H

 thanks. Just what I thought. No help needed now. Just figuring out the capabilities of the platform from personal interest.

Regarding the datamodel. Am I right that it is better to use my current setup. Invoice line references a single invoice item, instead of invoice referencing a list of invoice lines?

Ray Deck

depends on how you want to use the data. In a classical relational database, the lines table would contain a reference to the invoice table. However, Postgres and xano offer the use of more complicated documents in a single record. So you could have an invoice that contains an array of lines within it - no lines table necessary. That is a model available now that was no for people making invoicing systems a decade ago. Depending on your need to index the lines, it could be simpler.

FJP H

Thanks. Good to know the capabilities Xano/Postgres offer. I will try out both flavours. I do like option to just add a property on invoice that contains the lines.

Besides that, I succeeded to alter the Invoice POST endpoint. Ik creates the invoices and invoice lines from one JSON Request. Also it calculates the total price of an invoice line using the multiply function.

jekenstedt

This is very useful and it would really help if you could publish a video of how you designed the endpoint or just share an image of how you did it.

Keep on the good work with Xano!

jekenstedt

I solved it after several tries. I created a variable for the id of the record I posted to the first table and used that when I posted to the other table.

I attach an image and hope it could be useful for other people.

FJP H

Hi, I now have I different approach. When a user created an Invoice in the front-end I do vreate a Invoice in draft status withlut Invoice lines. Then on the invoice page, user can add invoiceines. Each line is Posted to Xano and linked to the invoice. The user can set the invoice final when ready.