Skip to main content
Solved

Substring Function for an Attribute


Forum|alt.badge.img

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?

Best answer by lauraj46

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

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

8 replies

lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 484 replies
  • June 15, 2023

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


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 63 replies
  • June 15, 2023

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?


lauraj46
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 484 replies
  • Answer
  • June 15, 2023

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


Forum|alt.badge.img
  • Author
  • Jr Varsity III
  • 63 replies
  • June 15, 2023

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


  • 44 replies
  • September 8, 2023

@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
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 484 replies
  • September 8, 2023

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


  • 44 replies
  • September 8, 2023

@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
Captain II
Forum|alt.badge.img+8
  • Captain II
  • 484 replies
  • September 8, 2023

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


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