Filtering across APP, to Return a @SUM of Numbers for category.

Follow

James Hayward

• May 10, 2015 03:11

• In one App (A) I have a collection of expense submitted by employees, in which they assign a 'category' to the type of expense it is, (Flight, Taxi, Meals)
• In the second App (B) I have total Sum of Expenses from App (A) which is across all Expense Types. I having trouble with my next calculation field inside App (B) where my next fields I would like Separate the expense Totals from App (A) by having Total Flight Expense.

Hoping some smart java guru might be able to guide us with a calculation.

0

• 

  James Hayward

  • May 10, 2015 03:54

  Adding to my question.. I have tried the following Calculation by @Rainer though I get a zero result

  var expType = @All of Program ; // from Expense App
  var expAmt = @Sum of Expense Claim; // from Expense App
  var expSum = 0;

  for(var i = 0; i < expAmt.lenght; i++) {
  if(expType[i] == "FLIGHT") {
  expSum +=expAmt[i];
  }
  }

  vacSum

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • May 10, 2015 07:23

  Hi James,

  there are 3 mistakes in your calculation:
  1. you have a typo: it's ".length" (not ".lenght" - it's may typical mistake too :) ) .
  2. it must be @ALL of Expense Claim, not @SUM of
  3. last line must expSum

  Rainer

  1

  Comment actions Permalink
• 

  James Hayward

  • May 10, 2015 07:48

  Thank you @Rainer

  Have cleaned it up and seems to working like magic.

  var expType = @All of Program ; // from your leave app
  var expAmt = @All of Expense Claim; // from your leave app
  var expSum = 0;

  for(var i = 0; i < expAmt.length; i++) {
  if(expType[i] == "UT") {
  expSum +=expAmt[i];
  }
  }

  expSum

  0

  Comment actions Permalink
• 

  Rainer Grabowski (FVM)

  • May 10, 2015 08:08

  Great. But take care to set the the right category value in this line (it's Case sensitivie and must be exactly the text of your category)
  if(expType[i] == "UT") {

  Btw: You can delete // from your leave app
  Everything behind the // is only a comment which isn't needed for the calculation. // tells Javascript, not to use what's behind it.

  0

  Comment actions Permalink
• 

  Scott Anderson

  • February 01, 2017 00:28

  This worked for me as well.  Thanks to you both!

  0

  Comment actions Permalink
• 

  Scott Anderson

  • March 13, 2017 18:22

  @Rainer - Help..  I used the below code and, at first it seemed to work fine, but then noticed miscalculations here and there.  

  I have a 'Rents' app which is where all our tenant rent information is stored.  If a tenant has a 10 year lease.. there are 10 rent items.  Only the Rent Types equaling 'Current' or 'MTM' are of interest to me for the sake of building a rent roll report pdf in GF.  

  I have a 'Properties' app with calculation fields that totals up all the Base Rent items that equal 'Current' or 'MTM' items in the Rents app.  I used the following code.

   

  var expType = @All of Rent Type ;
  var expAmt = @All of *Base Rent;
  var expSum = 0;
  for(var i = 0; i < expAmt.length; i++) {
  if(expType[i] == "Current" || expType[i] == "MTM") {
  expSum +=expAmt[i];
  }
  }
  expSum

   

  THE PROBLEM - I noticed that the resulting Total Base Rent amount is sometimes correct, and sometimes not correct from property to property.  I have checked my apps, the related app items and all looks good.  I can only think it has something to do with this code.. but since I am no code guru.. I have no idea how to troubleshoot this.  ??  Help.  These reports are due to our clients, and a few of them have gone out with this miscalculation.  Thank you.  

   

  0

  Comment actions Permalink
• 

  Rainer Grabowski

  • March 14, 2017 12:23
  • Edited

  Hi Scott,

  is it possible that some of the fieds  Rent Type and/or Base Rent in the Rent app don't have a value (= null)?
  If so you have to take the variable tokens "with null" instead of the normal variable tokens in the calculation field. The code should then look like this: 

  var expType = @All of Rent Type with null ; 
  var expAmt = @All of *Base Rent with null; 
  var expSum = 0;
  for(var i = 0; i < expAmt.length; i++) {
  if(expType[i][0] == "Current" || expType[i][0] == "MTM") {
  expSum +=expAmt[i][0];
  }
  }
  expSum

  0

  Comment actions Permalink

Please sign in to leave a comment.