Skip to main content
Solved

IIF - using account number


jyounes
Jr Varsity II
Forum|alt.badge.img+1

Issue:  I need a formula to work one way when its to account 0500 and another way when it isn't.  

I tried: 0500 ‘0500’ and “0500”, and the formula is only returning values for the false.  

How to I format the expression to return the result for when its True.  

 

= IIf( [GLTran.AccountID]= '0500', ( ([GLTran.CreditAmt] *.5) - ([GLTran.DebitAmt] ) *.5) , ([GLTran.CreditAmt] *.75) - ([GLTran.DebitAmt] ) *.75 )

Best answer by aaghaei

The AccountID is in fact the system internal ID the way you use. You will need to Join GLTran with Account ON GLTran.AccountID = Account.AccountID and then use Account.AccountCD in your formula

View original
Did this topic help you find an answer to your question?

3 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4

Hi @jyounes! For this issue the GLTran.AccountID is actually not the AccountCD, this is a common misstep in creating calculations on ID fields.

Often the UI will switch the ID to the CD value so you can more easily understand what the value represents, but in this case the GLTran.AccountID is the numerical representation of the AccountCD, so it’s always false since you probably don’t have an account with the ID of 0500.

Try adding the =[GLTran.AccountID] field to the GI results to see the ID value of the account you’re looking to create the calculation on, then use this number instead of the 0500


aaghaei
Captain II
Forum|alt.badge.img+9
  • Captain II
  • 1178 replies
  • Answer
  • April 20, 2023

The AccountID is in fact the system internal ID the way you use. You will need to Join GLTran with Account ON GLTran.AccountID = Account.AccountID and then use Account.AccountCD in your formula


jyounes
Jr Varsity II
Forum|alt.badge.img+1
  • Author
  • Jr Varsity II
  • 59 replies
  • April 20, 2023
aaghaei wrote:

The AccountID is in fact the system internal ID the way you use. You will need to Join GLTran with Account ON GLTran.AccountID = Account.AccountID and then use Account.AccountCD in your formula

Added the PX.Objects.GL.Account table and joined it the relations tab.  Added accountCD to the inquiry and referenced it in my formula and now I get the correct results.  

UPDATED FORMULA

= IIf( [GLAccount.AccountCD] = '0500', ( ([GLTran.CreditAmt] *.5) - ([GLTran.DebitAmt] ) *.5) , ([GLTran.CreditAmt] *.75) - ([GLTran.DebitAmt] ) *.75 )


Reply


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings