Formulas tell the Import program exactly what to import into Lucity. A formula could be as simple as "This source data goes in to that Lucity field." Or, it could outline more complex operations like formatting, data conversion, etc.
Formulas can be used in Data Source Queries, Raw Web Requests, Mappings, and Pre & Post processing.
Note that if the first character is a plus sign (+), the system expects a function, and strings containing =A and a number are interpreted as column mappings.
When a formula contains a plus sign (+) as the first character, the Import and Update tool generates a SQL statement and passes it to the underlying Lucity database platform to resolve the formula. First, the system replaces the mapped values in the formula with the data from the import file, removing the +; then, it issues a SELECT statement with the resulting value.
For example, if a user specifies a formula of +CAST('=A1' AS VARCHAR), and the mapped data for A1 is Test, the system issues the following SQL statement: SELECT (CAST('TEST' AS VARCHAR)) AS TEXTVAL
Description |
Formula |
Resolves To |
Final Value |
Map Source Data to Lucity |
=A2 |
5 |
5 |
Insert a value into a Lucity Field |
"2" or "1/1/2015" or "Bill" |
2 or 1/1/2015 or Bill |
2 or 1/1/2015 or Bill |
Add values together |
+=A1 + =A2 |
10.97 + 5 |
15.97 |
Concatenate values |
+'=A2 - =A3' |
'5 - Concrete' |
5 - Concrete |
Extracting a value from the left of a string |
+LEFT('=A4', 3) |
LEFT('PLK000078FD', 3) |
PLK |
Extracting multiple values from a string and reformatting them together (dates) |
+SUBSTRING('=A5', 5, 2) + '/' + SUBSTRING('=A5', 7, 2) + '/' + LEFT('=A5', 4) |
SUBSTRING('20071023115523', 5, 2) + '/' + SUBSTRING('20071023115523', 7, 2) + '/' + LEFT('20071023115523', 4) |
10/23/2007 |
Extracting part of a string and converting to a different format |
+CAST(SUBSTRING('=A4', 4, 6) AS INT) |
CAST(SUBSTRING(' PLK000078FD', 4, 6) AS INT) |
78 |
Setting a value based on another value |
+CASE WHEN '=A3' = 'Concrete' THEN '1' ELSE '2' END |
CASE WHEN 'Concrete' = 'Concrete' THEN '1' ELSE '2' END |
1 |
Insert the Current Date |
+GETDATE() |
GETDATE() |
12/7/2015 10:48:50 AM |
Set value based on the results of a lookup
|
+SELECT CT_ID FROM WKCAT WHERE CT_NAME = '=A6' |
SELECT CT_ID FROM WKCAT WHERE CT_NAME = 'Sewer Department' |
89 |
|
+SELECT DESCRIPTION FROM WKFIELDSDESC WHERE ID = 841 AND CODE = =A2 |
SELECT DESCRIPTION FROM WKFIELDSDESC WHERE ID = 841 AND CODE = 5 |
Vacation Time |
|
+SELECT SUM(NT_LENGTH) FROM SWNET WHERE NT_MAT_TY = '=A3' |
SELECT SUM(NT_LENGTH) FROM SWNET WHERE NT_MAT_TY = 'Concrete' |
1023 |
Property Names
Formulas written for the Post Processing tab can include references to module properties. Properties are the programs way of referencing fields. Some properties reference multiple fields. The program will pull the value associated with that property name from the last saved record.
Note: For these to work in post processing formulas to work you must have the Every Record box checked for the post process.
Property names must be entered into a formula using the following format: %%PropertyName%%
Reserved Words
Reserved words are used to add data to a formula based on when, and by whom, the import is run. The import and updates supports using the standard Lucity Reserved Words as well as some built in reserved words for the document linking fields
Reserved words must be entered into a formula using the following format: %RESERVEDWORD%
More information on standard Reserved Words
More information about document linking Reserved Words
Syntax for Pre and Post Processing
Formulas added to the Pre and Post Processing tabs must be enclosed in [[ ]]
Lookup
You can right click into any field that allows you to enter Formulas and you will get an option to Insert Reserved Word. This gives you the option to select sql reserved workds, import and update reserved workds, and some formula notations.
|
In This Section |
How to find a Property Name