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.
Since we are using an attribute in the substring function, we get the below 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.
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.
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.
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.
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”
We use 3 different kinds of cookies. You can choose which cookies you want to accept. We need basic cookies to make this site work, therefore these are the minimum you can select. Learn more about our cookies.