Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hi, I don't think this is possible but i thought i'd ask the question.
I have thousands of stock REASON DESCRIPTIONS. an example of some descriptions are:
STOCK CHECK
P/O
ORDERING
etc
but the problem is that the description would have a date and time after it, e.g.:
STOCK CHECK 10/11/10 15:30:01
This results in thousands of description starting with STOCK CHECKING. I am trying to create a table with just an abreviation of the REASON DESCRIPTION which will then display anything relating to it.
e.g,
New Table:
Stock Check
Price Over
Ordering Adjustment
When 'Stock Check' is selected, All results relating to the REASONDESCRIPTIONS 'STOCK CHECK ....' will be displayed, instead of having to highlight ALL STOCK CHECK descriptions.
The easiest way i thought to do this would be to load in a CSV.
Question 1: Is this the easiest way?
Question 2: Can i use something like a WILDCARD function in the CSV?
What i was thinking is i could list all of my abreviations in a CSV with a wildcard next to it:
e.g.
Does this make sense? is it possible?
Thanks
The 'Mappings' table reflects the strings you want to match with the wildcard '*'. For example, 'Stock*' is used to map anything beginning with 'Stock' to the 'MapTo' field value of 'Stock*'. This table you could maintain as an xls/csv etc. The filed 'Dummy' doesnt need to be in the external source but is used just to be able to use a 'Group By' in the temporary table 'tMap String'.
The 'LET vWildQuoted = ...' fetches the first (and only) value (into variable vWildQuoted) of the field 'WildQuoted' from table 'tMap String' and so contains all the values of 'MapTo' in a string with each one between single quotes and seperated by a ',' (ie 'Stock*','Order*','Price override')
Tables 'tData' and 'Data' represents your table 'StoreStockMovement' where the field 'InData' represents your field 'narrative'.
The 'wildmatch(...' looks for a wildcard match of 'InData' with the string in vWildQuoted and if so returns a number as to its matching position in the string as the field 'MapMatch'. 'MapMatch' will be 0 if there was no match and so the preceding load uses 'if (MapMatch = 0...)' to use the full text else returns the wildcard string it matched on as the 'key field' back to table 'Mappings'.
Hope this helps!
Gordon