Suming all previous amounts in one app related to another

Follow

Richard Hedger

• July 17, 2016 09:45

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.

 

0

• 

  Rainer Grabowski

  • July 17, 2016 20:35

  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);
  }};
  sumPrev

  Rainer
  rg@delos-consulting.com

  0

  Comment actions Permalink

Please sign in to leave a comment.