QUESTION

How is the Static Value / Formula column used on the mapping screen of Rightworks Transaction Pro Importer?

RESOLUTION

The Static Value / Formula column within Importer can save time from manually manipulating the import file prior to importing into QuickBooks Desktop (QBDT).

Below are some examples of how this column can be used to help further streamline import process.  

Static Value Examples

Often times, the QB field value is the same for all lines in the import file, but the file does not contain this information. If this is the case, enter the value to be imported for this field under the Static Value / Formula column, and the value will import for all lines of the import file.

Some examples of QB fields where a Static Value can be useful are:

• Bank Account Names or Numbers on Checks and Sales Receipt imports
• Memo field on any transactions 
• Journal Entry RefNumber field, if the entire import file is one Journal Entry
• Item Type for Item List imports, if all of the Items are the same type (ie. Service)

Formula Examples

Importer allows the following simple formulas to be used on the mapping screen:

1. Simple formulas (one operator only): Addition, Subtraction, Multiplication, and Division
2. Combining of fields (also known as the concatenate feature in Excel)

NOTE: When entering formulas in Importer under the Static Value / Formula column, the Column Headers from the import file will be used in the formula, enclosed in square brackets.

Simple Formula: Invoice import where the import file has a column for the Total Line Amount and a column for the Quantity but does not have a column with the per item Price. For the PRICE field under Static Value / Formula, enter =[Total Line Amount]/[Quantity] and Importer will import the correct value for the PRICE field in QBDT. 

Combining Fields Formula: Customer import where the import file has the Customer's First Name and Last Name in separate columns, but the name needs to be in the same column to import as the Display Name in QBDT.

If the Customer needs to be formatted with the First Name first with a space in between the two names, the formula to be used would be =[First Name]& " " &[Last Name]

If the Customer needs to be formatted with Last Name first with a comma in between the two names, the formula to be used would be =[Last Name]& “, ” &[First Name]

Essentially, what needs to be provided is the Column Headers from the import file that are to be included in the combined field enclosed by square brackets. Any extra characters that are to included should be entered enclosed by quotation marks. In the first example, it was a space enclosed in the quotation marks; and in the second example, it was a comma and a space that was enclosed in the quotation marks.

For more examples about how to use the Static Value/Formula feature, refer to this Knowledge Base ARTICLE.