This is the full MoneyWorks database schema. You can get this in XML format by exporting "xmlschema".
When accessing the database from external systems, be sure to pay attention to which fields are indexed. Indexed fields should always be used for any searches that ae conducted frequently by API clients.
account
The principle dimension of the general ledger. Each account may or may not be departmentalised
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| code | string (7 chars) | indexed importable | The account code. Codes should ideally be numbered hierarchically for easy summarisation |
| type | char (2) | indexed importable | The account type. This will be one of IN, SA, EX, CS, CA, CL, FA, TA, TL or SF for Income, Sales, Expense, Cost of Sales, Current Asset, Current Liability, Fixed Asset, Term Asset, Term Liability or Shareholder's Funds respectively. |
| group | string (5 chars) | indexed importable | The department group for the account (defined in the Groups list, stored in the General table with S prefix). When present, the account has one ledger record for each department in the group |
| category | string (7 chars) | indexed importable | The category code for the account (blank if Category is None). Category codes are defined in the Categories list (and stored in the General table with a C code prefix) |
| description | string (63 chars) | importable script-mutable | Description shown in transaction entry and reports |
| pandl | string (7 chars) | indexed importable | The Profit and Loss account for IN/EX/SA/CS accounts. Balances are transferred to this account at the EOFY boundary |
| taxcode | string (5 chars) | importable cond-mutable | The tax code for the account, related to taxrate.taxcode |
| flags | integer (short) | importable | |
| system | char (2) | indexed importable | The "system" account type. The account types Bank Account, Profit & Loss, Accounts Receivable, Accounts Payable, GST Received and GST Paid are special system account types. Accounts of these types contain the codes: "BK", "PL", "AR", "AP", "GR", or "GP" (respectively) in the System field. Ordinary account types have " " (2 spaces) in the System field. |
| created | timestamp | importable unsigned | The date/time the account was created. |
| category2 | string (15 chars) | importable script-mutable | User defined, for analysis |
| category3 | string (15 chars) | importable script-mutable | User defined, for analysis |
| category4 | string (15 chars) | importable script-mutable | User defined, for analysis |
| accountantcode | string (9 chars) | importable script-mutable | Code in accountant's chart that corresponds to this account. |
| colour | integer (short) | importable cond-mutable | The colour, represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| currency | string (3 chars) | importable | The ISO currency code (empty for local currency accounts). Used only for bank and AR/AP accounts |
| securitylevel | integer (short) | indexed importable | The security level for the account. Users must have at least this security level to view the account or transactions that use it |
| bankaccountnumber | string (23 chars) | importable script-mutable | For bank accounts, the number of the bank account. |
| balancelimit | decimal | ||
| manualchequenumber | string (11 chars) | importable | Next manual cheque number in sequence (for bank accounts) |
| printedchequenumber | string (11 chars) | importable | Next batch cheque number in sequence (for bank accounts) |
| laststatementimport | timestamp | importable unsigned | Last bank statement import cut-off date |
| comments | string (1023 chars) | importable script-mutable | User notes on the account |
| manualchequenumdigits | integer (byte) | ||
| printedchequenumdigits | integer (byte) | ||
| usernum | float (double) | importable script-mutable | General storage for use by scripts |
| usertext | string (255 chars) | importable script-mutable | General storage for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| feedid | string (31 chars) | importable script-mutable | |
| cashflow | string (7 chars) | importable script-mutable | |
| cashforecast | string (31 chars) | importable script-mutable | |
| ebitda | string (1 chars) | importable cond-mutable | Tag to specify EBITDA status of account for reporting ("I" for Interest, "T" for Tax, "D" for Depreciation/Amortisation, otherwise blank) |
| importformat | string (9 chars) | importable |
ledger
Stores ledger balances for 90 accounting periods, plus budgets. There is one record for each account-department combination
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| accountcode | string (7 chars) | importable | |
| department | string (5 chars) | indexed importable | |
| category | string (7 chars) | indexed importable | |
| classification | string (5 chars) | indexed importable | |
| type | char (2) | indexed importable | |
| balance | decimal | ||
| budgeta | integer (long) | importable | |
| budgetb | integer (long) | importable | |
| concat | string (13 chars) | indexed importable | |
| system | char (2) | indexed importable | |
| usernum | float (double) | importable script-mutable | |
| usertext | string (255 chars) | importable script-mutable | |
| taggedtext | string (255 chars) | importable script-mutable | |
| … plus per-period numeric series fields (balance, budget) covering ~90 past periods and ~18 future periods | |||
general
Combined storage for categories, classifications and groups of departments
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| code | string (9 chars) | indexed importable | A prefixed code storing a category, classification, or group code. The prefixes are: C for Category, D for Classification, S for Group |
| description | string (31 chars) | importable | |
| date | date | importable unsigned | |
| long | integer (long) | importable |
department
Departments are the secondary dimension of the general ledger
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| code | string (5 chars) | indexed importable | |
| description | string (35 chars) | importable script-mutable | |
| classification | string (5 chars) | importable | The classification code for the department, defined in the Classifications list and stored in the General table with a D prefix |
| custom1 | string (15 chars) | importable script-mutable | |
| custom2 | string (9 chars) | importable script-mutable | |
| flags | integer (short) | importable | |
| usernum | float (double) | importable script-mutable | General storage for use by scripts |
| usertext | string (255 chars) | importable script-mutable | General storage for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
link
Many-many group-department link table defining the departments that belong to each department group
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| dept | string (5 chars) | indexed importable | |
| group | string (5 chars) | indexed importable |
transaction
Accounting transaction and orders
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | indexed unsigned | The sequence number of the transaction. |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| ourref | string (11 chars) | indexed importable auto cond-mutable | The reference number of the transaction. For Cash Payments, this is the cheque number. For Cash Receipts it is the receipt number. For Debtor Invoices, it is the invoice number and for Creditor Invoices it is your order number. For Journals, it is the Journal number, prefixed with the type of journal (JN for general journal, JS for stock journal, BK for banking journal). |
| transdate | date | indexed importable auto unsigned cond-mutable | The transaction date. |
| enterdate | date | indexed unsigned | The date on which the transaction was entered. |
| duedate | date | importable auto unsigned cond-mutable | The date on which payment is due |
| period | integer (short) | indexed | A number representing the period of the transaction. This number will be (100 * year_number + period_number), where year_number is a number in the range 0-99, with year 1 being the first year of operation for your MoneyWorks document, and period_number is a number in the range 1-12, with 1 representing the first period of your financial year. |
| type | string (3 chars) | indexed importable auto | The transaction type.--see following table. |
| theirref | string (31 chars) | importable script-mutable | For debtor invoices, the customer's Order No. For creditor invoices, the supplier's invoice number. For receipts, the cheque number |
| namecode | string (11 chars) | indexed importable | Customer or Supplier Code, related to Name.Code |
| flag | string (5 chars) | importable script-mutable | Bit flags. Constants are defined with fTrans_ prefix |
| description | string (1023 chars) | importable cond-mutable | The description of the transaction. |
| gross | decimal | importable auto | The gross value of the transaction. |
| analysis | string (9 chars) | importable script-mutable | User-defined analysis |
| contra | string (7 chars) | importable auto | For CP and CR transactions, this contains the account code of the bank that was selected in the bank pop-up menu. For invoices this is the accounts payable/receivable control acct. Related to account.code |
| tofrom | string (255 chars) | importable auto cond-mutable | For a payment, the To field. For a receipt, the From field. |
| status | string (1 chars) | indexed | This will be either a "U" (for unposted) or "P" (for posted). |
| hold | boolean | importable auto cond-mutable | True if the transaction is on hold |
| datepaid | date | unsigned | The date the last payment for an invoice was made |
| amtpaid | decimal | The amount of the invoice that has been paid | |
| payamount | decimal | The amount of the invoice that you have elected to pay a creditor in the next payment run. | |
| aging | integer (short) | The aging cycle for the transaction | |
| taxamount | decimal | The amount of GST involved for the transaction. | |
| taxcycle | integer (short) | A number representing the GST cycle in which the transaction was processed for GST. This is 0 for transactions not yet processed for GST. | |
| recurring | boolean | importable | True if the transaction is a recurring transaction. "False" if it isn't. |
| printed | integer (short) | script-mutable | The number of times the transaction has been printed using an INVC form |
| flags | integer (long) | See Transaction Flags table below | |
| taxprocessed | decimal | ||
| salesperson | string (5 chars) | importable cond-mutable | The salesperson for the transaction. If the transaction involves any products with the "Append Salesperson" attribute set, the value of this field will be appended to that products sales and/or cost of goods account code. |
| colour | integer (short) | importable cond-mutable | The colour, represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| bankjnseq | integer (long) | unsigned | |
| paymentmethod | integer (short) | importable | The payment method for the transaction (0 = None, 1 = Cash, 2 = Cheque, 3 = Electronic, 4 = Credit Card, 5-7 = user defined) |
| timeposted | timestamp | unsigned | Date and time transaction is posted. |
| securitylevel | integer (short) | indexed | The transaction's security level. This is set to the highest security level of the visible detail lines. |
| user1 | string (255 chars) | importable script-mutable | User defined |
| user2 | string (255 chars) | importable script-mutable | User defined |
| user3 | string (255 chars) | importable script-mutable | User defined |
| promptpaymentdate | date | importable auto unsigned script-mutable | The date the prompt payment discount expires |
| promptpaymentamt | decimal | importable auto | The amount of the eligible prompt payment discount |
| prodpricecode | string (1 chars) | importable auto | Pricing code (A-F) |
| mailingaddress | string (255 chars) | importable cond-mutable | Transaction's mailing address. Blank if default from name. |
| deliveryaddress | string (255 chars) | importable cond-mutable | The delivery address for this transaction. Used only if the address is overriding the delivery address from the Name record |
| freightcode | string (31 chars) | importable | Freight code used for orders |
| freightamount | decimal | importable | Freight amount of order |
| freightdetails | string (255 chars) | importable script-mutable | Details of freight for order |
| specialbank | string (31 chars) | importable script-mutable | For receipts, the bank number of the cheque |
| specialbranch | string (31 chars) | importable script-mutable | For receipts, the branch of the cheque |
| specialaccount | string (31 chars) | importable script-mutable | For receipts, the bank account of the cheque |
| currency | string (3 chars) | importable | |
| exchangerate | float (double) | importable | The exchange rate (0 for base currency transactions) |
| enteredby | string (3 chars) | Initials of user who entered the transaction | |
| postedby | string (3 chars) | Initials of user who posted the transaction | |
| amtwrittenoff | decimal | For invoices, the amount written off in a write-off | |
| ordertotal | decimal | The total of the order | |
| ordershipped | decimal | The amount shipped of an order | |
| orderdeposit | decimal | The accumulated deposit on an order | |
| originatingorderseq | integer (long) | The sequence number of the order that created the invoice through the ship or receive goods commands | |
| currencytransferseq | integer (long) | ||
| promptpaymentterms | integer (short) | ||
| promptpaymentdisc | float | ||
| approvedby1 | string (3 chars) | cond-script-mutable | Initials of first user to approve transaction. This field can only be set to the current user's initials (or blank) using a script. |
| approvedby2 | string (3 chars) | cond-script-mutable | Initials of second user to approve transaction. This field can only be set to the current user's initials (or blank) using a script. |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| user4 | string (15 chars) | importable script-mutable | User defined |
| user5 | string (15 chars) | importable script-mutable | User defined |
| user6 | string (15 chars) | importable script-mutable | User defined |
| user7 | string (15 chars) | importable script-mutable | User defined |
| user8 | string (15 chars) | importable script-mutable | User defined |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| emailed | integer (short) | script-mutable | Non zero if transaction has been emailed |
| transferred | integer (short) | script-mutable | Non zero if transaction has been sent as eInvoice |
| paynowtoken | string (99 chars) | script-mutable |
detail
Line items belonging to transactions
| Field | Type | Properties | Notes |
|---|---|---|---|
| detail.sequencenumber | integer (long) | unsigned | |
| detail.lastmodifiedtime | timestamp | unsigned | |
| detail.parentseq | integer (long) | indexed auto | The sequence number of the parent transaction, related to transaction.sequencenumber |
| detail.sort | integer (short) | auto | The order of the detail lines as displayed in a transaction |
| detail.account | string (13 chars) | indexed importable | The account code and department code for the line-item separated by a hyphen. Department will be blank for non-departmentalised accounts |
| detail.dept | string (5 chars) | auto | The department code (which is also stored in the detail.account field) |
| detail.postedqty | float (double) | For stock purchase transactions, the buy quantity adjusted for the product conversion factor, gives the qty in sell units, which is the posted qty. | |
| detail.taxcode | string (5 chars) | importable auto | The tax code of the account. Related to Taxrate.TaxCode |
| detail.gross | decimal | importable | The gross value of the detail line. |
| detail.tax | decimal | importable | The tax (GST, VAT etc) amount of the detail line. |
| detail.debit | decimal | The debit value of the detail line. This is the amount by which the ledger identified by detail.account is debited when the transaction is posted. It corresponds to the Net or Extension for a CR or DI. | |
| detail.credit | decimal | The credit value of the detail line. This is the amount by which the account is credited when the transaction gets posted. It corresponds to the Net or Extension for a CP or CI. | |
| detail.net | decimal | importable | Used for import and export, but actually calculated from detail.debit - detail.credit; importing negative net actually imports to credit |
| detail.description | string (1023 chars) | importable auto | The description for the detail line |
| detail.stockqty | float (double) | importable | The quantity of the product specified by Detail.StockCode that is being purchased or sold. The units correspond to either the buyUnits for the product or the SellUnits for the product depending on whether this is a purchase (CP/CI) or a sale (CR/DI) transaction. |
| detail.stockcode | string (31 chars) | indexed importable auto | The product code for the detail line. This will be blank if the transaction is a service-type transaction. Related to product.code |
| detail.costprice | float (double) | importable auto | This is the base currency buy-price of the product. It is represented as dollars per buying unit for a purchase or as dollars per selling unit. for a sale. For a sale, the cost price is taken from the AverageValue of the product. |
| detail.unitprice | float (double) | importable | For a purchase, this is the same as the cost price. For a sale, this is the unit selling price of the product exclusive of GST and discount. |
| detail.statement | integer (long) | indexed auto | The sequence number for the reconciliation record (BankRecs.SequenceNumber) for which this detail line was reconciled. It is normally only used for detail lines which specify a bank account. If the item is not yet reconciled it contains a 0 (or a -1 if the reconciliation has been saved but not finalised). |
| detail.jobcode | string (9 chars) | indexed importable | Related to Job.Code. Use for ascribing line items to job income or costs |
| detail.saleunit | string (5 chars) | importable | For product transaction detail lines, this is the selling unit of measure as copied from the product record. |
| detail.discount | float (double) | importable | The percent discount for the line |
| detail.flags | integer (short) | Bit-flags. Constants are defined with fDetail_ prefix | |
| detail.orderqty | float (double) | importable | The original order quantity for an order |
| detail.backorderqty | float (double) | The amount currently on backorder for an order | |
| detail.prevshipqty | float (double) | For orders, the quantity of the item that has been previously shipped on that order. The OrderQty less the PrevShipQty will give the quantity on backorder | |
| detail.basecurrencynet | decimal | The detail.net amount converted to the base currency | |
| detail.serialnumber | string (31 chars) | importable | The items's serial/batch number. Related to Inventory.Identifier |
| detail.period | integer (short) | Same as the transaction.period field | |
| detail.transactiontype | char (2) | The first two characters of the transaction type (i.e. CP, CR, CI, DI, JN, PO, SO, QU) | |
| detail.securitylevel | integer (short) | indexed | The security level of the line (inherited from the account's security level) |
| detail.revalueqty | float (double) | ||
| detail.stocklocation | string (15 chars) | importable | The item's location code, related to a locations defined in the Stock Locations validation list (Lists.Item where Lists.ListID = "Stock Locations" |
| detail.orderstatus | boolean | 0 if not shipped or part shipped, 1 if fully shipped | |
| detail.expensedtax | decimal | The amount of non-claimable sales tax on the line (only set for transactions of type CI and CP that involve sales tax). When a line is saved, the net is adjusted up by this amount and the tax down; when the transaction is viewed, the reverse happens. | |
| detail.date | date | importable | The date on the detail line (also the expiry date for time-limited batches) |
| detail.moreflags | integer (short) | More bit flags | |
| detail.usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| detail.usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| detail.taggedtext | string (255 chars) | importable auto script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| detail.noninvrcvdnotinvoicedqty | float (double) | auto | The quantity of non-inventoried items on an order received but not invoiced |
| detail.custom1 | string (31 chars) | importable auto script-mutable | User-defined for use by scripts |
| detail.custom2 | string (31 chars) | importable script-mutable | User-defined for use by scripts |
| detail.originalunitcost | float (double) | The unit cost of an inventoried item before the transaction was posted (available for some lines involving stock replenishment) |
log
A write-only lot of major accounting milestones and changes to the GL structure
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| description | char (4) | importable | |
| who | string (3 chars) | importable | |
| info1 | string (15 chars) | importable | |
| info2 | string (15 chars) | importable | |
| info3 | string (15 chars) | importable |
taxrate
Codes and rates for input-output taxes applied to transaction line-items
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| taxcode | string (5 chars) | importable | The tax code |
| paidaccount | string (7 chars) | importable | The control account for GST paid out under this rate |
| recaccount | string (7 chars) | importable | The control account for GST received out under this rate |
| rate1 | float (double) | importable | Rate used before changeover date |
| date | date | importable unsigned | Tax rate changeover date |
| rate2 | float (double) | importable | Rate used on or after changeover date |
| combine | integer (short) | Flags (how 2nd tier tax is combined) | |
| combinerate1 | float (double) | importable | 2nd tier rate used before changeover date (PST) |
| combinerate2 | float (double) | importable | 2nd tier rate used after changeover date (PST) |
| gstreceived | decimal | importable | Total GST received for taxcode in last GST finalisation |
| netreceived | decimal | importable | Net received for taxcode in last GST finalisation |
| gstpaid | decimal | importable | Total GST paid for taxcode in last GST finalisation |
| netpaid | decimal | importable | Net paid for taxcode in last GST finalisation |
| ratename | string (59 chars) | importable | |
| reportcyclestart | integer (short) | importable | |
| reportcycleend | integer (short) | importable | |
| reportdate | date | importable unsigned | |
| pstreceived | decimal | importable | |
| pstpaid | decimal | importable | |
| type | integer (short) | importable | |
| combination | string (31 chars) | importable | |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| aliascode | string (5 chars) | importable script-mutable | |
| aliascountry | string (3 chars) | importable script-mutable | |
| reversedrate1 | float (double) | importable | |
| reversedrate2 | float (double) | importable |
message
Periodic reminder messages and recurring transaction time definitions
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| startdate | date | importable unsigned script-mutable | |
| enddate | date | importable unsigned script-mutable | |
| nextdate | date | indexed importable unsigned script-mutable | |
| keep | boolean | importable | |
| ref | integer (long) | importable | |
| lastday | integer (byte) | importable | |
| ndaily | integer (byte) | importable | |
| nweekly | integer (byte) | importable | |
| nmonthly | integer (byte) | importable | |
| once | integer (byte) | importable | |
| xtimes | integer (byte) | importable | |
| forever | integer (byte) | importable | |
| day | integer (byte) | importable | |
| type | integer (short) | importable | |
| dayofweek | integer (byte) | importable | |
| n | integer (byte) | importable | |
| x | integer (byte) | importable | |
| avoidweekends | integer (byte) | importable | |
| reverse | integer (byte) | importable | |
| kill_next_time | integer (byte) | ||
| message | string (255 chars) | importable script-mutable | |
| user | string (3 chars) | importable | |
| usernum | float (double) | importable script-mutable | |
| usertext | string (255 chars) | importable script-mutable | |
| taggedtext | string (255 chars) | importable script-mutable |
name
Customer and Supplier and other legal entity records
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| code | string (11 chars) | indexed importable auto | The name code. For non-sundries, only the first ten characters are used. |
| name | string (255 chars) | importable script-mutable | Name of company |
| contact | string (39 chars) | importable script-mutable | Contact person 1 in the company |
| position | string (39 chars) | importable script-mutable | Position of contact person 1 |
| address1 | string (59 chars) | importable script-mutable | Mailing Address (first line) |
| address2 | string (59 chars) | importable script-mutable | Mailing Address (second line) |
| address3 | string (59 chars) | importable script-mutable | Mailing Address (third line) |
| address4 | string (59 chars) | importable script-mutable | Mailing Address (fourth line) |
| delivery1 | string (59 chars) | importable script-mutable | Delivery address (first line) |
| delivery2 | string (59 chars) | importable script-mutable | Delivery address (second line) |
| delivery3 | string (59 chars) | importable script-mutable | Delivery address (third line) |
| delivery4 | string (59 chars) | importable script-mutable | Delivery address (fourth line) |
| phone | string (19 chars) | importable script-mutable | Phone number |
| fax | string (19 chars) | importable script-mutable | Facsimile number |
| category1 | string (15 chars) | importable script-mutable | User defined |
| category2 | string (15 chars) | importable script-mutable | User defined |
| category3 | string (15 chars) | importable script-mutable | User defined |
| category4 | string (15 chars) | importable script-mutable | User defined |
| customertype | integer (short) | indexed importable | 0 for not a customer, 1 for customer, 2 for debtor |
| d90plus | decimal | Debtor 90 days+ balance (3 cycles of manual ageing). | |
| d60plus | decimal | Debtor 60 day balance (2 cycles of manual ageing). | |
| d30plus | decimal | Debtor 30 day balance (1 cycle of manual ageing). | |
| dcurrent | decimal | For a debtor, the current balance. The total balance for the debtor is the sum of all the balance fields. | |
| ccurrent | decimal | For a creditor, the current balance. | |
| debtorterms | integer (short) | importable | If > 0, within N days; if < 0, Nth day of month following |
| creditorterms | integer (short) | importable | If > 0, within N days; if < 0, Nth day of month following |
| bank | string (7 chars) | importable script-mutable | The customer's bank (e.g. BNZ) |
| accountname | string (63 chars) | importable script-mutable | The bank account name (e.g. XYZ Trading Company) |
| bankbranch | string (21 chars) | importable script-mutable | The bank branch (e.g. Main St.) |
| theirref | string (15 chars) | importable script-mutable | The reference code by which the supplier or customer refers to your company. |
| hold | boolean | importable script-mutable | True if the debtor is on hold ("False" otherwise) |
| recaccount | string (7 chars) | importable auto | The Accounts Receivable control account code for a debtor. |
| payaccount | string (7 chars) | importable auto | The Accounts Payable control account code for a creditor. |
| kind | integer (short) | indexed | The kind of Name. 0 for a template, 1 for a normal |
| creditlimit | integer (long) | importable script-mutable | The credit limit for a debtor |
| discount | decimal | importable script-mutable | Discount field for a customer |
| comment | string (1023 chars) | importable script-mutable | A comment |
| suppliertype | integer (short) | indexed importable | 0 for not a supplier, 1 for supplier, 2 for creditor |
| colour | integer (short) | importable cond-mutable | The colour, represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| salesperson | string (5 chars) | importable script-mutable | Code for salesperson for client--automatically copied to the transaction.salesperson field. |
| taxcode | string (5 chars) | importable | Tax code override |
| splitmode | integer (short) | ||
| postcode | string (11 chars) | importable script-mutable | Post code |
| state | string (7 chars) | importable script-mutable | State (for postal address) |
| bankaccountnumber | string (23 chars) | importable script-mutable | The bank account number of the name, as supplied by their bank |
| currency | string (3 chars) | importable | Currency of customer/supplier (blank if local). Related to OffLedger.Name where OffLedger.Kind= "CUR" |
| paymentmethod | integer (short) | importable cond-mutable | Payment method (0 = None, 1 = Cash, 2 = Cheque, 3 = Electronic, etc). |
| dbalance | decimal | Sum of D90Plus, D60Plus, D30Plus and DCurrent | |
| ddi | string (19 chars) | importable script-mutable | Direct dial number for contact 1 |
| string (139 chars) | importable script-mutable | email address for contact 1 | |
| mobile | string (19 chars) | importable script-mutable | Mobile phone number for contact 1 |
| afterhours | string (19 chars) | importable script-mutable | After hours phone number for contact 1 |
| contact2 | string (39 chars) | importable script-mutable | Name of contact person 2 |
| position2 | string (39 chars) | importable script-mutable | Position of contact 2 |
| ddi2 | string (19 chars) | importable script-mutable | Direct dial number for contact 2 |
| email2 | string (139 chars) | importable script-mutable | email address for contact 2 |
| mobile2 | string (19 chars) | importable script-mutable | Mobile phone number for contact 2 |
| afterhours2 | string (19 chars) | importable script-mutable | After hours phone number for contact 2 |
| weburl | string (63 chars) | importable script-mutable | Web URL |
| productpricing | string (1 chars) | importable cond-mutable | Pricing level for customer. (A-F) |
| dateoflastsale | date | unsigned | Date of last invoice or cash sale |
| splitacct1 | string (13 chars) | importable | Account code for first split account |
| splitacct2 | string (13 chars) | importable | Account code for remainder split account |
| splitpercent | float (double) | importable | Percent of allocation to be put into SplitAcct1 |
| splitamount | decimal | ||
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| custpromptpaymentterms | integer (short) | importable cond-mutable | 0 for no prompt payment; > 0 for within N days; < 0 for by Nth date of following month |
| custpromptpaymentdiscount | float | importable cond-mutable | |
| supppromptpaymentterms | integer (short) | importable cond-mutable | |
| supppromptpaymentdiscount | float | importable cond-mutable | Percentage amount of prompt payment discount offered by supplier |
| lastpaymentmethod | integer (short) | PaymentMethod used in previous transaction | |
| creditcardnum | string (19 chars) | importable script-mutable | Credit card number |
| creditcardexpiry | string (5 chars) | importable script-mutable | Expiry date of credit card |
| creditcardname | string (63 chars) | importable script-mutable | Name on credit card |
| taxnumber | string (31 chars) | importable script-mutable | Their tax number (GST#, VAT#, ABN etc, depending on country) |
| custom1 | string (255 chars) | importable script-mutable | User-defined |
| custom2 | string (255 chars) | importable script-mutable | User-defined |
| custom3 | string (15 chars) | importable script-mutable | User-defined |
| custom4 | string (15 chars) | importable script-mutable | User-defined |
| deliverypostcode | string (11 chars) | importable script-mutable | Postcode/zipcode of delivery address |
| deliverystate | string (7 chars) | importable script-mutable | Sate of delivery address |
| addresscountry | string (59 chars) | importable script-mutable | |
| deliverycountry | string (59 chars) | importable script-mutable | |
| receiptmethod | integer (short) | importable cond-mutable | Preferred payment method of customers. 1 = Cash, 2 = Cheque etc. |
| abuid | string (31 chars) | importable | Mac Address Book Universal ID--set for imported address book entries only |
| bankparticulars | string (31 chars) | importable script-mutable | |
| flags | integer (short) | See Names Flags table below | |
| salutation | string (39 chars) | importable script-mutable | Salutation for contact 1 |
| salutation2 | string (39 chars) | importable script-mutable | Salutation for contact 2 |
| memo | string (255 chars) | importable script-mutable | Memo/notes for contact 1 |
| memo2 | string (255 chars) | importable script-mutable | Memo/notes for contact 2 |
| role | integer (short) | importable script-mutable | Roles for contact 1. This is a bit mapped field, with each bit representing a role. |
| role2 | integer (short) | importable script-mutable | Roles for contact 2. This is a bit mapped field, with each bit representing a role. |
| custom5 | string (15 chars) | importable script-mutable | User-defined |
| custom6 | string (15 chars) | importable script-mutable | User-defined |
| custom7 | string (15 chars) | importable script-mutable | User-defined |
| custom8 | string (15 chars) | importable script-mutable | User-defined |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| einvoicingid | string (31 chars) | importable script-mutable |
payments
Many-many link table linking payments to invoices or overpayments to customers
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| invoiceid | integer (long) | indexed importable auto | The sequence number of the invoice that is being paid. For a debtor overpayment (where no invoice is available) this stores the Name,SequenceNumber, with the high bit set, making it negative (add 2147483648 to it to get the namecode sequencenumber). Otherwise normally relates to Transaction.SequenceNumber |
| cashtrans | integer (long) | indexed | The sequencenumber of the payment/receipt. Relates to Transaction.SequenceNumber |
| date | date | unsigned | The receipt date |
| gstcycle | integer (short) | The tax cycle when the receipt was processed for GST/VAT/Tax by the GST Report. This will be negative if processed on an invoice/accruals basis. | |
| amount | decimal | importable auto | The amount of the receipt that was allocated to the invoice |
contacts
Additional staff contacts belonging to Name records
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| parentseq | integer (long) | indexed importable script-mutable | Sequencenumber of the Name record for the contact. Related to Name.sequencenumber |
| order | integer (short) | importable script-mutable | The order of the contact |
| role | integer (short) | importable script-mutable | Roles for the contact. This is a bit field, with each bit representing a role |
| contact | string (39 chars) | importable script-mutable | Contact's name |
| position | string (39 chars) | importable script-mutable | Contact's position e.g. "CEO" |
| salutation | string (39 chars) | importable script-mutable | Contact's salutation |
| ddi | string (19 chars) | importable script-mutable | Contact's direct dial |
| string (139 chars) | importable script-mutable | Contact's email address | |
| mobile | string (19 chars) | importable script-mutable | Contact's mobile number |
| afterhours | string (19 chars) | importable script-mutable | Contact's after hours number |
| memo | string (255 chars) | importable script-mutable | Memo/notes on contact |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
product
Products and services the company buys and sells
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| code | string (31 chars) | indexed importable | The product code. |
| supplierscode | string (39 chars) | importable script-mutable | The product code that your usual supplier uses to refer to the product. |
| supplier | string (11 chars) | importable cond-mutable | The supplier code of your usual supplier. If present, this should be the code of a supplier in the names list. |
| description | string (255 chars) | importable script-mutable | The name of the product. |
| comment | string (1023 chars) | importable script-mutable | Any additional information about the product. |
| category1 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category2 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category3 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category4 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| salesacct | string (13 chars) | importable | The Income account that is credited whenever the product is sold. |
| cogacct | string (13 chars) | importable | Cost Of Goods account. If you only buy the product, this is the Expense account that is debited each time you purchase this product. If you stock and sell the product, this account is debited when you sell the product. |
| stockacct | string (13 chars) | importable | The Current Asset account that is debited whenever you buy a product that you stock and is credited whenever you sell it. |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| sellunit | string (5 chars) | importable script-mutable | The units in which you sell the product. e.g. "ea", "kg", "ml", "doz" |
| sellprice | float (double) | importable cond-mutable | The "A" sell price at which you sell the product. This is GST exclusive and exclusive of any discount that you may apply. |
| plussage | float | importable cond-mutable | Amount to add to the purchase price for margin calculations within the product sell matrix. Not included in the costs maintained by MoneyWorks |
| buyweight | float | importable cond-mutable | The weight/volume of the buy unit of the item |
| buyunit | string (5 chars) | importable script-mutable | The units in which you buy the product. e.g. "ea", "kg", "ml", "dz". If the buy units are different from the sell units, you must supply a scalar conversion factor in the ConversionFactor field that will convert from buy units to sell units. |
| costprice | float (double) | The standard cost of the item. For purchased items, this is the purchase price (adjusted for discount if any) converted to the base currency. | |
| conversionfactor | float (double) | importable | This is used to calculate the number of sell units that equate to one buy unit. When you purchase product, the quantity purchased is divided by this conversion factor to calculate the number of selling units of stock on hand. Note that the reciprocal of the conversion factor is displayed on the product entry screen. |
| marginwarning | float (double) | script-mutable | The minimum margin/markup level for the product below which to show a warning on the Selling Price screen |
| selldiscount | float (double) | importable cond-mutable | The percentage discount. This is used only if the discount mode is 1 or 3 |
| selldiscountmode | integer (short) | importable cond-mutable | A number representing the discount mode as selected in the discount mode pop-up menu in the product entry window. 1=None; 2 = by customer; 3 = by product; 4=Add |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| stockonhand | float (double) | The total number of items of stock of the product that you have on hand. This is represented in the selling units for the product. Use the [SOHForLocation(location)](calculations.html#SOHForLocation) function for the stock at a given location | |
| stockvalue | float (double) | The value of the stock on hand (based on purchase cost) | |
| minbuildqty | float (double) | importable | The minimum build quantity--items must be built in multiples of this |
| normalbuildqty | float (double) | importable | The normal build/reorder quantity |
| reorderlevel | float (double) | importable script-mutable | The stock level at which a reordering warning should be given. (in selling units) |
| jobpricingmode | integer (short) | cond-mutable | Sell price determinator for job costing: 1 = Use Product Sell Price, 2 = Apply Job Markup to Standard Cost, 3 = Use Undiscounted Purchase Price |
| flags | integer (long) | importable script-mutable | See Product Flags table below |
| colour | integer (short) | importable cond-mutable | The colour of the product record (not necessarily of the actual product), represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| usemultipleprices | boolean | script-mutable | True if using multiple sell prices |
| sellpriceb | float (double) | importable cond-mutable | The B sell price (before GST and discounts) |
| sellpricec | float (double) | importable cond-mutable | The C sell price (before GST and discounts) |
| sellpriced | float (double) | importable cond-mutable | The D sell price (before GST and discounts) |
| sellpricee | float (double) | importable cond-mutable | The E sell price (before GST and discounts) |
| sellpricef | float (double) | importable cond-mutable | The F sell price (before GST and discounts) |
| qtybreak1 | float | importable cond-mutable | Quantity at which the first break price is used |
| qtybreak2 | float | importable cond-mutable | Quantity at which the second break price is used |
| qtybreak3 | float | importable cond-mutable | Quantity at which the third break price is used |
| qtybreak4 | float | importable cond-mutable | Quantity at which the fourth break price is used |
| qtybrksellpricea1 | float (double) | importable cond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak1 |
| qtybrksellpricea2 | float (double) | importable cond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak2 |
| qtybrksellpricea3 | float (double) | importable cond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak3 |
| qtybrksellpricea4 | float (double) | importable cond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak4 |
| qtybrksellpriceb1 | float (double) | importable cond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak1 |
| qtybrksellpriceb2 | float (double) | importable cond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak2 |
| qtybrksellpriceb3 | float (double) | importable cond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak3 |
| qtybrksellpriceb4 | float (double) | importable cond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak4 |
| type | string (1 chars) | indexed importable cond-mutable | P = product; R = resource; T = time; S = ship method; O=other. |
| count | float (double) | importable cond-mutable | |
| onorder | float (double) | ||
| stocktakestartqty | float (double) | The total stock on hand when a stock take is commenced. Use the [StockTakeStartQtyForLocation(location)](calculations.html#StockTakeStartQtyForLocation) function for the commencing stock at a given location | |
| stocktakevalue | float (double) | ||
| stocktakenewqty | float (double) | importable script-mutable | The total entered stock count for a stock take. Use the [StocktakeNewQtyForLocation(location)](calculations.html#StocktakeNewQtyForLocation) function for the entered stock count at a given location |
| barcode | string (19 chars) | indexed importable script-mutable | The item's barcode. This can be used in transaction entry instead of the product code |
| buypricecurrency | string (3 chars) | importable | The ISO currency of the last purchase of the item. Related to OffLedger.Name where OffLedger.Kind= "CUR" |
| buyprice | float (double) | importable cond-mutable | The undiscounted buy price of one buy unit of the item (in the currency of the purchase). This is updated automatically by MoneyWorks if "Update price whenever purchased" option is on |
| custom1 | string (255 chars) | importable script-mutable | For your own use |
| custom2 | string (255 chars) | importable script-mutable | For your own use |
| custom3 | string (15 chars) | importable script-mutable | For your own use |
| custom4 | string (15 chars) | importable script-mutable | For your own use |
| buytaxcodeoverride | string (5 chars) | importable cond-mutable | |
| selltaxcodeoverride | string (5 chars) | importable cond-mutable | |
| leadtimedays | integer (short) | importable script-mutable | The expected lead time e for the delivery of the product |
| hash | integer (short) | indexed | For fast searching of products by buy/sell/stock. A bit is set for each status: If buy, #0002; if sell, #0004, if inventory, #0008, Thus all inventoried items will have Hash >= 8. |
| sellweight | float | importable cond-mutable | The weight/volume of one sell unit |
| custom5 | string (15 chars) | importable script-mutable | For your own use |
| custom6 | string (15 chars) | importable script-mutable | For your own use |
| custom7 | string (15 chars) | importable script-mutable | For your own use |
| custom8 | string (15 chars) | importable script-mutable | For your own use |
| taggedtext | string (255 chars) | script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
inventory
Batch, location and serial number tracking records for products that are so tracked
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| productseq | integer (long) | indexed importable | The sequencenumber of the product master record for the item |
| location | string (15 chars) | importable | The location |
| identifier | string (31 chars) | importable | The serial/batch number of the item |
| expiry | date | importable unsigned | The expiry date for expiring batches |
| qty | float (double) | importable | The qty (stock on hand) of the item at this location. Use the [SOHForLocation(location)](calculations.html#SOHForLocation) function for the stock at a given location. |
| stocktakestartqty | float (double) | importable | The total stock on hand when a stock take is commenced. Use the [StocktakeStartQtyForLocation(location)](calculations.html#StockTakeStartQtyForLocation) function for the stock at a given location. |
| stocktakenewqty | float (double) | importable | The total entered stock count for a stock take. Use the [StocktakeNewQtyForLocation(location)](calculations.html#StocktakeNewQtyForLocation) function for the stock at a given location. |
job
Jobs of work in progress for clients
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| code | string (9 chars) | indexed importable | The job code. |
| description | string (255 chars) | importable script-mutable | The job name. |
| client | string (11 chars) | importable | The code of the client for whom the job is being done. Must be a debtor. |
| comment | string (1023 chars) | importable script-mutable | Comments on the job |
| startdate | date | importable unsigned script-mutable | The start date of the job |
| markup | float (double) | importable script-mutable | The percent markup applied to items used on the job. |
| quote | decimal | importable script-mutable | The amount quoted for the job. |
| billed | decimal | The amount billed to date for the job. | |
| status | char (2) | indexed importable cond-mutable | The status of the job. "QU" for quoted, "OP" for active, "CO" for complete |
| flags | integer (short) | ||
| colour | integer (short) | importable cond-mutable | The colour, represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| wipaccount | string (13 chars) | importable | |
| category1 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category2 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category3 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| category4 | string (15 chars) | importable script-mutable | Any value. This can be used for analysis purposes. |
| ordernum | string (31 chars) | importable script-mutable | The client's order number for the job |
| contact | string (63 chars) | importable script-mutable | The contact for the job |
| phone | string (19 chars) | importable script-mutable | The contact's phone number |
| enddate | date | importable unsigned script-mutable | The expected end date |
| manager | string (3 chars) | importable script-mutable | The manager for the job |
| percentcomplete | float | importable | Percent that the job is complete |
| variations | decimal | ||
| retentionsheld | decimal | ||
| retentionsowing | decimal | ||
| productpricing | string (1 chars) | ||
| retainpercent | float | ||
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| project | string (9 chars) | importable | Job code of project to which this belongs |
| targetdate | date | importable unsigned script-mutable | Target date for job |
| custom1 | string (255 chars) | importable script-mutable | For your own use |
| custom2 | string (255 chars) | importable script-mutable | For your own use |
| custom3 | string (15 chars) | importable script-mutable | For your own use |
| custom4 | string (15 chars) | importable script-mutable | For your own use |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| custom5 | string (15 chars) | importable script-mutable | For your own use |
| custom6 | string (15 chars) | importable script-mutable | For your own use |
| custom7 | string (15 chars) | importable script-mutable | For your own use |
| custom8 | string (15 chars) | importable script-mutable | For your own use |
assetlog
Log of asset acquisitions, and disposals
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| parentseq | integer (long) | importable script-mutable | Sequencenumber of asset |
| action | string (3 chars) | importable script-mutable | Type of action: AA - acquisition, AD - disposal, AP - part disposal, DS - straight line depreciation, DD - diminishing value depreciation, ME - memo, RV - revaluation |
| logdate | date | importable script-mutable | |
| qty | float (double) | importable script-mutable | Quantity |
| depreciation | decimal | importable script-mutable | Depreciation due to action |
| adjustment1 | decimal | importable script-mutable | Adjustments |
| adjustment2 | decimal | importable script-mutable | Adjustments |
| rate | float (double) | importable script-mutable | Depreciation rate used |
| privateusepercent | float | importable script-mutable | Private use percent |
| accumdepreciation | decimal | importable script-mutable | Accumulated depreciation after action |
| accumreval | decimal | importable script-mutable | Accumulated revaluation after action |
| closingvalue | decimal | importable script-mutable | Book value after action |
| transactionseq | integer (long) | importable | Sequencenumber of transaction associated with action |
| memo | string (255 chars) | importable script-mutable | User memo |
| disposedaccdepn | decimal | importable script-mutable | |
| gainlossondisposal | decimal | importable script-mutable | |
| gainlossondisposalprivate | decimal | importable script-mutable | |
| disposalaccdepnprivate | decimal | importable script-mutable |
build
BOM definition for manufactured products
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| build.productseq | integer (long) | indexed importable | The sequence number of the Product record to which the recipe belongs |
| build.order | integer (short) | ||
| build.qty | float (double) | importable | Quantity of component required |
| build.partcode | string (31 chars) | importable | Code of component |
| build.flags | integer (short) | importable | |
| build.memo | string (255 chars) | importable script-mutable | Memo for the component |
jobsheet
Time and materials used for Jobs
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| job | string (9 chars) | indexed importable | The job code. |
| qty | float (double) | importable cond-mutable | The quantity of the resource used |
| resource | string (31 chars) | indexed importable | The code of the resource |
| date | date | importable auto unsigned cond-mutable | The date the resource was used |
| costcentre | string (5 chars) | importable cond-mutable | The cost centre for which the resource was used |
| account | string (7 chars) | importable | The general ledger expense code for the resource |
| period | integer (short) | A number representing the period of the job sheet item. This is determined automatically by the Date field, and is stored in the same manner as the period field in the transaction file. | |
| units | string (5 chars) | importable auto cond-mutable | The units of resource used. |
| costprice | decimal | importable auto cond-mutable | The cost price of the resource used. |
| sellprice | decimal | importable auto cond-mutable | The estimated sell value of the resource used, based on the job markup. |
| memo | string (1023 chars) | importable script-mutable | A description of the job sheet item. |
| desttransseq | integer (long) | script-mutable | The sequence number of the invoice on which this item was billed out. |
| sourcetransseq | integer (long) | indexed | The sequence number of the originating transaction |
| dateentered | date | unsigned | The date the job sheet item was entered. |
| flags | integer (short) | Accessible via the Testflags function. 1 = Time, 2 = Material | |
| colour | integer (short) | importable cond-mutable | The colour, represented internally as a numeric index in the range 0-7 [but rendered as a textual colour name](#colourfield) |
| status | char (2) | indexed importable cond-mutable | The status field. "PE" if the item is pending (unbilled), "PR" if it is processed, "BU" if it is a budget entry. |
| type | char (2) | importable auto script-mutable | The type of entry. "IN" for income, "EX" for expense. |
| analysis | string (9 chars) | importable cond-mutable | The analysis field |
| billvalue | decimal | importable | |
| activitycode | string (31 chars) | importable cond-mutable | A free-form activity code |
| comments | string (255 chars) | importable script-mutable | General comments on the entry |
| batch | integer (long) | importable | For jobsheets entered using a Timesheet, the sequencenumber of the first jobsheet created when the Timesheet is accepted. This forms a unique batch number to identify all jobsheets entered on that time-share. |
| enteredby | string (3 chars) | indexed | User who entered the job sheet record |
| serialnumber | string (31 chars) | importable | Serial/Batch number of the item |
| stocklocation | string (15 chars) | importable | The location of the item |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| timeprocessed | timestamp | unsigned | Timestamp for when the job sheet was processed |
bankrecs
Bank reconciliation records
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | indexed unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| account | string (13 chars) | importable | |
| opening | decimal | importable | |
| closing | decimal | importable | |
| statement | integer (short) | importable | |
| date | date | importable unsigned | |
| reconciledtime | timestamp | importable unsigned | |
| discrepancy | decimal | importable |
asset
Fixed Assets that need to have depreciation tracked
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| code | string (19 chars) | indexed importable script-mutable | A unique code for the asset |
| description | string (63 chars) | importable script-mutable | The description of the asset |
| category | string (7 chars) | importable script-mutable | The Asset Category of the asset |
| serialnum | string (31 chars) | importable script-mutable | A serial number for the asset |
| qty | float (double) | importable script-mutable | The quantity of the asset (normally 1, but might be a set of similar assets such as desks) |
| expectedlife | integer (long) | importable script-mutable | The expected life in years of the asset |
| cost | decimal | importable script-mutable | The cost (per unit) of the asset |
| accumdepreciation | decimal | importable script-mutable | The accumulated depreciation recorded against the asset |
| acquisitiondate | date | importable unsigned script-mutable | Date on which the asset was acquired |
| lastdepreciateddate | date | importable unsigned script-mutable | Date on which the asset was last depreciated |
| acquisitionseq | integer (long) | script-mutable | The sequencenumber of the purchase transaction for the asset |
| disposalseq | integer (long) | script-mutable | The sequencenumber of the last disposal transaction for the asset |
| location | string (15 chars) | importable script-mutable | Where the asset is kept |
| dept | string (5 chars) | importable script-mutable | The asset department (must be a MoneyWorks Department) |
| privateusepercent | float | importable script-mutable | The percent of the asset used privately |
| status | string (3 chars) | script-mutable | The asset status (NEW, ACT - active, NDP - non-depreciable, OTH - other, DSP - disposed) |
| lastmodifiedby | string (3 chars) | script-mutable | User who last modified the asset record |
| lastrevalueddate | date | unsigned script-mutable | Date of last revaluation (blank if none) |
| expectedresidualvalue | decimal | importable script-mutable | Expected residual value |
| revalsurplusimpairamt | decimal | script-mutable | Total revalued amount (positive if surplus) |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| accumdepnadj | decimal | script-mutable | Total adjustments to accumulated depreciation due to revaluations |
| bookvalue | decimal | importable script-mutable | The current book value |
| disposaldate | date | unsigned script-mutable | Date last disposed |
| gainlossondisposal | decimal | script-mutable | The gain or loss on asset disposal |
| colour | integer (short) | importable script-mutable | Colour of record |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| type | string (3 chars) | importable script-mutable | Depreciation type, SL (straight line) or DV (diminishing value) |
| rate | float (double) | importable auto script-mutable | Depreciation rate |
| comment | string (255 chars) | importable script-mutable | Comments on asset |
| custom1 | string (255 chars) | importable script-mutable | For your own use |
| custom2 | string (255 chars) | importable script-mutable | For your own use |
| custom3 | string (255 chars) | importable script-mutable | For your own use |
| custom4 | string (255 chars) | importable script-mutable | For your own use |
| disposedaccdepn | decimal | script-mutable | |
| disposalaccdepnprivate | decimal | script-mutable | |
| initialdepn | float (double) | script-mutable |
assetcat
Categories and depreciation rates for fixed assets
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| code | string (7 chars) | indexed importable script-mutable | Unique code for the category |
| description | string (63 chars) | importable script-mutable | Description of category |
| assetaccount | string (13 chars) | importable script-mutable | The fixed asset account |
| depexpense | string (13 chars) | importable script-mutable | Depreciation expense account |
| accumdep | string (13 chars) | importable script-mutable | Accumulated depreciation account |
| gainloss | string (13 chars) | importable script-mutable | Account for gain/loss on asset disposal |
| custom | string (39 chars) | importable script-mutable | For your own use |
| group | string (7 chars) | importable script-mutable | For your own use |
| type | string (3 chars) | importable script-mutable | Default depreciation type, SL or DV |
| impairment | string (13 chars) | importable script-mutable | An expense account for asset impairment (when assets are revalued down) |
| rate | float (double) | importable script-mutable | Default depreciation rate |
| revalsurplus | string (13 chars) | importable script-mutable | An equity (shareholder funds) account for upwards revaluations |
| gainlossprivate | string (13 chars) | importable script-mutable | Account for private portion gain/loss on sale |
| depexpenseprivate | string (13 chars) | importable script-mutable | Account for private portion depreciation |
| usernum | float (double) | importable script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| lastdepreciateddate | date | importable unsigned script-mutable | Date of last depreciation run for category |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| comment | string (255 chars) | importable script-mutable | Comments on the category |
| dailydepreciation | integer (short) | importable script-mutable |
autosplit
Auto-coding rules for splitting transactions
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| matchfunction | string (255 chars) | importable | The matching text/function that involves the split |
| splitmode | integer (long) | importable | The type of split |
| splitacct1 | string (13 chars) | importable | The first split account |
| splitacct2 | string (13 chars) | importable | The second split account |
| splitamount1 | float (double) | importable | Percent or amount to allocate to first split account |
| splitamount2 | float (double) | importable | Second split amount to allocate |
| splitacct3 | string (13 chars) | importable | The third split account |
| splitacct4 | string (13 chars) | importable | The fourth split account |
| splitamount3 | float (double) | importable | Third split amount to allocate |
| matchname | string (11 chars) | importable | The name of the rule |
| priority | integer (short) | importable script-mutable | Priority of the rule |
memo
Interaction memo records belonging to Name records
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| memo.nameseq | integer (long) | indexed importable | |
| memo.order | integer (short) | importable | |
| memo.date | date | importable unsigned script-mutable | |
| memo.recalldate | date | indexed importable unsigned script-mutable | |
| memo.flags | integer (short) | ||
| memo.text | string (255 chars) | importable script-mutable |
user
Data storage for scripts
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| key | string (9 chars) | indexed importable | A unique key to identify the record. Use this in conjunction with a (pre-allocated) DevKey to ensure your own storage |
| data | string (245 chars) | importable script-mutable | text data for the key |
offledger
90 periods of balances for currency exchange rates or user-defined off-ledger data
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| kind | string (3 chars) | indexed importable | Kind of record: ‘CUR' is currency, ‘USR' is user created offledger record. |
| name | string (15 chars) | indexed importable | The ISO currency code for a currency, or user-defined name of offledger record |
| description | string (39 chars) | importable | Description of currency/offledger record |
| flags | integer (long) | ||
| linkedaccountu | string (13 chars) | importable | The currency unrealised gain general ledger account |
| linkedaccountr | string (13 chars) | importable | The currency realised gain general ledger account |
| preferredbankcr | string (7 chars) | importable | Preferred bank receipts account for currency |
| preferredbankcp | string (7 chars) | importable | Preferred bank payments account for currency |
| usernum | float (double) | importable script-mutable | |
| usertext | string (255 chars) | importable script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | importable script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| … plus per-period numeric series fields (balance, budget) covering ~90 past periods and ~18 future periods | |||
filter
User-defined quick access searches for filtering lists
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| file | integer (short) | ||
| tabset | integer (short) | ||
| tab | integer (short) | ||
| type | integer (short) | ||
| user | string (3 chars) | ||
| name | string (31 chars) | importable script-mutable | |
| filterfunction | string (255 chars) | importable script-mutable | |
| order | integer (short) | importable script-mutable |
stickies
Sticky notes for attaching to Transactions, Accounts, Names, Products, Jobs
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| filenum | integer (short) | indexed importable | |
| colour | integer (short) | importable | |
| user | string (3 chars) | importable | |
| ownerseq | integer (long) | indexed importable | |
| message | string (255 chars) | importable | |
| flags | integer (short) | importable |
lists
User-defined validation lists plus the standard Stock Locations validation list if location-tracking is enabled
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| listid | string (15 chars) | indexed importable | |
| item | string (15 chars) | indexed importable | |
| comment | string (67 chars) | importable script-mutable | |
| usernum | float (double) | importable script-mutable | |
| usertext | string (255 chars) | importable script-mutable | |
| taggedtext | string (255 chars) | importable script-mutable |
login
Usernames and User roles for access control to the database
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| initials | string (3 chars) | indexed importable | The user initials (or for role record, a unique numeric code preceded by a space) |
| name | string (31 chars) | importable | The name of the user or role |
| password | string (33 chars) | importable | Hash of the password |
| securitylevel | integer (short) | importable | The security level of the user |
| privileges | string (65 chars) | importable | Privilege map for user or role. Bitfield stored as a hex string |
| string (63 chars) | importable | Email of user. For a MoneyWorks Now user, this is the MoneyWorks Now username | |
| flags | integer (short) | importable | Bit flags |
| category | string (31 chars) | importable script-mutable | User-defined |
| role | string (3 chars) | importable | User Role. If specified for a user, links to a role login record from which the privileges ar obtained for the user |
| usernum | float (double) | importable script-mutable | |
| usertext | string (255 chars) | importable script-mutable | |
| taggedtext | string (255 chars) | importable script-mutable | |
| settingsdonor | string (3 chars) | importable script-mutable | |
| lastloginfailuretime | timestamp | importable script-mutable | |
| loginfailurecount | integer (long) | importable unsigned script-mutable |
user2
Data storage for scripts
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| devkey | integer (long) | indexed importable unsigned script-mutable | |
| key | string (27 chars) | indexed importable script-mutable | |
| int1 | integer (long) | importable script-mutable | |
| int2 | integer (long) | importable script-mutable | |
| float1 | float (double) | importable script-mutable | |
| float2 | float (double) | importable script-mutable | |
| date1 | date | importable unsigned script-mutable | |
| date2 | date | importable unsigned script-mutable | |
| text1 | string (255 chars) | importable script-mutable | |
| text2 | string (255 chars) | importable script-mutable | |
| text | string (1023 chars) | importable script-mutable | |
| int3 | integer (long) | importable script-mutable | |
| int4 | integer (long) | importable script-mutable | |
| float3 | float (double) | importable script-mutable | |
| float4 | float (double) | importable script-mutable | |
| date3 | date | importable unsigned script-mutable | |
| date4 | date | importable unsigned script-mutable | |
| text3 | string (255 chars) | importable script-mutable | |
| text4 | string (255 chars) | importable script-mutable | |
| taggedtext | string (255 chars) | importable script-mutable | |
| colour | integer (short) | importable script-mutable |