Suming all previous amounts in one app related to another
I have an Invoice App and an Accounts App that are related.
For an item created in the Invoice App I want to be able to sum all the PREVIOUS invoices related to same account.
Can anyone assist with the script please.
-
Hi Richard,
one way is to use Globiflow and link all previous invoices (from the same account) to the new created invoice. Then create a calculation field and enter
@sum of invoice amount.
If you don't use Globiflow it will be a bit more complicated.
1. Create a calculation field "All invoices" n the Accounts App:
var amount = @all of amount field in invoices;
var date = @all of invoice date in invoices;
var result = [];
for(var i = 0, i < amount.length; i++){;
result.push(moment(date[i]).format("YYYYMMDD") + "_"+ amount[i]);
};
result.join(";")You should get something like: 20160630_1000.00;20160710_2000.00 ... etc.
2. Now create a calculation field in Invoices:
var date = moment(@invoice date).format("YYYYMMDD");
var prev = @all of all invoices.toString().split(";");
var sumPrev = 0;
var sumPrev = 0;
for(var i = 0; i < prev.length; i++){
dateString = prev[i].substr(0,prev[i].indexOf("_"));
amount = prev[i].substr(prev[i].indexOf("_")+1);
if(Number(dateString) < Number(date)){
sumPrev += Number(amount);
}};
sumPrevRainer
rg@delos-consulting.com
Please sign in to leave a comment.
Comments
1 comment