4 Replies Latest reply: Apr 25, 2013 9:59 AM by Marc Livingston RSS

    Formula Returns Null. Should return value

      I am having issues with the following formula. It seems to not like part of it  and returns null which returns null for the entire formula. In the case of {<trantype={'MFG-CUS','MFG-STK'}>}

      there are actualy no records to return which I am guessing is why I am getting a null result instead of a 0 result. Is there anyway to tell that section that if no records are found then to return a 0?

       

      sum({<trantype={'MFG-CUS','MFG-VAR'}>} tranqty *( lbrunitcost))  //Returns 0

       

      +

      (

      ((//[Labor to Date]

      sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

      +

      sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 +  if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

      if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

      -

      //[Inventory Labor]

      sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

      -

      sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>} (0 + tranqty *( lbrunitcost )))) // value returned 1,036.16

       

      *(sum({<trantype={'MFG-CUS'}>}if(jobclosed='Open',0, tranqty)) /   (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0,  tranqty ))))) //This returns null causing the entire section to be null. Actual value returned is 0

       

       

      +

       

      ((

      (//[Labor to Date]

      (sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

      +

      sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 +  if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

      if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

      -

      //[Inventory Labor]

      sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

      -

      sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>}(0 +  tranqty *( lbrunitcost ))))) // This returns 1,036.16

       

      -

       

      ((//[Labor to Date]

      sum({<jobclosed={'Closed'}>}(0 + (laborhrs * laborrate)))

      +

      sum({<trantype={'*-MTL','*-DMR','WIP-MFG'},jobclosed={'Closed'}>}(0 +  if(wildmatch(trantype,'*-DMR'),(tranqty * lbrunitcost * (-1)),

      if(wildmatch(trantype,'ADJ-*'),lbrunitcost, tranqty * lbrunitcost))))

      -

      //[Inventory Labor]

      sum({<trantype={'*-STK','MFG-WIP'},jobclosed={'Closed'}>}(0 + tranqty * lbrunitcost))

      -

      sum({<trantype={'MFG-CUS','MFG-VAR'},jobclosed={'Closed'}>}(0 +  tranqty *( lbrunitcost )))) // value returned 1,036.16

      *(sum({<trantype={'MFG-CUS'}>} if(jobclosed='Open',0, tranqty))/ (sum({<trantype={'MFG-CUS','MFG-STK'}>}if(jobclosed='Open',0, tranqty ))))) //Returns null should return 0

      ))

      )

       

      Final Value returned should be 1,036.16

       

      (0 + (1,036.16 * 0) + (1,036.16 - (1,036.16 * 0 )))= 1,036.16