Create an import formula

  1. Open the Import Formulas screen.
  2. In the Import Formula field, enter a code of up to eight alphanumeric characters to identify the import formula.
    Note: Alphanumeric characters include the letters A through Z, the numerals 0 through 9, and all special characters except comma, dash, period, percent, tilde, and single quote.
  3. Press Tab.
  4. To the right of the import formula code, enter the name of the import formula.
  5. In the Import Type field, click to select the import type for the import formula.
  6. In the Column field, do one of the following:
    • Click to select the column for the result of the import formula.
    • Enter the code of the import formula conversion for the result of the import formula or click to select an import formula conversion from a Lookup List.
  7. In the Status field, click to select the status of the import formula.
    • Active - the import formula is active and displays in lookup lists.
    • Inactive - the import formula does not display in lookup lists. It cannot be selected, but it persists in previous selections.
  8. In the Memo field, enter any comments or notes about the import formula.
  9. Complete the following steps as needed to build the import formula:
    • In the Fields field, select fields as needed for the import formula and click Select to paste the field in the Calculation field.
    • In the Operators and Functions field, select operators and functions as needed for the import formula and click Select to paste the field in the Calculation field.

      Valid values include the following: +, -, *, /, %, =, >, <, >=, <=, <>, (, ), AND, OR, NOT, RETURN.

      Additional

      ABS ABS(<numeric-value)
      CONCATENATE String.Concat(<string1,<string2…)
      DAY DAY(<date-value)
      FIND INSTR(<start-position,<search-in,<search-for)
      IF/THEN/ENDIF IF(<condition,<result-if-true,<result-if-false)
      GETDATEVALUE Formulas.GetDateValue(<string)
      GETDECIMALVALUE Formulas.GetDecimalValue(<string)
      GETINTVALUE Formulas.GetIntValue(<string)
      LEFT LEFT(<string,<number-of-characters)
      LOWER LCASE(<string-value)
      MAX MAX(<value1,<value2)
      MIDDLE MID(<in-string,<start-position,<number-of-characters)
      MIN MIN(<value1,<value2 > )
      MONTH MONTH(<date-value)
      PROPER STRCONV(<value,VBStrConv.ProperCase)
      REPLACE Formulas.Replace(<in-string,<search-for,<replace-with,<MatchCase?)
      RIGHT RIGHT(<string,<number-of-characters)
      ROUND ROUND(<value,<number-of-decimals)
      SIGN SIGN(<numeric-value)
      TODAY TODAY()
      TRIM TRIM(<string-value)
      UPPER UCASE(<string-value)
      YEAR YEAR(<date-value)
    • Enter values and complete the import formula.
    Note: Note the following when building the import formula:
    • You can only have one active import formula for each Import Type/Column combination.
    • All import formulas must begin with the RETURN function. If you don't insert or enter this function, the applications adds it for you.
    • All values and references to the import template must be in double quotation marks (").
    • All values, references to the import template, and field names are case-sensitive.
    • References to the import template must begin with pData and the column name must be inside parentheses and double quotation marks. For example, pdata("pay class").
    • Field names must begin with pData followed by a period, then the node name and the actual field name. For example, pData.Employee.Time_Card_Default_Pay_Class. For best results, select the field name from the Fields field.
    • The application converts all data to strings. To get a numeric value, use the GETDATEVALUE and GETDECIMALVALUE functions.

    Review the examples in Import Formulas screen for more details on building import formulas.

  10. When you have built the import formula, click Check to validate it.
  11. If the import formula doesn't pass validation, repeat steps 9 and 10 as needed to address any issues.
  12. When the import formula passes validation, click Save Formula.