Transaction Pro Importer 5.0 for QuickBooks and Transaction Pro Importer 3.0 for QuickBooks Online both have a very powerful feature to map either a static value or a formula. We went into detail on how to use this feature in another blog post. The purpose of this article is to get you thinking about how you can use this very powerful feature on your own imports to QuickBooks. Below is a screen shot of the mapping screen with the Static Value/Formula column highlighted in yellow.
Static (Default) Value
- Set a Static Transaction Date: For instance if you are doing your invoicing for the current month and your import file does not contain the invoice date
- Set a Static Billable Flag: If you are importing bills and all the expenses are billable but your import file does not contain a billable column you could set a static value of Y in the mapping.
- Set a Static RefNumber: On a journal entry if the entire file is one journal entry you could type a static value of 1 next to the QuickBooks field, RefNumber, and then Transaction Pro Importer will know to group all lines in that file onto one journal entry.
- Set a Static Expenses Account: If you are importing a list of checks that will all use the same Expenses Account you can just type the expense account as a static value in the mapping.
- Set a Static Customer Name: For web orders if you do not have a lot of repeat customers provide a static customer name for the shopping cart (eg Amazon, PayPal etc.). You will still map the bill to/ship to so when you open up the actual order in QuickBooks you will see the details of whom this order was sold to.
- Set a Static Bank Account or Credit Card Account: You bank and credit card institution typically do not store the account name from QuickBooks in their database so you can just type this in as a static value in the mapping.
- Combine two columns into one name field: Often times web stores will have two separate columns with the first name and last name. Alternatively perhaps the customer is in one column and the job is in another column. You can easily do a formula to combine the two fields into one with a colon in between the two.
- Combine multiple fields into a description or a memo field: Perhaps you are providing a service to customers and you want to include the technician name, date and what the service was that was provided in the description field on the invoice.
- Separate out sales tax from the total item price: If the price in your import file includes sales tax and you want to break this out you could do two formulas. One for the item price (eg column * .95) and another for sales tax (eg column *.05).
- Combine two columns into one: Perhaps your import file contains both labor and materials and you want to combine the total of these two columns into the total item price.
- Adding sub-accounts or jobs to existing main accounts or customers: Lets say you are adding multiple locations (jobs) to an existing customer in QuickBooks. You can do a formula that includes both a static value (the customer name) plus the job name separated by a colon.
Free trials are available for both our QuickBooks and QuickBooks Online importers. Please share how you are using the static value or formula features in the Transaction Pro Importer mapping screen in the comments.