Hello there,
We are trying to allow our user to export their invoice data.
Here is our database structure.
- Table 1 :
invoices
- Table 2 :
lineItems contains an invoice field of type invoices
- Table 3 :
subLineItems contains an invoice field of type invoices and a lineItem field of type lineItems
Every invoice contains at least 1 lineItem.
A lineItem contains between 0 and n subLineItem.
An example of what we are trying to achieve :
| invoice |
name |
amount |
| invoice 1 |
lineItem.name.1.1 |
lineItem.amount.1.1 |
| invoice 1 |
sublineItem.name.1.1.1 |
sublineItem.amount.1.1.1 |
| invoice 1 |
sublineItem.name.1.1.2 |
sublineItem.amount.1.1.2 |
| invoice 2 |
lineItem.name.2.1 |
lineItem.amount.2.1 |
| invoice 2 |
… |
… |
The issue lies in “merging” lineItems and subLineItems.
The path we are currently exploring :
Any help would be appreciated even if it’s just some other path to explore 
You will need a workaround. Nothing complicated. I do it a lot in my app and I am also using 1T - CSV Creator, which is an amazing plugin.
Let’s do it step by step…
First of all, it is important to know: Can you reproduce the desired result using a repeating group?
If you can, we are almost done 
. If not, we will need to go back a few steps, as this is important.
Thanks for the reply
I can not reproduce the desire result using a single repeating group - unless I am missunderstanding the :merge with operator.
I can reproduce the desire result using a repeating group within a repeating group since there is 2 datatypes (Lineitems and Sublinems)
As long as see, you have two problems.
The fist one is the fact that you can have lineItems withou any subLineItems inside, what makes mandatory to make two searches, one looking for the lineItems in the selected invoice and other looking for the subItemLines. The second problem is that, in your case, to use the operator “merge with” is not an option, as Bubble will not allow duplicate values in the same list. What is a paing in the a**. So you will need a workaround for this…
I think we can solve this using the command “For Each”, present in the free plugin “Ultimate Toolkit”.
This CSV needs to have an order or we can just put every line Items and subLine Items randomly at once? It will make things easier.
Yes it needs to be ordered by invoice as displayed in the example table.
I just installed the “Ultimate Toolkit” plugin but I cannot find any documentation.
Edit : I found the For each command but I am not too sure what to do next. Also I should point out that the CSV will most of the time contain hundreds if not thousands row so it might be better to handle it as backend worklows ?
Hummm, so if is going to be thousands of lines, it is better to not use “For each”. I explored some other solutions and I think it will work for what you want.
Database “Invoices”:
Database “Items”:
Database “Subitems”:
I Also created a database to store your eported CSV data. You can keep the records or delete they after you generate your CSV:
The Item (list) column will store all the items and subitems name. And the Quantity (list) column will store all the items and subitems’s quantity. Ok… so the main problem is that Bubble will not allow duplicates itens in your list, and as you can see, we have duplicate values in the Quantity columns of my Items and Subitems. So we will need to use a glitch to force Bubble to insert duplicate values in your list of quantity. We can do it by converting a list of numbers in a text, splited by commas, and after, converting this text back in a list, using the comma as delimiter. I don’t know why, but works. That is why I also created a Quantity (txt) column.
So, I created a 3 step workflow:
The first step is to create your CSV. Note that I am formatting the quantities of my Items and Subitems in a text, splited by comma. And storing in the Quantity (txt) column.
In the second step, I am making a change in the created CSV record, converting the Quantity (txt) column in a list of numbers. I am using the option “extract with regex” to split the numbers of the string by the commas and, in the end, converting each value to number.
Regex:
[^,]+
In the last step I am creating a Dynamic CSV file. The result is this:
Hope it help!
3 Likes
Thanks for taking the time to make that awesome explaination
Tell me if im wrong but based on your answer and some other post towards similar matters it seems that there is no way to export data from 2 nested table in 1 csv without creating a new table combining the 2 nested table ?
Ive got one concern with the method though, I fear for the performance when storing lists of thousands of data in one single row.
In the example I gave there is only 3 fields (invoice, name and amount) but in reality we will have more than that for each item (taxRate, taxAmount, paymentMethod … etc)
Also how do I group items by invoice if I store lists of quantity and itemNames ?
That makes me thing of a relatively similar solution: I could use API workflow to copy a list of lineItems and subLineItems into one new table itemsToExport (or something) then create a CSV based on that new table and send it by email to the user. That way we’ve got an asynchronous workflow that will work without preventing the user from performing other actions in the meantime.
What do you think ?
This is easy. You can create another column called “Invoice” in the itemsToExport table. And use this column to store Items and Subitems’s Invoice number. The same process I did for Items’s name and quantity.
Yes, for sure!!! It will be much better if you could do it using a backend workflow.
1 Like
