So I have a RG, Invoices, that has a Total, Balance, Rate, and Paid fields (all number fields). I do this calculation to calculate an amount Due: Due = (Total - Balance) * Rate - Paid. Easy enough.
The issue is that I want to get a sum of all the Due calculations in this repeating group, and I’m not sure how I would go about it. The easiest thing to do seems to be to add a due field to the Invoices datatype, and put in a some backend data triggers to do those calculations. But I’m wondering if there is a way to do that type of calculation without making a new field.
You probably shouldn’t store paid on invoices since as you set it up there are more than one payment.
But is total not total amount of invoice? Is it just total hours? And that has to be multipled by rate?
If so I’d much sooner add total $ amount of invoice, which won’t change after each payment, over due and then you can get total due doing sim of total $ amount - sum of total paid
I have other data types that I didn’t mention (such as a payment data type, which stores the actual payment data), as I was more interested in the actual issue I mentioned, doing the math inside of RG and getting a sum of multiple fields being calculated.
I got it working by added an extra field as I mentioned in my post.
Since you have a payment data type there’s no need for a paid on invoice. You haven’t explained what total is? “Due = (Total - Balance) * Rate - Paid” This doesn’t seem to work.
I’m using this redundant fields to be able to do calculations on RG, as I asked in my question.
Due = (Total (the invoice total) - Balance (what payment is remaining on the invoice)) * Rate (a commission percentage) - Paid (how much has already been paid out to salesperson).
If I wanted to do a get a total Due on a list of Invoices, I can’t just do a Search for Invoices: Each Item’s Total - Each Item’s Balance * Each Item’s Rate - Each Item’s Paid (as far I can tell that is not possible).
I also can’t sort by the amount due, since it isn’t a value stored on the data type. Likewise for paid, balance, etc.
So in my use case, as far I can tell, the easiest way to be able to do both is to have that Due calculation as a field on that data type, and I have it working just fine.
Ok that explains it. Yeah then def need to store those additional fields. I’d think you’d want to distinguish what rate and paid mean (e.g., commissionPaid)…
This topic was automatically closed after 70 days. New replies are no longer allowed.