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.
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 |