YouTube videos and Knowledge Base articles exist regarding the new Admin Portal and Billing Portal for Transaction Pro Online.

Search "Admin Tool" to locate them or email support@transactionpro.com with any questions.

Practical Uses for Static Values or Formulas in Transaction Pro Importer

QUESTION

What are some ways that the Static Value/Formula column can be used in transaction Pro Importer?

RESOLUTION

Transaction Pro Importer for QuickBooks Desktop (QBDT) has the ability to map either a Static Value or a Formula on the mapping window to use with the import file.  For more information about how to use the Static Value/Formula column, refer to this Knowledge Base ARTICLE. This article focus on ideas as to how to use this feature. Below is a screenshot of the mapping screen with the Static Value/Formula column highlighted in yellow.

Static (Default) Value

  • Set a Static Transaction Date:  If importing Invoices and the import file does not contain the Invoice Date, a Default Date can be entered to be applied to all Invoices imported.
  • Set a Static Billable Flag:  If importing Bills and all the Expenses are Billable and the import file does not contain a Billable column, a Static Value of Y can be used for the import.
  • Set a Static RefNumber:  On a Journal Entry, if the entire file is one Journal Entry, a Static Value of 1 can be entered for the RefNumber field, letting Importer know to import all detail lines in the import file onto a single Journal Entry.
  • Set a Static Expenses Account:  If importing a list of Checks that will allow post to the same Expense Account, the General Ledger Account can be entered for the Expense Account field as a Static Value.
  • Set a Static Customer Name:  When importing Point of Sales data or Web Sales when the Customer detail does not need to be kept in QBDT, enter a general Customer name (ie. Amazon, PayPal, POS) as a Static Value for the Customer field. 
  • Set a Static Bank Account or Credit Card Account:  Bank and credit card financial institutions typically do not store the Account Name from QBDT in their database. The General Ledger Account Name can be entered as a Static Value when importing Bank Statements or Credit Card Statements.

Formulas

  • Combine Two Columns into One Name Field:  Often times, import files will have two separate columns with the first name and last name; or perhaps the Customer is in one column and the Job is in another column. By using Importer's concatenate formula, these columns can be combined correctly when importing.
  • Combine Multiple Fields into a Description or a Memo Field:  Perhaps, the imported Invoice is for a service to a Customer and the technician name, date, and what the service was provided need to be shown in the description field on the invoice. Importer's concatenate formula can allow these columns to be combined into one field during import.
  • Separate Price from the Total Item Price:  If the Price column in the import file contains a Total Price that includes Sales Tax, to separate the Price without Sales Tax for the Item Price field, the formula would be the Price column divided by 1.05 (when the Sales Tax rate is 5%). 
  • Combine Two Columns into One:  If the import file contains both labor and materials that need to be combined for the Item Price, a Formula can be added on the mapping screen of Importer to add the values in the two columns together during import.
  • Adding Sub-Accounts to Existing Main Account or a Jobs to Existing Customer: If the import file contains the Sub-Account and not the Main Account, then a formula can be entered that includes both a Static Value (Main Account:) followed by the Sub-Account column in the import file. For adding Jobs to an existing Customer, the Static Value would be the Customer followed by a colon and then the Job column in the import file.
  • 322
  • 08-Jul-2019
  • 682 Views