3 followers Follow

Calculate balance using subset of references (i.e. IF)

I have two apps: 1) Licenses, and 2) Invoices

The invoice app contains a reference to a single license. From the perspective of the license, it is a one-to-many (1:m) relationship - i.e. a license can have multiple invoices associated with it, but not the other way around.

In the LICENSE app, I have a calculated field "Total Value of License" that sums the yearly royalties to create a total. Next, I want to have a calculated "Balance Due" field that takes the total value of the license and deducts the value of invoices that have been paid to-date. However, I only want invoices marked as "Paid" to be included in the calculation. This would omit invoices marked as "Pending", "Unknown" or "We're suppose to charge them?!" I currently have this command, which does not contain the necessary IF condition:

@Total value of license - @Sum of Amount (of incoming invoices)

I'm getting tripped up on the IF part of the statement and would love a little help.



Please sign in to leave a comment.



Hi Jason,

what you are looking for is this:

var am = @All of Amount;
var status = @All of Status;

var sum = 0;
for(var i = 0; i < am.length; i++){
if(status[i] == "Paid"){
sum += am[i];

@Total value of license - sum

@all of of status = the field where the invoice is marekd as Paid


Rainer Grabowski 0 votes