Solved

Using Attributes in formulas within a field

jesuscruz97
Rank

Hi: 

I'm trying to create a GI of Invoiced Items, but I want to separate the items by an attribute.  The GI is going to be summarized by Sales Rep and the Sales would be in 3 Columns. Attribute1 Value1, Attribute1 Value2 and Attribute1 Value 3. 

I create a formula like: =IIf( [InventoryItem.COMI_Attributes]='VO5', [ARTran.ExtPrice], 0.00), it validates. 

 

but when I run the GI I got this error. 

Error: The virtual field COMI_Attributes cannot be used in a condition expression.

 

How can I get my GI to work. 

 

Thanks 


Jesus.

 

icon

Best answer by mikeho 5 April 2023, 23:01

View original
Jesus Cruz

16 replies

M
Rank
Userlevel 5
Badge +1

I’ve run into the same issue - trying to use an Attribute in a formula in a GI and getting the same error message. Hoping someone can help provide direction!

D
Rank
Userlevel 7
Badge +5

One way would be to join the CSAnswers table into your GI. That’s where the attributes live. Then you’ll be able to use the value from CSAnswers in your formula.

M
Rank
Userlevel 5
Badge +1

@Django - How would you suggest joining the CSAnswers table to the Vendor table (for which I have my attribute I’m trying to use in a formula)? I’ve tried the below join as well as by attributeID_description and neither of those worked.

 

 

D
Rank
Userlevel 7
Badge +5

You connect to CSAnswers using the NoteID from the parent table (in your case from the Vendor table) to the CSAnswers.RefNoteID field.  Then filter on the CSAnswers.AttributeID for the attribute that you’re interested in. 

This will give you a one to many join condition so you might have to play with conditions and filters if the addition of that table impacts your output.

M
Rank
Userlevel 5
Badge +1

Thanks, @Django! I joined them by the NoteID/RefNoteID and then added a relation so that CSAnswers.attributeID = ‘[AttributeID I need to use in a formula]’ and that worked perfectly.

K2a
Rank
Userlevel 4
Badge +1

Thanks, @Django! I joined them by the NoteID/RefNoteID and then added a relation so that CSAnswers.attributeID = ‘[AttributeID I need to use in a formula]’ and that worked perfectly.

Any interest in sharing a screenshot?!   I have been trying to accomplish this just reading the descriptions and it’s not happening.  Cheers!

M
Rank
Userlevel 5
Badge +1

@K2a - sure, no problem.

Here’s the Relation I used:

 

Where VEND3PAZ is the Attribute ID:

 

K2a
Rank
Userlevel 4
Badge +1

Thanks for that!  I’m still getting an error when trying to use it in a formula.   I am using the “customer” table, not the vendor, but should be the same essentially:

 

And my formula, I’m simply trying to leverage an IF statement, when box is checked it outputs “Required” if the box is not checked “Does Not Require”: 

But I get the following error:

 

M
Rank
Userlevel 5
Badge +1

@K2a - try using this:

=iif(‘DOCAPPROVE’=1,’requires’,’does not require’)

K2a
Rank
Userlevel 4
Badge +1

@K2a - try using this:

=iif(‘DOCAPPROVE’=1,’requires’,’does not require’)

I tried that before as well, it gives me the same error. 

M
Rank
Userlevel 5
Badge +1

@K2a - I suppose it also depends on your attribute type. If yours is a checkbox, try referencing the value field instead:

=iif([CSAnswers.Value]=1,’requires’,’does not require’)

K2a
Rank
Userlevel 4
Badge +1

@K2a - I suppose it also depends on your attribute type. If yours is a checkbox, try referencing the value field instead:

=iif([CSAnswers.Value]=1,’requires’,’does not require’)

It is a checkbox.   So i tried using the actual attribute from the ‘fields’ list: =iif([Customer.DOCAPPROVE_Attributes]=1,' requires ', ' does not require ')

Not sure why it appends it with the ‘_attributes’  but I tried this before and this is how I actually got to this thread because I got my original error message relating to the ‘virtual’ field: 

 

K2a
Rank
Userlevel 4
Badge +1

and if I try: =iif([Csanswers.attributeid]=1,' requires ', ' does not require ')...it brings me back to the error converting to INT message...

M
Rank
Userlevel 5
Badge +1

@K2a - can you try specifically using the [CSAnswers.Value] field?

=iif([CSAnswers.Value]=1,’requires’,’does not require’)

K2a
Rank
Userlevel 4
Badge +1

@K2a - can you try specifically using the [CSAnswers.Value] field?

=iif([CSAnswers.Value]=1,’requires’,’does not require’)

OK!  That seems to have worked.  I guess the challenge is if I want to add more attributes to my GI :/

Thanks @mikeho !

M
Rank
Userlevel 5
Badge +1

@K2a - great! Yes, if you want to add more attributes you would probably need to add the CSAnswers table to your GI again with a different Alias (e.g. “CSAnswers2”), do the same joins to the Customers table and reference the Value field from that new table in any formulas (e.g. [CSAnswers2.Value]).

Reply


Terms & ConditionsCookie settings
About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved