Reporting several invoices from vendors table

[Jose_Munoz]

I have 5 tables:
A. invoices (many invoices from each vendor).
B to E. Advance payments, packaging, and other amounts they should deduct from the total in A.

I want to consolidate the total to pay, per vendor, after discounts (A-(B+C+D+E)), and have a detailed report for each vendor (invoices - deductions).
This last action is my challenge, the report for each vendor should include invoice & debit numbers, and send it for each vendor.

I could use Integromat to automate some processes, but I can’t get the detailed report per vendor yet.
Any ideas?
Thank you all in advance.
JRM

[Databaser]

Hi @Jose_Munoz

Should be doable in Airtable I think. Did you link every table to a “vendor” table where you can (conditionally) rollup the data from A-E?

[Jose_Munoz]

Hi @Databaser,

I used the rollup function, but I got a linear list of documents ID and Values. I need to present them in an organized way. Vertical list (of documents) and its values (in front of them) and the total.
Look what I got until now. …

[Databaser]

Jose_Munoz:

This last action is my challenge, the report for each vendor should include invoice & debit numbers, and send it for each vendor.

My bad, I was focused on the first part of your question. Wouldn’t know how to do it in the records itself, like you describe it. Did you consider using the page designer app? Via the app you should be able to get an overview in table form for the linked records and their data.

And otherwise, someone will no doubt jump in and point out options via Zapier, Integromat or scripting :slightly_smiling_face:

[Justin_Barrett]

If I understand things correctly, the data in the {Documents}, {Values (list)}, and {Total} fields are all rolling up from the same table. Is that correct? I’ve got a possible solution, but I noticed that some of the entries in the {Documents} rollup are duplicates. Looking at the first record, for example, you’ve got more than three document IDs listed, but only three values in {Values (list)}. What’s the relationship between those fields? Also, what’s the relationship between the total in the {Values (list)} field and the {Total} rollup? I don’t want to make assumptions about how the data is organized and tied together, so the more info that you can provide, the easier it’ll be to know if my idea will work.

[Jose_Munoz]

Hi @Justin_Barrett
Thanks for your help.
For any reason, they use the AWB as ID, however I can use other column (PO #) to identify each register, as you can see the the screenshot. There you can see better the structure of the data.
The first table (Vendors) has the AWB/PO´s,
Tables Payments, Packing and Others must be deduced from the total in table Report.
Finally, the report should be shown well organized (vertically) to be sent to each vendor individually.

[Justin_Barrett]

That doesn’t directly answer my question, but what I gather from that is that these rollup fields are all collecting data from different tables. Is that correct? If so, then I don’t that this problem can be solved (at least not easily) with a formula. You’re going to need to build a much more robust reporting setup, which is going to be tough to discuss and construct in a forum thread. My gut says that you should hire someone to work with you directly to build an effective reporting system for your needs. Unfortunately I’m not able to assist (my plate is quite full), so I recommend posting to the #developers:work-offered category.

[Jose_Munoz]

Thanks @Justin_Barrett.
The answer is yes, the rollup fields should collect data from different tables.