Calculate the Sum product of three values in a table

Hi there,
I need help calculating the value based on three values in a repeating group
I need to sum (qty x rate x gst)
I am trying to use sumproduct but I can only sum two values
Can anyone help
Thank you

A screenshot would help but if I understand this correctly, I’d suggest storing the values to be summed in a state or states and processing the summation from there. Alternatively, if the RG values are in the DB, you could sum up from there, no?

Thanks for your reply on this!

I’m not sure if a screen shot will help. The situation is basically line items on an invoice. I have two data types:

  • Invoice and
  • Invoice Item
    The invoice items get rolled up into the invoice and each invoice item has
  • Qty
  • Rate and
  • GST%
    I am presenting the three values on varies views
  • Sub-Total (Qty x Rate)
  • GST Value (GST% x Qty x Rate)
  • Total = (Sub-Total) + (GST Value)

I have a workaround where I am doing one calculation and storing it but I do not think that is the best way to do it. I simply want to calculate GST Value (GST% x Qty x Rate) dynamically and there seems to be no simple way of doing it.

Any other input here would be appreciated.

Thanks

Frank

What is the relation between Invoice and Invoice Item? Does Invoice Item have an Invoice field?
If you don’t have it already have a total field on your item itself
Then also go to settings and Versions tab and enable the parenthesis experimental option so your order of operations can be correct

  • Setup a backend database trigger with the condition Item Before change's quantity is not Item After change's quantity or Item before change's price is not item after changes price

  • Add the action “Make changes to a thing” Invoice Item after change then change the Total field to This Invoice Item’s Quantity * This Invoice Item’s Price * GST%

  • Depending on the relationship between the two data types, the next step might be “Make changes to Result of step 1’s Invoice” then change the sub total field with all the math, grand total, etc.


    In my case I’m searching for all items with the constraint Purchase Order field = my purchase order from the database trigger, then summing each item’s total. It gets a little messy but it’s doable

Thanks Tyler,

I have kind of solved it with a workaround/hack and some workflows in the form when the value is changed but I really like your solution and it has some applications in other areas that I am working so thank you for that. To date I have only used the backend database change trigger to create change logs but what you have suggested is quite clever and a great way to solve what I am working on.

I like your solution because it applied the calculation regardless of the change. My solution, I have to consider every scenario and build for that. Massive thank you again…

I love Bubble because there are so many ways to skin the cat

2 Likes

This topic was automatically closed after 70 days. New replies are no longer allowed.