Automatic trees can be generated using system or custom fields, but you can also apply formulas to those fields.
These formulas are made of fields and simple functions that allow you, for instance, to extract parts of character strings, calculate dates or read external files. You can interweave several functions in a single formula.
![]() |
ATTENTION: A formula must contain at least one field from the fields list. Functions cannot be used alone. |
To insert a field in the Formula pane, drag this field from the Fields list exactly where you want to place it in the formula. The field will appear between brackets and its name will be prefixed by a number (i.e. [10044-DirectX]). This name corresponds to Synexsys' internal identifier. Although this action is possible, avoid manually modifying the contents of a field inserted in a formula.
If you use functions, do not forget to delete example arguments such as str, num, key, filename, etc. that are only present to remind you the type of data you need to enter in the formula.
Date | Returns the current date as a string |
IF(DaysBetween([MyDate],date)>30,'Obsolete','Active') will classify Objects in the "Obsolete" node when the number of days between MyDate and the current date is larger than 30 and in the "Active" node in the opposite case. |
ATTENTION: Date cannot be used everywhere. More specifically, Date cannot be used as a direct comparison argument. For instance, you may not write: IF([MyDate]<date,...), since this formula will not return correct values. It is generally preferable to use functions such as DaysBetween() and dateSys(), when you need to compare dates. |
||
DateSys(<str>) | Returns the date as a string using format YYYYMMDD. <str> must be a valid date |
DateSys([MyDate]) will classify Objects in the nodes that represent the absolute value contained in MyDate. For instance, if the value of MyDate is 06/17/2004 for the object abc, abc will be classified under the node 20040617. DateSys() is practical for comparing dates and sorting the nodes in chronological order based on the dates. |
For example, if "Last contact" node is worth <DateSys([MyDate])>[MyDate], chronological sort order will be based on the date in [MyDate]. If sort order is not forced, dates are treated as strings and are, therefore, sorted in alphabetical order. |
||
DaysBetween(str,str) | Returns a string indicating the number of days between the two dates. The two <str> must be valid dates. |
IF(DaysBetween([MyDate],date)>30,'Obsolete','Active') will classify Objects in the "Obsolete" node when the number of days between MyDate and the current date is larger than 30, and in the "Active" node in the opposite case. |
DayN(str) | Returns the day of the month as a number. <str> must be a valid date |
DayN([MyDate]) will classify Objects under the nodes representing the day of the month of MyDate. For instance, if the value of MyDate is 06/17/2004 in the object abc, it will be classified under node 17. |
DayS(str) | Returns the name of the day of the week. <str> must be a valid date |
DayS([MyDate]) will classify Objects under the nodes representing the day of the week contained in MyDate. For instance, if the value of MyDate is 06/17/2004 in the abc object, it will be classified under the "Thursday" node. |
DayW(str) | Returns the number of the day of the week. <str> must be a valid date |
DayW([MyDate]) will classify Objects under the nodes representing the number of the day of the week contained in MyDate. For example, if the value of MyDate is 06/17/2004 in the abc object, it will be classified under node 4. |
Functions such as DayW() are especially practical in order to force a logical sort order when alphanumerical data is displayed. If, for instance, you need to display the days of the week in a tree, the following order will be applied: Friday, Monday, Thursday, Tuesday, Saturday, Sunday, Wednesday (alphabetical sorting) With formula <DayW([MyDate])>DayS([MyDate]) you will obtain the list of days in a logical order. (See below for more information on sort order). |
||
DecalDate(str, unit, x) | Returns the date <str> shifted of <x> <unit> as a string. <str> must be a valid date |
DecalDate([MyDate],'D',365) adds 365 days to MyDate. (D is the symbol for a shift in days). DecalDate([MyDate],'W',4) adds 4 weeks to MyDate. (W is the symbol for a shift in weeks). DecalDate([MyDate],'M',6) adds 6 months to MyDate. (M is the symbol for a shift in months). DecalDate([MyDate],'Y', 1) adds 1 year to MyDate. (Y is the symbol for a shift in years) |
By combining Year(), MonthS() and DecalDate(), you can easily create a tree to manage warranties automatically. For example, by adding 3 years to a purchase date. You can simply apply this example if you have created a "Purchase date" custom field and you substitute it with ([MyPurchaseDate]). First level of the "End of warranties" tree: Year(DecalDate([MyPurchaseDate],'Y',3)) returns the year. Second level of the "End of warranties" tree: MonthS(DecalDate([MyPurchaseDate],'Y',3)) returns the month. (See below for more information on sort order). |
||
Decode(filename, Section, key) | Returns the value of <key> in the Section <Section> of the file <filename> |
Decode('D:\regions.txt', 'zones', '[MySite]) will classify under "Florida" a PC that contains "Orlando" in its field MySite. Decode() looks for Section "zones" in the file "D:\regions.txt". Then it looks for the value "Orlando" and associates "Florida" to it as in the following sample: [zones] If an ELSE key is defined, any value not found on the list will be associated to the ELSE= value. If the ELSE key is not defined, keys without a match will keep their initial value. |
Decode() is extremely practical to display the logical values that all users can understand instead of the codes that identify them. For instance, if your IP addresses carry a byte that indicates the PCs' locations, you can isolate that value and associate it to the real name of the site in an external file. You will then obtain a tree that will allow you to automatically locate your equipment by site name based on a value contained in the IP address. For instance: Decode('.\SXSiMatchList.txt','IP MATCH',SUBSTR([12345-Main IP Address],POS('.',[12345-Main IP Address],2)+1,2)) |
||
Div(x,y) | Returns the integer resulting from dividing <x> by <y> |
Div(10,3)= 3 |
False | Returns boolean value "False" |
|
FileRec(filename, key) | Returns the first full line of the file <filename> where the <key> value was found. <key> can be placed anywhere on the line. |
FileRec('D:\services.txt', [MyService]) will classify under "Foreign Sales - 20050-10" an Object whose MyService field contains value "20050". FileRec() returns the first line of file "D:\services.txt", which contains a correspondence to the value of MyService. Thus, in the example below, Objects whose MyService field has a "sales" value will not be classified under "Sales service - 28030-20", but rather under "Foreign sales service - 20050-10", since this line also contains the "sales" string placed first. "Purchases - 10000-00" |
FormatDate(str,fmt,sep) | Returns the <str> date in the local format |
FormatDate('02-23-2003','mm-dd-yyyy','-') returns 23/02/2003 if the system date format is dd/mm/yyyy. See Formats for more details |
Frac(num) | Returns the decimal part of <num> |
Frac([MyNumber]) returns 0.25 if MyNumber is 19.25 |
If(expr, r1,r2) | Returns <r1> if <expr> is true and <r2> if it is false |
If(val([MyStock])<1,'Stock outage', If(val([MyStock])<10,'To order','In stock')) Classifies Objects whose MyStock field is set to 0 or 1 under the "Stock outage" node, those whose MyStock field has a <10 value under the "To order" node, and the rest under the "In stock"node. REMARK: always use Val() in numerical comparisons |
IF() is a very powerful function. Many If() functions can be combined to create sophisticated function groups. If, on the contrary, the number of unique values returned by the formula is too high or random, you may combine many IF() functions with the Decode() function. | ||
Int(num) | Returns the integer part of <num> as a number |
Int([MyNumber]) returns 19 if MyNumber is 19.25 |
Len(str) | Returns the length of the <str> string as a number |
Len([MyText]) returns 9 if MyText is "Hello world!" |
Lower(str) | Returns <str> in lowercase |
Lower([MyText]) returns "abcd" if MyText is "AbcD" |
LTrim(str) | Returns <str> without leading spaces |
LTrim([MyText]) returns "abcd" if MyText is " abcd" (initial space) |
MonthN(str) | Returns the number of the month of the date <str> |
MonthN([MyDate]) returns 7 if MyDate is "07/25/2003" |
MonthS(str) | Returns the month of the date <str>. |
MonthS([MyDate]) returns "July" if MyDate is "07/25/2003" Use the sort functions to force months being displayed in a logical order |
Pretty(str) | Returns <str> with the first character in upper case and the rest in lower case |
Pretty([MyText]) returns "Hello world !" if MyText is "hello world!" |
Pos(search,str,n) | Searches for the <n>th occurrence of the string <search> in the string <str>. |
Pos() is often used as an argument in another function. It is normally used to replace the argument <n> in the function Substr(str,n,l) |
A practical example of how Pos() is used in value extractions of IP addresses: Decode('.\SXSiMatchList.txt','IP MATCH',SUBSTR([10397-Main IP Address],POS('.',[10397-Main IP Address],2)+1,2)) |
||
RTrim(str) | Returns <str> without trailing spaces |
LTrim([MyText]) returns "abcd" if MyText is "abcd " (final space) |
Sqrt(num) | Returns the square root of <num> as a number |
Sqrt([Mynumber]) returns 5 if MyNumber is "25" |
Str(num) | Converts <num> to a string |
|
Substr(str,n,l) | Returns a sub-string of <str> from the character in position <n> on a length defined by <l> |
Substr([MyText],3,2) returns "cd" if MyText is "abcd" |
Time | Returns current time as a string |
|
Trim(str) | Returns <str> without leading and trailing spaces |
Trim([MyText]) returns "abcd" if MyText is " abcd " (initial and final space) |
TypeOf(expr) | Returns the type of expression <expr> |
TypeOf([MyText]) returns "STRING" if MyText is a string of characters |
Upper(str) | Returns <str> in upper case |
Upper([MyText]) returns "HELLO" if MyText is "hELlo" |
Val(str) | Converts <str> to a number |
Val([MyText])+0.25 returns number 18.25 if MyText is "18" |
Year(str) | Returns the year of <str> |
Year([MyDate]) will classify Objects in nodes representing the year of MyDate. For instance, if the value of MyDate is 06/17/2004 in the abc object, it will be classified under node 2004. |
<sort criteria>field/formula | Allows forcing the vertical sort order of the nodes of each level |
<MonthN([MyDate])>MonthS([MyDate]) will display the month names (strings) but will force their order according their number (January = 1, February = 2, etc.). If not, months are considered character strings and will appear in alphabetical order (default) |
Example 1 - Sort chronologically the nodes containing a date: <DateSys([MyDate])>[MyDate] |
||
Example 2 - Sort chronologically the nodes containing the days of the week: |