Solved

Substring Function for an Attribute

  • 15 June 2023
  • 8 replies
  • 268 views
K
Rank
Userlevel 4
Badge

We have a GI that pulls all our stock items that were/ on a sales order along with their dimensions like Length, Width, depth using attributes. 

 

We are using an attribute that has a prefix of ‘ID :’followed by the length dimensions. We would like the GI to only display the numbers and not ‘ID :’

 

We used substring function to only display the numbers. 

Substring function 

Since we are using an attribute in the substring function, we get the below error: 

Error

Is it possible to split/ display only certain characters from an attribute?

icon

Best answer by lauraj46 15 June 2023, 19:49

View original
Kaavya

8 replies

lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

There are certain GI limitations when using the virtual attributes fields. 

When you run into such a limitiation you can instead join on the CSAnswers table. 

The linkage is from the InventoryItem.NoteID to the CSAnswers.RefNoteID field. 

Use a “left” join on the relations tab and include the ‘AttributeID’ in the join criteria to specify the attribute that you need.  To include multiple attributes you would need to join the CSAnswers table more than once.

Add the ‘CSAnswers.Value’ field to the results grid and you should be able to apply the substring formula there.

Hope that helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46 , Thank you for the recommendation. I did try your suggested way before - but I am unable to convert them into a decimal number. I have used Cdbl and CDec functions. 

The one underlined in orange below is being directly pulled from the attribute and the one in yellow is from CSAnswers table.


Our team wants the capability to filter the numbers (where they get an option for greater than, less than, etc.). When we use the attributes directly, it gives us an option like one below -

But this doesn’t work when using CSAnswers tables as values are stored in nvarchar data type. 

Do you know how I can achieve this?

Kaavya
lauraj46
Rank
Userlevel 7
Badge +7

Hi @kanupindi ,

There’s no need for a conversion unless you will be doing calcuations in the formula.  The search options can be controlled by specifying a ‘Schema Field’ for the column.  Choose any numeric field that is formatted in the format that you need for your attribute.  You can even use a field from a table that isn’t part of your GI, just add it to the Tables and do not create any Relation to it.  Be sure to specify a Caption for the column, otherwise the column heading will also come from the definition of the field that you select as the Schema field.

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
K
Rank
Userlevel 4
Badge

Hi @lauraj46 , good idea!  I completely missed that. This works! Thank you!

Kaavya
P
Rank

@lauraj46 

I had the same issue as described above and I followed your solution above not to convert the data type and just add a decimal field in the Schema Field, however when I try to filter the values from the header filters I’m getting an error that says “Object must be of type Decimal”

can you please help me solve this issue?

lauraj46
Rank
Userlevel 7
Badge +7

Hi @Pinny,

I tested in demo data and the filter function worked for me.  Is all of the data in your attribute either blank or numeric?

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.
P
Rank

@lauraj46 

Yes, they’re all numeric values with decimals or just blank.

were you able to perform a between filter as well? to get the values between two numbers.

lauraj46
Rank
Userlevel 7
Badge +7

Hi @Pinny ,

It seems to be the decimal points that are causing this issue.  I was able to get it working by combining the schema field with a formula like this:

=CDec(CDbl([InventoryItem.MYATTRIBUTE_Attributes])*100)/100

You might have to increase the multiplier if you have more than two decimals in your data.

Hope this helps!

Laura

Laura - https://www.linkedin.com/in/laura-jaffe-965a411a/ ~ If a member helped you, please *Like* their post. If a member provided an answer to your question, please mark it as *Best Answer*.

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