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( IInventoryItem.COMI_Attributes]='VO5', 5ARTran.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.
Page 1 / 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!
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.
@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.
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.
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.
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!
@K2a - sure, no problem.
Here’s the Relation I used:
Where VEND3PAZ is the Attribute ID:
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:
@K2a - try using this:
=iif(‘DOCAPPROVE’=1,’requires’,’does not require’)
@K2a - try using this:
=iif(‘DOCAPPROVE’=1,’requires’,’does not require’)
I tried that before as well, it gives me the same error.
@K2a - I suppose it also depends on your attribute type. If yours is a checkbox, try referencing the value field instead:
=iif(fCSAnswers.Value]=1,’requires’,’does not require’)
@K2a - I suppose it also depends on your attribute type. If yours is a checkbox, try referencing the value field instead:
=iif(fCSAnswers.Value]=1,’requires’,’does not require’)
It is a checkbox. So i tried using the actual attribute from the ‘fields’ list: =iif(sCustomer.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:
and if I try: =iif((Csanswers.attributeid]=1,' requires ', ' does not require ')...it brings me back to the error converting to INT message...
@K2a - can you try specifically using the CSAnswers.Value] field?
=iif(>CSAnswers.Value]=1,’requires’,’does not require’)
@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 !
@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. 0CSAnswers2.Value]).