Formulas in the mapping

A formula can include the following components:

• Digital and text literals: Literals are constants within the formula that you don't want to be modified:
  • Type digital literals as they are, such as 2, 8.25, or 13.84.
  • Enclose text literals within single quotation marks, for example, 'DEF_CLASS' and 'FOB'.
• Operators: The following types of operators are available:
  • Arithmetic operators take numerical values and return a numerical value.
  • Logical operators evaluate one or two Boolean expressions and return a Boolean result.
  • Comparison operators compare two expressions and return a Boolean value that represents the result.
• Functions: Functions, which perform specific tasks and return results, include the following types:
  • Text functions perform operations on text strings.
  • Math functions perform calculations.
  • Conversion functions convert data from one type to another.
  • Date/time functions perform functions related to the date, the time, or both.
• Fields: External or internal fields (elements) can be used in a formula as operands or function arguments.

To assign a formula to a field, do the following in the Manage import scenarios (SM206025) or Manage export scenarios (SM207025) window:

1. Click the pen icon in the field where you want to insert a formula.
   
   This invokes the Formula editor dialog box, which can be used to create the formula you want.
   
   
2. Click any of the types in the Component types area (on the upper left of the dialog box) to open the list of related components in the right area.
3. Double-click a component from the list of components. Note that all components are added to the right of the formula text.
4. Repeat Steps 2–3 to add all the needed components.
5. In the Formula text area (at the bottom of the dialog box), manually edit the text to construct a correct formula:
   
   Move the function argument or arguments within a function's parentheses, correctly arrange operands and operations, and add any needed brackets to ensure the proper order of operations.
6. Click Validate to check the syntax of the formula and make necessary corrections if required.
7. Click OK to save the formula.

Once it's inserted, a formula is preceded by an equal sign (=).

When you map the data in Visma Net to the external fields, you will usually need field-to-field mapping.

In some cases, however, you may need to add values from multiple internal fields to export the resulting value to an external one, to extract only a part of the internal field's value, or to perform another transformation or conversion for the data to be exported.

If you map an internal field to a formula instead of an external field, the resulting value will be assigned back to the Visma Net field.
This functionality can be used, for example, to set criteria on inquiry forms to export only filtered data or to mark exported records as exported.

When you map Visma Net fields to the external data, you will usually need field-to-field mapping.

For some fields, however, you may need to add values from multiple external fields to an internal one, to extract only a part of the external field's value, or to perform another transformation or conversion.

As you map an Visma Net field to the external data, the system checks the functionality of the mapped field and automatically adds, if necessary, a line that contains the system action (hidden by default) required for the field, such as a refresh of the window or a commit to the database.

Here are some examples illustrating the use of formulas:

• This formula is used for assigning a literal: ClassID ='Imported suppliers'.
• This formula assigns a concatenated string: SupplierID = 'X'+[SupplierID].
• This formula is used for assigning a value to a check box: IsAddressSameAsMain = true.
• If the internal field is required, it's important to check that the field value is not blank.
  
  To do this, use the Iif(str, truestatement, falsestatement) function, as in the following example: CountryID =iif(trim[Country]='','US',[Country]).
  
  Then the internal field will get the US value if the external field is blank.
• If the customer numbers from the source cannot be used in your implementation, create new IDs for them.
  
  For example: In the demo data, customer and supplier IDs are short versions (up to 15 characters) of their names converted to uppercase.
  
  To do this, remove any spaces from the name by using the trim(arg) function, convert it to uppercase with the UCase(arg) function, and remove any characters in excess of 10 by using the Left(arg) function: Customer ID =Left( trim(UCase([Name])),10).