I have an exercise, that has been squeezing my brain lately, and I haven't come up with a specific solution yet.
Let me explain first, how the process works, I believe it's crucial to understand how records are being added there.
The table above, represents invoices from the highway tolls recieved by the cars rental company. One invoice comes from one entity, and includes several toll gates inside.
In this case, we received 2 invoices, T1 and T2, every single toll inside has a different cost.
On the invoice T1, there are 5 tolls, that have been passed by 2 booking numbers B1 and B2 (two unique customers). The total cost for now for B1 = 6$ (1+2+3). B2 = 9$ (4+5). Customers haven't been charged yet, that's an important information.
Before we attempted to charge invoice T1, invoice T2 from another entity has arrived. On the invoice T2, again the same customers were identified. This time, the customer from booking B1 passed 2 tolls (with the cost of 6$ and 7$) and B2 passed the toll with the cost of 8$.
So now, in total, B1 needs to pay 6$ from T1 + 13$ from T2, and B2 9$ + 8$.
In addition, only once per booking, rental company is adding 15$ of admin fee.
Now, the rental company needs to charge the customers the respective amounts. For this reason we need a view for charging, that ideally would like below, I did it completlely manualy just to picture it to you.
So we've got the list of bookings to bill, with the summarized amount from tolls + unique 15$ each admin fee that needs to be billed. Besides it's giving the attachments from respective invoices and rest of the informations about the customer, so the name, address etc.
The important factor is obviously to consider if the specific toll / invoice was already charged or not, and if the customer was already charged 15$ admin fee.
It seems to me quite complex, but I genuinely believe in the power of this community. @Nathaniel_Grano, it's some kind of puzzle that I've seen you like and you're good at.
Please help me guys
How is your data entry performed?
it looks like you have one table when the data would be better split across multiple linked tables: invoices, tolls, bookings, and customers. You might even need another table to group which tolls are charged in a batch, since you are billing across multiple bookings and invoices.
From there, it is a matter of adding rollups and formula fields.