Option at Cutting list rules to use formula with text at the Expression column
At the 'Expression' column formula containing text can be used. This is useful, for example, where the text from two or more fields need to be combined into a third field or where text needs to be added to a field.
For example:-
Information box: Customer: Jones
Information box: Range: Tudor
Another Information box can be set up to contain a more detailed title using the data from the above boxes.
=STR("Order for:"+[Customer]+ " - "+[Range]+" range"
The result is:-
Order for Jones - Tudor Range
The rules are:-
Indicate a string (text) operation by starting with: =STR
Add strings
=STR("Text1"+"Text2")
For example:-
=STR("Parts for: "+[Customer] + " Week 3"
This results in the text: 'Parts for: Jones Week 3'
In this case '[Customer]' is an information box containing the customer for an order.
Add and alter strings
=STR([infobox1]+" "+[infobox2])
'infobox1' etc. stands for the name of the information box (as set in Information box parameters)
For example:-
=STR([Edge Left]+" : "+[Edge Right])
In this case the result is: 'BLUELAM 1MM : GREENLAM 1MM'
'Edge Left' is an information box containing the edging code. The result
is to combine the edging codes into a single phrase.
Add text from a field and an information box
=STR([Material code]+[infobox1])
'infobox1' etc. stands for the name of the information box (as set in Information box parameters)
For example:-
=STR([Material code]+ " " +[Product code])
In this case the result is: MDF18MM TUDOR DESK 32
Where 'Material code is the material code and 'Product code' is an information box containing the product code related to that part.
Cut and alter text
Use the LEFT, MID, RIGHT and INSTR options to cut and alter strings
=STR(LEFT([Material code],3)+ " - " + MID([Board code],5,3))
In this case the result is:-
MDF-203
That is we have extracted some data from the material code 'MDF-18mm' and the board code BRD/203/42.
Find text
Use the INSTR function to locate the position of text in longer string of text.
INSTR (target text, search text)
For example to locate "/" within the text "MEL/18/CHIP"
INSTR("MEL/18/CHIP","/")
The function, in this example, returns a value of '4'
This is useful for locating text in a string and then using MID to extract parts of the text.
Replace
Inside a string formulae, the function REPLACE can be used to replace character(s) with other character(s).
The format of the function REPLACE is as follows:
REPLACE (initial string, search string, replace string)
e.g. REPLACE (LAM-OAK,LAM,LAMINATE) would give the following LAMINATE-OAK.
If the search string is not found, then the text will be unchanged.
