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 are 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 | The account code. Codes should ideally be numbered hierarchically for easy summarisation |
| type | char (2) | indexed | 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 | 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 | 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) | script-mutable | Description shown in transaction entry and reports |
| pandl | string (7 chars) | indexed | 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) | cond-mutable | The tax code for the account, related to taxrate.taxcode |
| flags | integer (short) | ||
| system | char (2) | indexed | 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 | unsigned | The date/time the account was created. |
| category2 | string (15 chars) | script-mutable | User defined, for analysis |
| category3 | string (15 chars) | script-mutable | User defined, for analysis |
| category4 | string (15 chars) | script-mutable | User defined, for analysis |
| accountantcode | string (9 chars) | script-mutable | Code in accountant's chart that corresponds to this account. |
| colour | integer (short) | 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) | The ISO currency code (empty for local currency accounts). Used only for bank and AR/AP accounts | |
| securitylevel | integer (short) | indexed | 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) | script-mutable | For bank accounts, the number of the bank account. |
| balancelimit | decimal | ||
| manualchequenumber | string (11 chars) | Next manual cheque number in sequence (for bank accounts) | |
| printedchequenumber | string (11 chars) | Next batch cheque number in sequence (for bank accounts) | |
| laststatementimport | timestamp | unsigned | Last bank statement import cut-off date |
| comments | string (1023 chars) | script-mutable | User notes on the account |
| manualchequenumdigits | integer (byte) | ||
| printedchequenumdigits | integer (byte) | ||
| usernum | float (double) | script-mutable | General storage for use by scripts |
| usertext | string (255 chars) | script-mutable | General storage for use by scripts |
| taggedtext | string (255 chars) | script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| feedid | string (31 chars) | script-mutable | |
| cashflow | string (7 chars) | script-mutable | |
| cashforecast | string (31 chars) | script-mutable | |
| ebitda | string (1 chars) | 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) |
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) | ||
| department | string (5 chars) | indexed | |
| category | string (7 chars) | indexed | |
| classification | string (5 chars) | indexed | |
| type | char (2) | indexed | |
| balance | decimal | ||
| budgeta | integer (long) | ||
| budgetb | integer (long) | ||
| concat | string (13 chars) | indexed | |
| system | char (2) | indexed | |
| usernum | float (double) | script-mutable | |
| usertext | string (255 chars) | script-mutable | |
| taggedtext | string (255 chars) | 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 | 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) | ||
| date | date | unsigned | |
| long | integer (long) |
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 | |
| description | string (35 chars) | script-mutable | |
| classification | string (5 chars) | 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) | script-mutable | |
| custom2 | string (9 chars) | script-mutable | |
| flags | integer (short) | ||
| usernum | float (double) | script-mutable | General storage for use by scripts |
| usertext | string (255 chars) | script-mutable | General storage for use by scripts |
| taggedtext | string (255 chars) | 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 | |
| group | string (5 chars) | indexed |
transaction
Accounting transaction and orders
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | indexedunsigned | The sequence number of the transaction. |
| lastmodifiedtime | timestamp | unsigned | The time that the record was last written in the database |
| ourref | string (11 chars) | indexedimportablework-it-outcond-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 | indexedimportablework-it-outunsignedcond-mutable | The transaction date. |
| enterdate | date | indexedunsigned | The date on which the transaction was entered. |
| duedate | date | importablework-it-outunsignedcond-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) | indexedimportablework-it-out | The transaction type.--see following table. |
| theirref | string (31 chars) | importablescript-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) | indexedimportable | Customer or Supplier Code, related to Name.Code |
| flag | string (5 chars) | importablescript-mutable | Bit flags. Constants are defined with fTrans_ prefix |
| description | string (1023 chars) | importablecond-mutable | The description of the transaction. |
| gross | decimal | importablework-it-out | The gross value of the transaction. |
| analysis | string (9 chars) | importablescript-mutable | User-defined analysis |
| contra | string (7 chars) | importablework-it-out | 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) | importablework-it-outcond-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 | importablework-it-outcond-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) | importablecond-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) | importablecond-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) | importablescript-mutable | User defined |
| user2 | string (255 chars) | importablescript-mutable | User defined |
| user3 | string (255 chars) | importablescript-mutable | User defined |
| promptpaymentdate | date | importablework-it-outunsignedscript-mutable | The date the prompt payment discount expires |
| promptpaymentamt | decimal | importablework-it-out | The amount of the eligible prompt payment discount |
| prodpricecode | string (1 chars) | importablework-it-out | Pricing code (A-F) |
| mailingaddress | string (255 chars) | importablecond-mutable | Transaction's mailing address. Blank if default from name. |
| deliveryaddress | string (255 chars) | importablecond-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) | importablescript-mutable | Details of freight for order |
| specialbank | string (31 chars) | importablescript-mutable | For receipts, the bank number of the cheque |
| specialbranch | string (31 chars) | importablescript-mutable | For receipts, the branch of the cheque |
| specialaccount | string (31 chars) | importablescript-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) | importablescript-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importablescript-mutable | User-defined for use by scripts |
| user4 | string (15 chars) | importablescript-mutable | User defined |
| user5 | string (15 chars) | importablescript-mutable | User defined |
| user6 | string (15 chars) | importablescript-mutable | User defined |
| user7 | string (15 chars) | importablescript-mutable | User defined |
| user8 | string (15 chars) | importablescript-mutable | User defined |
| taggedtext | string (255 chars) | importablescript-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) | indexedwork-it-out | The sequence number of the parent transaction, related to transaction.sequencenumber |
| detail.sort | integer (short) | work-it-out | The order of the detail lines as displayed in a transaction |
| detail.account | string (13 chars) | indexed | 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) | work-it-out | 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) | work-it-out | The tax code of the account. Related to Taxrate.TaxCode |
| detail.gross | decimal | The gross value of the detail line. | |
| detail.tax | decimal | 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) | work-it-out | The description for the detail line |
| detail.stockqty | float (double) | 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) | indexedwork-it-out | 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) | work-it-out | 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) | 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) | indexedwork-it-out | 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 | Related to Job.Code. Use for ascribing line items to job income or costs |
| detail.saleunit | string (5 chars) | For product transaction detail lines, this is the selling unit of measure as copied from the product record. | |
| detail.discount | float (double) | The percent discount for the line | |
| detail.flags | integer (short) | Bit-flags. Constants are defined with fDetail_ prefix | |
| detail.orderqty | float (double) | 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) | 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) | 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 | 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) | script-mutable | User-defined for use by scripts |
| detail.usertext | string (255 chars) | script-mutable | User-defined for use by scripts |
| detail.taggedtext | string (255 chars) | work-it-outscript-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| detail.noninvrcvdnotinvoicedqty | float (double) | work-it-out | The quantity of non-inventoried items on an order received but not invoiced |
| detail.custom1 | string (31 chars) | work-it-outscript-mutable | User-defined for use by scripts |
| detail.custom2 | string (31 chars) | 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) | ||
| who | string (3 chars) | ||
| info1 | string (15 chars) | ||
| info2 | string (15 chars) | ||
| info3 | string (15 chars) |
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) | The tax code | |
| paidaccount | string (7 chars) | The control account for GST paid out under this rate | |
| recaccount | string (7 chars) | The control account for GST received out under this rate | |
| rate1 | float (double) | Rate used before changeover date | |
| date | date | unsigned | Tax rate changeover date |
| rate2 | float (double) | Rate used on or after changeover date | |
| combine | integer (short) | Flags (how 2nd tier tax is combined) | |
| combinerate1 | float (double) | 2nd tier rate used before changeover date (PST) | |
| combinerate2 | float (double) | 2nd tier rate used after changeover date (PST) | |
| gstreceived | decimal | Total GST received for taxcode in last GST finalisation | |
| netreceived | decimal | Net received for taxcode in last GST finalisation | |
| gstpaid | decimal | Total GST paid for taxcode in last GST finalisation | |
| netpaid | decimal | Net paid for taxcode in last GST finalisation | |
| ratename | string (59 chars) | ||
| reportcyclestart | integer (short) | ||
| reportcycleend | integer (short) | ||
| reportdate | date | unsigned | |
| pstreceived | decimal | ||
| pstpaid | decimal | ||
| type | integer (short) | ||
| combination | string (31 chars) | ||
| usernum | float (double) | script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| aliascode | string (5 chars) | script-mutable | |
| aliascountry | string (3 chars) | script-mutable | |
| reversedrate1 | float (double) | ||
| reversedrate2 | float (double) |
message
Periodic reminder messages and recurring transaction time definitions
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| startdate | date | unsignedscript-mutable | |
| enddate | date | unsignedscript-mutable | |
| nextdate | date | indexedunsignedscript-mutable | |
| keep | boolean | ||
| ref | integer (long) | ||
| lastday | integer (byte) | ||
| ndaily | integer (byte) | ||
| nweekly | integer (byte) | ||
| nmonthly | integer (byte) | ||
| once | integer (byte) | ||
| xtimes | integer (byte) | ||
| forever | integer (byte) | ||
| day | integer (byte) | ||
| type | integer (short) | ||
| dayofweek | integer (byte) | ||
| n | integer (byte) | ||
| x | integer (byte) | ||
| avoidweekends | integer (byte) | ||
| reverse | integer (byte) | ||
| kill_next_time | integer (byte) | ||
| message | string (255 chars) | script-mutable | |
| user | string (3 chars) | ||
| usernum | float (double) | script-mutable | |
| usertext | string (255 chars) | script-mutable | |
| taggedtext | string (255 chars) | 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) | indexedimportablework-it-out | The name code. For non-sundries, only the first ten characters are used. |
| name | string (255 chars) | importablescript-mutable | Name of company |
| contact | string (39 chars) | importablescript-mutable | Contact person 1 in the company |
| position | string (39 chars) | importablescript-mutable | Position of contact person 1 |
| address1 | string (59 chars) | importablescript-mutable | Mailing Address (first line) |
| address2 | string (59 chars) | importablescript-mutable | Mailing Address (second line) |
| address3 | string (59 chars) | importablescript-mutable | Mailing Address (third line) |
| address4 | string (59 chars) | importablescript-mutable | Mailing Address (fourth line) |
| delivery1 | string (59 chars) | importablescript-mutable | Delivery address (first line) |
| delivery2 | string (59 chars) | importablescript-mutable | Delivery address (second line) |
| delivery3 | string (59 chars) | importablescript-mutable | Delivery address (third line) |
| delivery4 | string (59 chars) | importablescript-mutable | Delivery address (fourth line) |
| phone | string (19 chars) | importablescript-mutable | Phone number |
| fax | string (19 chars) | importablescript-mutable | Facsimile number |
| category1 | string (15 chars) | importablescript-mutable | User defined |
| category2 | string (15 chars) | importablescript-mutable | User defined |
| category3 | string (15 chars) | importablescript-mutable | User defined |
| category4 | string (15 chars) | importablescript-mutable | User defined |
| customertype | integer (short) | indexedimportable | 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) | importablescript-mutable | The customer's bank (e.g. BNZ) |
| accountname | string (63 chars) | importablescript-mutable | The bank account name (e.g. XYZ Trading Company) |
| bankbranch | string (21 chars) | importablescript-mutable | The bank branch (e.g. Main St.) |
| theirref | string (15 chars) | importablescript-mutable | The reference code by which the supplier or customer refers to your company. |
| hold | boolean | importablescript-mutable | True if the debtor is on hold ("False" otherwise) |
| recaccount | string (7 chars) | importablework-it-out | The Accounts Receivable control account code for a debtor. |
| payaccount | string (7 chars) | importablework-it-out | 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) | importablescript-mutable | The credit limit for a debtor |
| discount | decimal | importablescript-mutable | Discount field for a customer |
| comment | string (1023 chars) | importablescript-mutable | A comment |
| suppliertype | integer (short) | indexedimportable | 0 for not a supplier, 1 for supplier, 2 for creditor |
| colour | integer (short) | importablecond-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) | importablescript-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) | importablescript-mutable | Post code |
| state | string (7 chars) | importablescript-mutable | State (for postal address) |
| bankaccountnumber | string (23 chars) | importablescript-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) | importablecond-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) | importablescript-mutable | Direct dial number for contact 1 |
| string (139 chars) | importablescript-mutable | email address for contact 1 | |
| mobile | string (19 chars) | importablescript-mutable | Mobile phone number for contact 1 |
| afterhours | string (19 chars) | importablescript-mutable | After hours phone number for contact 1 |
| contact2 | string (39 chars) | importablescript-mutable | Name of contact person 2 |
| position2 | string (39 chars) | importablescript-mutable | Position of contact 2 |
| ddi2 | string (19 chars) | importablescript-mutable | Direct dial number for contact 2 |
| email2 | string (139 chars) | importablescript-mutable | email address for contact 2 |
| mobile2 | string (19 chars) | importablescript-mutable | Mobile phone number for contact 2 |
| afterhours2 | string (19 chars) | importablescript-mutable | After hours phone number for contact 2 |
| weburl | string (63 chars) | importablescript-mutable | Web URL |
| productpricing | string (1 chars) | importablecond-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) | importablescript-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importablescript-mutable | User-defined for use by scripts |
| custpromptpaymentterms | integer (short) | importablecond-mutable | 0 for no prompt payment; > 0 for within N days; < 0 for by Nth date of following month |
| custpromptpaymentdiscount | float | importablecond-mutable | |
| supppromptpaymentterms | integer (short) | importablecond-mutable | |
| supppromptpaymentdiscount | float | importablecond-mutable | Percentage amount of prompt payment discount offered by supplier |
| lastpaymentmethod | integer (short) | PaymentMethod used in previous transaction | |
| creditcardnum | string (19 chars) | importablescript-mutable | Credit card number |
| creditcardexpiry | string (5 chars) | importablescript-mutable | Expiry date of credit card |
| creditcardname | string (63 chars) | importablescript-mutable | Name on credit card |
| taxnumber | string (31 chars) | importablescript-mutable | Their tax number (GST#, VAT#, ABN etc, depending on country) |
| custom1 | string (255 chars) | importablescript-mutable | User-defined |
| custom2 | string (255 chars) | importablescript-mutable | User-defined |
| custom3 | string (15 chars) | importablescript-mutable | User-defined |
| custom4 | string (15 chars) | importablescript-mutable | User-defined |
| deliverypostcode | string (11 chars) | importablescript-mutable | Postcode/zipcode of delivery address |
| deliverystate | string (7 chars) | importablescript-mutable | Sate of delivery address |
| addresscountry | string (59 chars) | importablescript-mutable | |
| deliverycountry | string (59 chars) | importablescript-mutable | |
| receiptmethod | integer (short) | importablecond-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) | importablescript-mutable | |
| flags | integer (short) | See Names Flags table below | |
| salutation | string (39 chars) | importablescript-mutable | Salutation for contact 1 |
| salutation2 | string (39 chars) | importablescript-mutable | Salutation for contact 2 |
| memo | string (255 chars) | importablescript-mutable | Memo/notes for contact 1 |
| memo2 | string (255 chars) | importablescript-mutable | Memo/notes for contact 2 |
| role | integer (short) | importablescript-mutable | Roles for contact 1. This is a bit mapped field, with each bit representing a role. |
| role2 | integer (short) | importablescript-mutable | Roles for contact 2. This is a bit mapped field, with each bit representing a role. |
| custom5 | string (15 chars) | importablescript-mutable | User-defined |
| custom6 | string (15 chars) | importablescript-mutable | User-defined |
| custom7 | string (15 chars) | importablescript-mutable | User-defined |
| custom8 | string (15 chars) | importablescript-mutable | User-defined |
| taggedtext | string (255 chars) | importablescript-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| einvoicingid | string (31 chars) | importablescript-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) | indexedimportablework-it-out | 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 | importablework-it-out | 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) | indexedscript-mutable | Sequencenumber of the Name record for the contact. Related to Name.sequencenumber |
| order | integer (short) | script-mutable | The order of the contact |
| role | integer (short) | script-mutable | Roles for the contact. This is a bit field, with each bit representing a role |
| contact | string (39 chars) | script-mutable | Contact's name |
| position | string (39 chars) | script-mutable | Contact's position e.g. "CEO" |
| salutation | string (39 chars) | script-mutable | Contact's salutation |
| ddi | string (19 chars) | script-mutable | Contact's direct dial |
| string (139 chars) | script-mutable | Contact's email address | |
| mobile | string (19 chars) | script-mutable | Contact's mobile number |
| afterhours | string (19 chars) | script-mutable | Contact's after hours number |
| memo | string (255 chars) | script-mutable | Memo/notes on contact |
| usernum | float (double) | script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | 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) | indexedimportable | The product code. |
| supplierscode | string (39 chars) | importablescript-mutable | The product code that your usual supplier uses to refer to the product. |
| supplier | string (11 chars) | importablecond-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) | importablescript-mutable | The name of the product. |
| comment | string (1023 chars) | importablescript-mutable | Any additional information about the product. |
| category1 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category2 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category3 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category4 | string (15 chars) | importablescript-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) | importablescript-mutable | User-defined for use by scripts |
| sellunit | string (5 chars) | importablescript-mutable | The units in which you sell the product. e.g. "ea", "kg", "ml", "doz" |
| sellprice | float (double) | importablecond-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 | importablecond-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 | importablecond-mutable | The weight/volume of the buy unit of the item |
| buyunit | string (5 chars) | importablescript-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) | importablecond-mutable | The percentage discount. This is used only if the discount mode is 1 or 3 |
| selldiscountmode | integer (short) | importablecond-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) | importablescript-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) | importablescript-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) | importablescript-mutable | See Product Flags table below |
| colour | integer (short) | importablecond-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) | importablecond-mutable | The B sell price (before GST and discounts) |
| sellpricec | float (double) | importablecond-mutable | The C sell price (before GST and discounts) |
| sellpriced | float (double) | importablecond-mutable | The D sell price (before GST and discounts) |
| sellpricee | float (double) | importablecond-mutable | The E sell price (before GST and discounts) |
| sellpricef | float (double) | importablecond-mutable | The F sell price (before GST and discounts) |
| qtybreak1 | float | importablecond-mutable | Quantity at which the first break price is used |
| qtybreak2 | float | importablecond-mutable | Quantity at which the second break price is used |
| qtybreak3 | float | importablecond-mutable | Quantity at which the third break price is used |
| qtybreak4 | float | importablecond-mutable | Quantity at which the fourth break price is used |
| qtybrksellpricea1 | float (double) | importablecond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak1 |
| qtybrksellpricea2 | float (double) | importablecond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak2 |
| qtybrksellpricea3 | float (double) | importablecond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak3 |
| qtybrksellpricea4 | float (double) | importablecond-mutable | The A sell price, if the quantity is greater than or equal to QtyBreak4 |
| qtybrksellpriceb1 | float (double) | importablecond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak1 |
| qtybrksellpriceb2 | float (double) | importablecond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak2 |
| qtybrksellpriceb3 | float (double) | importablecond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak3 |
| qtybrksellpriceb4 | float (double) | importablecond-mutable | The B sell price, if the quantity is greater than or equal to QtyBreak4 |
| type | string (1 chars) | indexedimportablecond-mutable | P = product; R = resource; T = time; S = ship method; O=other. |
| count | float (double) | importablecond-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) | importablescript-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) | indexedimportablescript-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) | importablecond-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) | importablescript-mutable | For your own use |
| custom2 | string (255 chars) | importablescript-mutable | For your own use |
| custom3 | string (15 chars) | importablescript-mutable | For your own use |
| custom4 | string (15 chars) | importablescript-mutable | For your own use |
| buytaxcodeoverride | string (5 chars) | importablecond-mutable | |
| selltaxcodeoverride | string (5 chars) | importablecond-mutable | |
| leadtimedays | integer (short) | importablescript-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 | importablecond-mutable | The weight/volume of one sell unit |
| custom5 | string (15 chars) | importablescript-mutable | For your own use |
| custom6 | string (15 chars) | importablescript-mutable | For your own use |
| custom7 | string (15 chars) | importablescript-mutable | For your own use |
| custom8 | string (15 chars) | importablescript-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 | The sequencenumber of the product master record for the item |
| location | string (15 chars) | The location | |
| identifier | string (31 chars) | The serial/batch number of the item | |
| expiry | date | unsigned | The expiry date for expiring batches |
| qty | float (double) | 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) | 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) | 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) | indexedimportable | The job code. |
| description | string (255 chars) | importablescript-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) | importablescript-mutable | Comments on the job |
| startdate | date | importableunsignedscript-mutable | The start date of the job |
| markup | float (double) | importablescript-mutable | The percent markup applied to items used on the job. |
| quote | decimal | importablescript-mutable | The amount quoted for the job. |
| billed | decimal | The amount billed to date for the job. | |
| status | char (2) | indexedimportablecond-mutable | The status of the job. "QU" for quoted, "OP" for active, "CO" for complete |
| flags | integer (short) | ||
| colour | integer (short) | importablecond-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) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category2 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category3 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| category4 | string (15 chars) | importablescript-mutable | Any value. This can be used for analysis purposes. |
| ordernum | string (31 chars) | importablescript-mutable | The client's order number for the job |
| contact | string (63 chars) | importablescript-mutable | The contact for the job |
| phone | string (19 chars) | importablescript-mutable | The contact's phone number |
| enddate | date | importableunsignedscript-mutable | The expected end date |
| manager | string (3 chars) | importablescript-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) | importablescript-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importablescript-mutable | User-defined for use by scripts |
| project | string (9 chars) | importable | Job code of project to which this belongs |
| targetdate | date | importableunsignedscript-mutable | Target date for job |
| custom1 | string (255 chars) | importablescript-mutable | For your own use |
| custom2 | string (255 chars) | importablescript-mutable | For your own use |
| custom3 | string (15 chars) | importablescript-mutable | For your own use |
| custom4 | string (15 chars) | importablescript-mutable | For your own use |
| taggedtext | string (255 chars) | importablescript-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| custom5 | string (15 chars) | importablescript-mutable | For your own use |
| custom6 | string (15 chars) | importablescript-mutable | For your own use |
| custom7 | string (15 chars) | importablescript-mutable | For your own use |
| custom8 | string (15 chars) | importablescript-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) | script-mutable | Sequencenumber of asset |
| action | string (3 chars) | 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 | script-mutable | |
| qty | float (double) | script-mutable | Quantity |
| depreciation | decimal | script-mutable | Depreciation due to action |
| adjustment1 | decimal | script-mutable | Adjustments |
| adjustment2 | decimal | script-mutable | Adjustments |
| rate | float (double) | script-mutable | Depreciation rate used |
| privateusepercent | float | script-mutable | Private use percent |
| accumdepreciation | decimal | script-mutable | Accumulated depreciation after action |
| accumreval | decimal | script-mutable | Accumulated revaluation after action |
| closingvalue | decimal | script-mutable | Book value after action |
| transactionseq | integer (long) | Sequencenumber of transaction associated with action | |
| memo | string (255 chars) | script-mutable | User memo |
| disposedaccdepn | decimal | script-mutable | |
| gainlossondisposal | decimal | script-mutable | |
| gainlossondisposalprivate | decimal | script-mutable | |
| disposalaccdepnprivate | decimal | script-mutable |
build
BOM definition for manufactured products
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| build.productseq | integer (long) | indexed | The sequence number of the Product record to which the recipe belongs |
| build.order | integer (short) | ||
| build.qty | float (double) | Quantity of component required | |
| build.partcode | string (31 chars) | Code of component | |
| build.flags | integer (short) | ||
| build.memo | string (255 chars) | 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) | indexedimportable | The job code. |
| qty | float (double) | importablecond-mutable | The quantity of the resource used |
| resource | string (31 chars) | indexedimportable | The code of the resource |
| date | date | importablework-it-outunsignedcond-mutable | The date the resource was used |
| costcentre | string (5 chars) | importablecond-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) | importablework-it-outcond-mutable | The units of resource used. |
| costprice | decimal | importablework-it-outcond-mutable | The cost price of the resource used. |
| sellprice | decimal | importablework-it-outcond-mutable | The estimated sell value of the resource used, based on the job markup. |
| memo | string (1023 chars) | importablescript-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) | importablecond-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) | indexedimportablecond-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) | importablework-it-outscript-mutable | The type of entry. "IN" for income, "EX" for expense. |
| analysis | string (9 chars) | importablecond-mutable | The analysis field |
| billvalue | decimal | importable | |
| activitycode | string (31 chars) | importablecond-mutable | A free-form activity code |
| comments | string (255 chars) | importablescript-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) | importablescript-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importablescript-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | importablescript-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) | indexedunsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| account | string (13 chars) | ||
| opening | decimal | ||
| closing | decimal | ||
| statement | integer (short) | ||
| date | date | unsigned | |
| reconciledtime | timestamp | unsigned | |
| discrepancy | decimal |
asset
Fixed Assets that need to have depreciation tracked
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| code | string (19 chars) | indexedimportablescript-mutable | A unique code for the asset |
| description | string (63 chars) | importablescript-mutable | The description of the asset |
| category | string (7 chars) | importablescript-mutable | The Asset Category of the asset |
| serialnum | string (31 chars) | importablescript-mutable | A serial number for the asset |
| qty | float (double) | importablescript-mutable | The quantity of the asset (normally 1, but might be a set of similar assets such as desks) |
| expectedlife | integer (long) | importablescript-mutable | The expected life in years of the asset |
| cost | decimal | importablescript-mutable | The cost (per unit) of the asset |
| accumdepreciation | decimal | importablescript-mutable | The accumulated depreciation recorded against the asset |
| acquisitiondate | date | importableunsignedscript-mutable | Date on which the asset was acquired |
| lastdepreciateddate | date | importableunsignedscript-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) | importablescript-mutable | Where the asset is kept |
| dept | string (5 chars) | importablescript-mutable | The asset department (must be a MoneyWorks Department) |
| privateusepercent | float | importablescript-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 | unsignedscript-mutable | Date of last revaluation (blank if none) |
| expectedresidualvalue | decimal | importablescript-mutable | Expected residual value |
| revalsurplusimpairamt | decimal | script-mutable | Total revalued amount (positive if surplus) |
| usernum | float (double) | importablescript-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | importablescript-mutable | User-defined for use by scripts |
| accumdepnadj | decimal | script-mutable | Total adjustments to accumulated depreciation due to revaluations |
| bookvalue | decimal | importablescript-mutable | The current book value |
| disposaldate | date | unsignedscript-mutable | Date last disposed |
| gainlossondisposal | decimal | script-mutable | The gain or loss on asset disposal |
| colour | integer (short) | importablescript-mutable | Colour of record |
| taggedtext | string (255 chars) | importablescript-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| type | string (3 chars) | importablescript-mutable | Depreciation type, SL (straight line) or DV (diminishing value) |
| rate | float (double) | importablework-it-outscript-mutable | Depreciation rate |
| comment | string (255 chars) | importablescript-mutable | Comments on asset |
| custom1 | string (255 chars) | importablescript-mutable | For your own use |
| custom2 | string (255 chars) | importablescript-mutable | For your own use |
| custom3 | string (255 chars) | importablescript-mutable | For your own use |
| custom4 | string (255 chars) | importablescript-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) | indexedscript-mutable | Unique code for the category |
| description | string (63 chars) | script-mutable | Description of category |
| assetaccount | string (13 chars) | script-mutable | The fixed asset account |
| depexpense | string (13 chars) | script-mutable | Depreciation expense account |
| accumdep | string (13 chars) | script-mutable | Accumulated depreciation account |
| gainloss | string (13 chars) | script-mutable | Account for gain/loss on asset disposal |
| custom | string (39 chars) | script-mutable | For your own use |
| group | string (7 chars) | script-mutable | For your own use |
| type | string (3 chars) | script-mutable | Default depreciation type, SL or DV |
| impairment | string (13 chars) | script-mutable | An expense account for asset impairment (when assets are revalued down) |
| rate | float (double) | script-mutable | Default depreciation rate |
| revalsurplus | string (13 chars) | script-mutable | An equity (shareholder funds) account for upwards revaluations |
| gainlossprivate | string (13 chars) | script-mutable | Account for private portion gain/loss on sale |
| depexpenseprivate | string (13 chars) | script-mutable | Account for private portion depreciation |
| usernum | float (double) | script-mutable | User-defined for use by scripts |
| usertext | string (255 chars) | script-mutable | User-defined for use by scripts |
| lastdepreciateddate | date | unsignedscript-mutable | Date of last depreciation run for category |
| taggedtext | string (255 chars) | script-mutable | Tagged data for use by user scripts, formatted as _tag: value; using the SetTaggedValue() function |
| comment | string (255 chars) | script-mutable | Comments on the category |
| dailydepreciation | integer (short) | 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) | The matching text/function that involves the split | |
| splitmode | integer (long) | The type of split | |
| splitacct1 | string (13 chars) | The first split account | |
| splitacct2 | string (13 chars) | The second split account | |
| splitamount1 | float (double) | Percent or amount to allocate to first split account | |
| splitamount2 | float (double) | Second split amount to allocate | |
| splitacct3 | string (13 chars) | The third split account | |
| splitacct4 | string (13 chars) | The fourth split account | |
| splitamount3 | float (double) | Third split amount to allocate | |
| matchname | string (11 chars) | The name of the rule | |
| priority | integer (short) | 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 | |
| memo.order | integer (short) | ||
| memo.date | date | unsignedscript-mutable | |
| memo.recalldate | date | indexedunsignedscript-mutable | |
| memo.flags | integer (short) | ||
| memo.text | string (255 chars) | 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 | 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) | 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 | Kind of record: ‘CUR' is currency, ‘USR' is user created offledger record. |
| name | string (15 chars) | indexed | The ISO currency code for a currency, or user-defined name of offledger record |
| description | string (39 chars) | Description of currency/offledger record | |
| flags | integer (long) | ||
| linkedaccountu | string (13 chars) | The currency unrealised gain general ledger account | |
| linkedaccountr | string (13 chars) | The currency realised gain general ledger account | |
| preferredbankcr | string (7 chars) | Preferred bank receipts account for currency | |
| preferredbankcp | string (7 chars) | Preferred bank payments account for currency | |
| usernum | float (double) | script-mutable | |
| usertext | string (255 chars) | script-mutable | User-defined for use by scripts |
| taggedtext | string (255 chars) | 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) | script-mutable | |
| filterfunction | string (255 chars) | script-mutable | |
| order | integer (short) | 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 | |
| colour | integer (short) | ||
| user | string (3 chars) | ||
| ownerseq | integer (long) | indexed | |
| message | string (255 chars) | ||
| flags | integer (short) |
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 | |
| item | string (15 chars) | indexed | |
| comment | string (67 chars) | script-mutable | |
| usernum | float (double) | script-mutable | |
| usertext | string (255 chars) | script-mutable | |
| taggedtext | string (255 chars) | 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 | The user initials (or for role record, a unique numeric code preceded by a space) |
| name | string (31 chars) | The name of the user or role | |
| password | string (33 chars) | Hash of the password | |
| securitylevel | integer (short) | The security level of the user | |
| privileges | string (65 chars) | Privilege map for user or role. Bitfield stored as a hex string | |
| string (63 chars) | Email of user. For a MoneyWorks Now user, this is the MoneyWorks Now username | ||
| flags | integer (short) | Bit flags | |
| category | string (31 chars) | script-mutable | User-defined |
| role | string (3 chars) | 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) | script-mutable | |
| usertext | string (255 chars) | script-mutable | |
| taggedtext | string (255 chars) | script-mutable | |
| settingsdonor | string (3 chars) | script-mutable | |
| lastloginfailuretime | timestamp | script-mutable | |
| loginfailurecount | integer (long) | unsignedscript-mutable |
user2
Data storage for scripts
| Field | Type | Properties | Notes |
|---|---|---|---|
| sequencenumber | integer (long) | unsigned | |
| lastmodifiedtime | timestamp | unsigned | |
| devkey | integer (long) | indexedunsignedscript-mutable | |
| key | string (27 chars) | indexedscript-mutable | |
| int1 | integer (long) | script-mutable | |
| int2 | integer (long) | script-mutable | |
| float1 | float (double) | script-mutable | |
| float2 | float (double) | script-mutable | |
| date1 | date | unsignedscript-mutable | |
| date2 | date | unsignedscript-mutable | |
| text1 | string (255 chars) | script-mutable | |
| text2 | string (255 chars) | script-mutable | |
| text | string (1023 chars) | script-mutable | |
| int3 | integer (long) | script-mutable | |
| int4 | integer (long) | script-mutable | |
| float3 | float (double) | script-mutable | |
| float4 | float (double) | script-mutable | |
| date3 | date | unsignedscript-mutable | |
| date4 | date | unsignedscript-mutable | |
| text3 | string (255 chars) | script-mutable | |
| text4 | string (255 chars) | script-mutable | |
| taggedtext | string (255 chars) | script-mutable | |
| colour | integer (short) | script-mutable |