MoneyWorks Manual
Transaction Importing1
Clicking the Options button on the Import Settings window will display options that affect how the information is imported.
Signs: MoneyWorks can adjust the signs of values imported. By default MoneyWorks assumes that all import data will have positive values. If you are importing (say) Cash Receipts, and the import data is negative, then you must click the Negative radio button in the Receipts/Debtor Invoices box. The signs of values for these transaction types will be corrected as the transactions are imported.
Check Invoice Nºs: Set this if you are importing Debtor Invoices and you want to ensure that imported invoice numbers do not duplicate existing ones.
Create Names: Set this if you want MoneyWorks to automatically create a new Debtor or Creditor record if the Debtor Invoice or Creditor Invoice being imported contains a Debtor or Creditor code that MoneyWorks does not know. When this box is checked, information can be imported into the fields designated by “Name.” in the Destination Field list.
Create Jobs: Set this if you want MoneyWorks to automatically create a new Job record if the transaction being imported contains a job code that MoneyWorks does not know. When set, MoneyWorks will create a new (descriptionless) job record for unknown job codes, otherwise an error will be reported for unknown jobs.
Rounding Tolerance: MoneyWorks expects that the sum of the Detail.Gross values will equal the transaction Gross value. If you are importing the transaction gross value and have MoneyWorks work out the Detail.Gross values for you, you may find that the application in which the transactions were created rounded the GST differently, resulting in an inequality between the sum of the individual gross values and the overall gross value.
MoneyWorks is lenient concerning the foibles of the source application. Provided that the difference between the imported transaction gross value, and the total gross as calculated by MoneyWorks is less than or equal to the Rounding Tolerance specified ($0.02 by default), MoneyWorks will adjust the individual GST values in the detail lines as it imports the transaction so that the detail line gross values sum up to the transaction gross value that is imported.
Each line of text holds one whole transaction: Set this if each record in the import file holds the information for a complete transaction. This is the format to use if you are importing information from another MoneyWorks document. This format is also suitable for FileMaker Pro repeating fields
Transactions are split over multiple lines of text Set this if one record in your source file corresponds to a transaction detail line in MoneyWorks. For MoneyWorks to know when a new transaction starts there must be a Key field, which holds the same value for each record within a transaction. Choose this field from the Key Field pop-up menu—the field you choose will be displayed in red on the import map. Adjacent records from the imported file with the same key field value will be amalgamated into a single transaction.
Note: MoneyWorks will not import any transactions that are dated after the end of the current period.
Importing embedded newlines in fields that allow them
When importing from a tab-delimited (or CSV) text file, an ASCII CR or LF is used as a record delimiter, so cannot be included in the data to be imported. If you want to import a newline, you need to use ASCII value 11 (Hex 0B, LINE TABULATION). MoneyWorks will map this to a newline character (stored internally as a CR, CARRIAGE RETURN).
Working it Out
MoneyWorks has heuristics for determining sensible values for fields that you do not import values into. To use the heuristic, double click on the field value in the right hand column and click the Work it Out For Me radio button. The following table describes the fields for which the Work It Out For Me option is available and how they are evaluated by MoneyWorks.
| Field Name | Heuristic |
|---|---|
| OurRef | If the transaction type is DI, then use the next sequential invoice number. |
| TransDate | Use today's date. |
| DueDate | Use the Names' payment terms to work out the due date. |
| Type | If there is a debtor/creditor code present in the NameCode field, then the transaction must be an invoice. If the account in the first detail line is an income account then it is a Debtor Invoice (DI), otherwise it is a Creditor Invoice (CI).If there is no debtor/creditor code then if the first detail line is an income account then it is a Cash Receipt (CR), otherwise it is a Cash Payment (CP). |
| Gross | Use the sum of the gross values in the detail lines. |
| Contra | For invoices, use the Accounts Receivable or Accounts payable account for the Debtor or Creditor.For cash transactions we can't decide which bank to use so if importing cash transactions you should not use the Work It Out option for Contra. (MoneyWorks will insert = "no bank into the Contra field which will generate an error.) |
| ToFrom | For invoices, use the company name of the debtor or creditor. |
| Hold | For debtor invoices, set to Yes if the Auto Credit Hold option is on in the preferences and the debtor has overdue invoices or is over the credit limit specified in the Debtor record. |
| Detail.Account | Use the relevant control account for the product in Detail.StockCode. Do not use Work it Out for this field if you are not importing product codes. |
| Detail.TaxCode | Use the tax code for the account in Detail.Account |
| Detail.Net | If there is a stock code in Detail.StockCode, use Detail.UnitPrice * Detail.StockQtyOtherwise use Detail.Gross - Detail.GST |
| Detail.Gross | Use Detail.Net + Detail.GST |
| Detail.GST | If we know the net and the tax code, use Net * Tax Rate.If we know the gross and the tax code, use Gross - (Gross / (1 + Tax Rate)).If we know gross and net, use Gross - Net. |
| Detail.UnitPrice | If there is a stock code, look up the product sell price in the Products file |
| Detail.CostPrice | If there is a stock code, look up the product's average stock value in the Products file. For a transaction of type CI or CP, the buy price. |
| Detail.Unit | If there is a stock code, look up the product sell or buy unit in the Products file |
Transaction Field Names
The following table gives a list of any special import requirements. For a full list of fields, refer to Appendix A—Field Descriptions.
| Field | Size | Description |
| OurRef | 11 | For Cash Payments, the cheque number; for Cash Receipts, the receipt number; for Debtor Invoices, the invoice number; for Creditor Invoices, the order number. |
| TransDate | The date of the transaction. This should be normally specified in dd/mm/yy format (If your system is set up for U.S. date formats, you will need to specify dates in mm/dd/yy format). MoneyWorks will also accept dates in d mmm yyyy format. | |
| DueDate | For invoices, this is the date that the invoice is due for payment. For cash transactions, you should set it to the same as the transaction date (TransDate) | |
| Type | 2 | CP for Cash Payment; CR for Cash ReceiptDI for Debtor Invoice; CI for Creditor InvoiceSO for Sales Order; PO for Purchase Order; QU for Quote |
| TheirRef | 21 | For debtor invoices, this is the customer's order number; for creditor invoices, this is your order number; for cash transactions, this field is not used. |
| NameCode | 11 | For debtor invoices, this is the debtor code; for creditor invoices, it is the creditor code. |
| Flag | 5 | This can be any text. If this field is not blank, a flag icon shows up in the status column of the transaction list. |
| Gross | The gross amount of the transaction. This must be equal to the sum of the detail line gross values (which it will be if you use the Work it Out option). | |
| ToFrom | 255 | The name of the person to whom the invoice or cheque is written or from whom cash or the invoice is received. The customer or supplier name is used if the Work it Out option is set. |
| Contra | 7 | For Cash Payments, the account code of the bank account from which the payment is made; For Cash Receipts, the account code of the bank to which the receipt will be deposited; For Debtor Invoices, the Accounts Receivable control account; For Creditor Invoices, the Accounts Payable control acct. |
| Hold | Yes or No, 1 or 0, true or false | |
| Salesperson | 5 | The salesperson field. This is the field that can be used for appending a department to product control accounts during data entry. Transaction importing will append this value to the Detail.Account field only if you have the Work It Out option set for Detail.Account and the product's control account has the Append Salesperson option set. |
| Recurring | A pseudo field for setting the recurrence of a transaction. The parameters are encoded with whitespace separating the 5 parameters (start d m w finish). Examples:
"3/8/07 3 2 * *" -- 3rd day of every second month
"1/8/07 14 * * *" -- every 14th day
"31/7/07 2 * 3 *" -- every second wednesday
"22/8/07 3 1 5 *" -- 3rd friday of every month
"31/7/7 32 1 * *" -- last day of every month
"1/8/07 7 * * 31/12/08" -- every 7 days, finishing 31/12/08
"1/8/07 7 * * 5" -- every 7 days, recurring 5 times | |
| n.Detail.Account | 13 | The account code for the detail line |
| n.Detail.TaxCode | 3 | The tax code for the detail line. |
| n.Detail.Net | The net amount for the detail line. This will be the extended amount for a product transaction. | |
| n.Detail.Tax | The GST/VAT or Sales Tax amount for the detail line | |
| n.Detail.Gross | The gross amount for the detail line. This must be equal to the sum of the Net and the tax | |
| n.Detail.Description | 255 | The description for the detail line |
| n.Detail.StockQty | The quantity of a product bought or sold | |
| n.Detail.StockCode | 15 | The product code of a product |
| n.Detail.CostPrice | The cost price of the product. Use this for purchase transactions. | |
| n.Detail.SaleUnit | 3 | The selling unit of the product |
| n.Detail.UnitPrice | The selling price of the product. Use this for sales transactions. | |
| n.Detail.JobCode | 9 | The job code of the job to which the item relates |
| Name.CustType | The type of customer: 0 for not a customer; 1 for a customer; 2 for a debtor. | |
| Name.SuppType | The type of supplier: 0 for not a supplier; 1 for a supplier; 2 for a creditor. | |
| Name.Terms | The payment terms for a creditor. A positive number N, denotes N days. A negative number -N, denotes Nth day of month following. | |
| Name.Hold | 0 for No, 1 for Yes. This corresponds to the Hold check box for debtors. |