I am stuck for few days at Invoices Amount calculation

[Arash_Singh]

Dear Friends,

I have a Sales Table:

Which provides data such as the start date of an Installment, Commission Rates, and “Invoices Table” calculates the Commission Invoice Amount Payable at the time the Invoices are Created MINUS any Previous Invoices Paid.

When the status of the Invoice changes to Paid, the Revenue field in the Invoices Table gets updated to reflect the Commission Invoice Amount, which gets “ROLL’ed UP” in the Sales Table that adds all the Invoices paid till date against the Sales ID linked to Invoices in Total Revenue Generated.

The “Sales Tables” than minuses the Total Revenue Generated from the Total Invoices Cycles Run To date (Expected Revenue) to give the “Amount to Invoice for the next Invoice”. This Amount to Invoice is LOOKED up in the Invoices Table to give an indication as to when the new invoices can be generated.

The problem is when the Invoice changed to a paid status, the current invoices and all other invoices have wrong Invoice Amount as they all minus the Previous Invoices Paid field in order to calculate the Invoice Amount.

The solution to this is to make the Previous Amount Paid frozen to the date of Invoice which is not affected by the Invoices Status being changed to PAID, also it should add-up all the Previous Invoices Paid at the time of Creating the Invoice and that Commission Invoice Amount should stay frozen

I don’t know how to accomplish that? Please help, as I have been stuck for many days.

[Arash_Singh]

any help in the right direction will highly appreciated

[Bill_French]

Arash_Singh:

also it should add-up all the Previous Invoices Paid at the time of Creating the Invoice and that Commission Invoice Amount should stay frozen

I waited 17 hours for someone like @Justin_Barrett or @ScottWorld to show us how to do this without script. :winking_face: And I believe it’s possible but I predict it will be overly complex for me to understand and just complex enough for the experts that envision them.

I [personally] would tackle this by first embracing the idea that transaction data must be considered far different information items as compared with aggregations. And while Airtable is pretty good at rollups and other forms of summations which are often quite good, these features don’t always produce ideal results in every case.

Arash_Singh:

The problem is when the Invoice changed to a paid status, the current invoices and all other invoices have wrong Invoice Amount as they all minus the Previous Invoices Paid field in order to calculate the Invoice Amount.

Indeed, you need a Paid Status [event] to invoke an update, and this is not easily achieved in Airtable.

Rant for the Day…

That said, the bigger challenge I see often (mostly because my work is often biased toward the more stringent reporting and data visualization work), is that even the best-crafted aggregation process in Airtable is not easily made portable - or even repeatable - in other business apps primarily because of the dependencies typically created using formula fields and lookup fields. These are not real fields, of course, they’re proxy fields and that creates (at times) some challenges.

Another aspect of integral aggregation logic in Airtable is that you cannot easily document it; you can’t back it up; you cannot protect it or defend it from other users. Basically, some of your most important algorithms for doing business are exposed in database setup configurations. This is not a concern in some cases, but far less than ideal in many others. Businesses enjoy competitive advantages through optimized processes and algorithms. Ya’ might want to codify it.

With my rant for the day behind us, here’s how I try to build to the requirements while also embracing a portable - viz-ready/report-ready data set.

1. Summarize and aggregate data into actual fields; avoid formula fields if possible.
2. Push outcomes via script (automated API processes are ideal, but Script Block can suffice, and Blocks will soon support automation I believe).
3. Summations and aggregations are typically the domain of management; best to identify the personas ahead of time and perhaps push such data into a separate base (security issues and all).
4. Rollup, summate, aggregate into actual tables with actual fields.
5. Use those tables for direct reporting (internal to Airtable) as well as exported data via API’s, etc.

Hopefully, one of the two experts noted above with prove my rant was a waste of time. :winking_face:

[ScottWorld]

Lol, thank you, @Bill.French! :stuck_out_tongue_closed_eyes: I’ve got a very busy day today, but I will try to look into this later tonight!

[M_k]

So was I! :grinning:

I was waiting for someone from the Brain Trust Society. :slightly_smiling_face:

I felt bad, since it sounded like help was really needed.

Although, it’s the weekend and things slow down a bit, too.

Mary Kay

[ScottWorld]

Hi @Arash_Singh,

I’m not entirely sure if there is an automated way to do what you want without scripting. Although I’m also not 100% sure that I can fully envision your database without seeing it.

Can you please provide some screenshots of your base?

Or, even better, if your base doesn’t have any confidential information in it, could you share a link to your base so we can make a copy of it and play with it? (You can change the link later, so other people can’t access it.) You can share your base from your Workspace page, by clicking on the little down arrow that appears when you hover your mouse over the base. Then, choose “Share”. After that, choose “Base”, and at the bottom, choose the option to “Allow viewers to copy the data in this base”.

Best,
Scott

[Justin_Barrett]

Bill.French:

I waited 17 hours for someone like @Justin_Barrett or @ScottWorld to show us how to do this without script.

Sorry that I’m late to the party. :winking_face: The bulk of my Saturday was spent wrapping up a video project for an overseas client, and I take Sundays off from a lot of things.

Frankly, the initial description leaves me confused. I can’t tell of some of the things discussed are table names, field names, view names, etc. I don’t have a clear picture of either the current base structure, the calculations involved, or the desired outcome, so I can only echo the request from @ScottWorld for more detail about how things are set up. Screenshots are extremely helpful, with a sample base being the most helpful.

[Arash_Singh]

Thanks guys for reaching out to help me: here is the link, the clients’ data has been removed

The student database is where the student enter their data through a form link or I manually enter it, and then once an education provider ( see below what does an education provider data base does) is assigned ( in the Student database),

Education providers ( has the data about colleges such as name address and Business number, location), Course database keeps data about courses offered by education providers. One we sell ( allocate a course(s) to the student, the Sales is generated related to the entity which has agreement with the education provider, and then invoices are generate related to the Sales.

Don’t really use interaction or Task database just yet, but in future.

( I hope it’s easy to follow)

https://airtable.com/shrLOqcMDGr2FGotw

[Arash_Singh]

Can any one please offer a piece of advice ?

Thank you.

[Justin_Barrett]

I somehow missed the notification that this thread was updated after my post last week.

I just took a look at the base you linked, but it’s completely empty. The tables are there, but there are no records. Without records—even dummy records with sample data—it’s still tough to picture your desired use, the problems you’re encountering, etc. It would help immensely if you could add some records to that base that illustrate the issues you want our help with.

I tend to say this a lot: the more info you can provide, the easier it is for us to help you.