Sum of values if condition is met - looping through records?
Given a table 'invoices' with a numeric 'value' field and a choice 'status' field (status being 'pending' and 'paid') how can I iterate through all invoices and calculate the sum of all paid invoices and the sum of all pendind invoices?
I think I need a for loop that will iterate through all invoices records, and a conditional sum function inside it, but so far, no success on creating this.
All help appreciated, thank you!
4 replies
-
Something like this in a formula field:
sum(select invoices where text(status) = "pending").value)
for the pending invoices and for the paid invoices change pending to paid.
might need some tweaking because it's not checked.
Steven.
-
Thanks for the quick reply, it really helped. Half of the problem is solved - i forgot to mention an important thing: 'invoices' table is related to a 'projects' table, so I will need to sum only the invoices that belong to a certain project. Tried:
sum(select invoices where text(status) = "pending" and text(project) = "project1").value)
but this does not work...
-
I am getting somewhere! After some trial I managed to do this:
sum((select invoices where text(status) = "pending" and text(project.name) = "hardcoded project name").value)
Project name is still hardcoded in the function, I still need the function to evaluate the current project and calculate the sum for that project. Will keep trying but all insights very welcome!
-
Made it. I don't know if there is a cleaner way but did it like this:
let currentProject := Name;sum((select invoices where text(status) = "pending" and text(project.name) = currentProject).value)Thanks for the precious help.
Content aside
- 4 yrs agoLast active
- 4Replies
- 1054Views