I would like to get some help with a conversion issue in the Results Grid. I have a Generic Inquiry that I was able to successfully pull in the customer name as the parent name if that field is empty (parent and child are the same company in some cases). I attempted to do something similar with the parent account number using the customer number in place but we have several parent accounts that are not “standard” numerics such as ‘AAMBER’, ‘INTMISC’, ‘C000012’, ‘C000013’, with several others similar to those few. My logic for the parent name substitution is: =ISNULL([BAccountR.ParentBAccountID_description], [BAccountR.AcctName], [BAccountR.ParentBAccountID_description]), the the logic for the Parent Account number is: =ISNULL([Customer.ParentBAccountID], [BAccountR.AcctCD], [Customer.ParentBAccountID]). A conversion error is popping up which I do understand why, but if there is a workaround I would appreciate any help.
Conversion issue(s)
Best answer by lauraj46
Hi
The ParentBAccountID field is an integer. Outside of a formula Acumatica automatically references the schema and displays the substitute key. Within a formula you need to join with the Customer DAC to find the account code.
In the GI you are able join the Customer DAC a second time and alias it as ‘Parent’. Your formula would be something like this:
=IIF([Customer.ParentBAccountID] = NULL, [Customer.AcctCD], [Parent.AcctCD])
I still received a conversion error on the ISNULL and changed it to an IIF. See attached XML for your reference.
Hope this helps!
Laura
If it’s at all possible, maybe we can filter out those oddball accounts as they appear to be End User accounts and not Direct or INT Direct (International Direct) accounts.
+15There may be a couple things going on here. I believe the IsNull expression should only have two parameters:
Also, you should add another instance of the BAccount table as the parent account version (ie ParentBAccount) where ParentBAccount.bAccountID = BAccountR.parentBAccountID and then use this expression:
=ISNULL([ParentBAccount.AcctCD],[BAccountR.AcctCD])
+8Hi
The ParentBAccountID field is an integer. Outside of a formula Acumatica automatically references the schema and displays the substitute key. Within a formula you need to join with the Customer DAC to find the account code.
In the GI you are able join the Customer DAC a second time and alias it as ‘Parent’. Your formula would be something like this:
=IIF([Customer.ParentBAccountID] = NULL, [Customer.AcctCD], [Parent.AcctCD])
I still received a conversion error on the ISNULL and changed it to an IIF. See attached XML for your reference.
Hope this helps!
Laura
Lauraj46, that appears to be working perfectly. Thank you lauraj46 and darylbowman for your input. LIFE IS GOOD!
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.