In supply and distribution application I am developing, I store product catalogue numbers and then allow users to search and to re-use products already entered on the database. A problem was identified with longer catalogue numbers that users tend to enter the same number in different ways by inserting spaces, dashes, dots, etc. Moreover, a supplier company sometimes changes presentation of their catalogue numbers in a new catalogue.
A way around this problem would be to have a function which compresses a text string by removing certain characters from it, i.e. converting 12.34 56-7 to 1234567 and to store the catalogue number as it was entered by a user in one attribute and in a truncated form in another atribute. Then a search can be performed though the second attribute.
Any thoughts?
greg
Controlled truncation of a data string
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Controlled truncation of a data string
You can use the "REPLACE" function to replace the punctuation characters that you specify with an empty string. To be able to use this solution for your problem you will need to do the following:
a) Get the product to store the catalog number in two formats - as user enters it and as it should be searched. So Product will need to have the additional calculated attribute to calculate and store the "good" number (the new "replace" function will do the calculation).
b) The query that searches for products should take user input, use the "REPLACE" function to calculate the "good" number and then use it to compare with the "good" attribute in the product. In order to do this you will need to collect query input in a temporary object and then run query using the attribute of this object
Example of REPLACE function usage:
REPLACE (Product.Catalogue, '[,-.]', '')
Second parameter is a regular expression. Any characters specified inside the square brackets will be replaced.
Aware IM Support Team
a) Get the product to store the catalog number in two formats - as user enters it and as it should be searched. So Product will need to have the additional calculated attribute to calculate and store the "good" number (the new "replace" function will do the calculation).
b) The query that searches for products should take user input, use the "REPLACE" function to calculate the "good" number and then use it to compare with the "good" attribute in the product. In order to do this you will need to collect query input in a temporary object and then run query using the attribute of this object
Example of REPLACE function usage:
REPLACE (Product.Catalogue, '[,-.]', '')
Second parameter is a regular expression. Any characters specified inside the square brackets will be replaced.
Aware IM Support Team
Re: Controlled truncation of a data string
Can I remove an empty space with the REPLACE function? greg
aware_support wrote:You can use the "REPLACE" function to replace the punctuation characters that you specify with an empty string. To be able to use this solution for your problem you will need to do the following:
a) Get the product to store the catalog number in two formats - as user enters it and as it should be searched. So Product will need to have the additional calculated attribute to calculate and store the "good" number (the new "replace" function will do the calculation).
b) The query that searches for products should take user input, use the "REPLACE" function to calculate the "good" number and then use it to compare with the "good" attribute in the product. In order to do this you will need to collect query input in a temporary object and then run query using the attribute of this object
Example of REPLACE function usage:
REPLACE (Product.Catalogue, '[,-.]', '')
Second parameter is a regular expression. Any characters specified inside the square brackets will be replaced.
Aware IM Support Team
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
In my configuration, I want to produce a shorten version of a product catalogue number. I have an attribute CatNo, I introduced another calculated attribute CatNoShort and a rule like this one
Product.CatNoShort=Product.CatNo
REPLACE (Product.CatNoShort, '[ ,.]', '')
The system did not allow the second line of this rule. The message was that an identifier was expected in column 9, i.e. where the first bracket is. It seems that the REPLACE function is treated as an action to replace an instance of a business object, not a text string. Can you help?
Product.CatNoShort=Product.CatNo
REPLACE (Product.CatNoShort, '[ ,.]', '')
The system did not allow the second line of this rule. The message was that an identifier was expected in column 9, i.e. where the first bracket is. It seems that the REPLACE function is treated as an action to replace an instance of a business object, not a text string. Can you help?
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
-
- Posts: 7525
- Joined: Sun Apr 24, 2005 12:36 am
- Contact:
Yes, this function will replace all characters specified inside square brackets. See also partial description of regular expressions below.
Summary of regular-expression constructs
Construct Matches
Characters
x The character x
\\ The backslash character
\0n The character with octal value 0n (0 <= n <= 7)
\0nn The character with octal value 0nn (0 <= n <= 7)
\0mnn The character with octal value 0mnn (0 <= m <= 3, 0 <= n <= 7)
\xhh The character with hexadecimal value 0xhh
\uhhhh The character with hexadecimal value 0xhhhh
\t The tab character ('\u0009')
\n The newline (line feed) character ('\u000A')
\r The carriage-return character ('\u000D')
\f The form-feed character ('\u000C')
\a The alert (bell) character ('\u0007')
\e The escape character ('\u001B')
\cx The control character corresponding to x
Character classes
[abc] a, b, or c (simple class)
[^abc] Any character except a, b, or c (negation)
[a-zA-Z] a through z or A through Z, inclusive (range)
[a-d[m-p]] a through d, or m through p: [a-dm-p] (union)
[a-z&&[def]] d, e, or f (intersection)
[a-z&&[^bc]] a through z, except for b and c: [ad-z] (subtraction)
[a-z&&[^m-p]] a through z, and not m through p: [a-lq-z](subtraction)
Predefined character classes
. Any character (may or may not match line terminators)
\d A digit: [0-9]
\D A non-digit: [^0-9]
\s A whitespace character: [ \t\n\x0B\f\r]
\S A non-whitespace character: [^\s]
\w A word character: [a-zA-Z_0-9]
\W A non-word character: [^\
Summary of regular-expression constructs
Construct Matches
Characters
x The character x
\\ The backslash character
\0n The character with octal value 0n (0 <= n <= 7)
\0nn The character with octal value 0nn (0 <= n <= 7)
\0mnn The character with octal value 0mnn (0 <= m <= 3, 0 <= n <= 7)
\xhh The character with hexadecimal value 0xhh
\uhhhh The character with hexadecimal value 0xhhhh
\t The tab character ('\u0009')
\n The newline (line feed) character ('\u000A')
\r The carriage-return character ('\u000D')
\f The form-feed character ('\u000C')
\a The alert (bell) character ('\u0007')
\e The escape character ('\u001B')
\cx The control character corresponding to x
Character classes
[abc] a, b, or c (simple class)
[^abc] Any character except a, b, or c (negation)
[a-zA-Z] a through z or A through Z, inclusive (range)
[a-d[m-p]] a through d, or m through p: [a-dm-p] (union)
[a-z&&[def]] d, e, or f (intersection)
[a-z&&[^bc]] a through z, except for b and c: [ad-z] (subtraction)
[a-z&&[^m-p]] a through z, and not m through p: [a-lq-z](subtraction)
Predefined character classes
. Any character (may or may not match line terminators)
\d A digit: [0-9]
\D A non-digit: [^0-9]
\s A whitespace character: [ \t\n\x0B\f\r]
\S A non-whitespace character: [^\s]
\w A word character: [a-zA-Z_0-9]
\W A non-word character: [^\
Aware IM Support Team
Newline insert
I found this (old) post in relation to my requirement:
BO Address has attributes Name, Street, City and FullAddr. A BO rule should take care to fulfill the FullAddr attribute if empty, ie.
If Address.FullAddr IS UNDEFINED Then
Address.FullAddr=Address.Name+\n+Address.Street+\n+Address.City
After trying with ' ', '<<>>', '/\n/' and...., I just can't get a newline code to be inserted between each attributes .
How should it be done ?
Thanks
BO Address has attributes Name, Street, City and FullAddr. A BO rule should take care to fulfill the FullAddr attribute if empty, ie.
If Address.FullAddr IS UNDEFINED Then
Address.FullAddr=Address.Name+\n+Address.Street+\n+Address.City
After trying with ' ', '<<>>', '/\n/' and...., I just can't get a newline code to be inserted between each attributes .
How should it be done ?
Thanks