This is an old revision of the document!
*
The following tables list the main tables in mSupply (There are some others, but we're not telling you about those!). This information will help you if you are building your own reports.
Note that if a field is indexed, finding information (querying) using that field will be very quick.
Main tables
Table | Description |
Transactions | A transaction record is stored for each invoice |
Transaction lines | one transaction line record is created for each line on an invoice |
Items | An item record is created for each product or service purchased or supplied |
item_line | Each batch of stock at a particular location has one item_line record |
Names | Each patient, customer or supplier has one name record |
Purchase Orders | One record is created for each purchase order to a supplier |
Purchase Order Lines | Each record is an order for one item on a purchase order |
Quotes and Quote Lines | Quotations submitted by suppliers |
Tenders and Tender Lines | Tenders created to invite quote submissions from suppliers |
Requistions | Tables that store customer submitted stock and order data |
Stock take and Stock take lines | Stock takes and their related records |
Store | Information about a particular virtual store |
Drug_Register | Drug Registration records (for use in the Drug Registration module) |
Table | Description |
Number and Number re-use | Stores serial numbers such as invoice numbers |
Preferences | The data for each preference is in binary format. You're not interested. |
Item store join |
Field name | Description | Type | Length | Ind |
Note that records are created for each item- there should always be one record for each item in each store |
id | | Text | | |
store_ID | | Text | | |
item_ID | | Text | | |
inactive | true means item is not visible in this store. False means it is visible | bool | | |
Name store join |
Field name | Description | Type | Length | Ind |
Note that records are only created as needed- that is a missing record implies the name is invisible for that store |
id | | Text | | |
store_ID | | Text | | |
name_ID | | Text | | |
inactive | true means name is not visible in this store. False means it is visible | bool | | |
Name group |
Field name | Description | Type | Length | Ind |
id | | Text | | |
name | | Alpha | 40 | |
Name note |
Field name | Description | Type | Length | Ind |
note_type | | Alpha | 3 | |
entry_date | | Date | | |
note | | Text | | |
name_key | | Alpha | 8 | |
id | | Text | | |
Permissions |
Field name | Description | Type | Length | Ind |
item_dept_id | | Text | | |
name_group_id | Stores which name groups have permission to be supplied which item departments | Text | | |
Prefs |
Field name | Description | Type | Length | Ind |
item | Internally used: storing preferences of text type(Now deprecated in favour of pref blob) | Alpha | 20 | |
value | | Alpha | 60 | |
Reports |
Field name | Description | Type | Length | Ind |
report_name | | Alpha | 80 | |
report_blob | | BLOB | | |
owner_ID | | Text | | |
id | | Text | | |
last_updated | | Date | | |
Field name | Description | Type | Length | Ind |
id | | Text | | |
method | | Alpha | 80 | |
Field name | Description | Type | Length | Ind |
id | | Text | | |
type | I=insert, U=update, D=delete, M=merge, T=transfer | Alpha | 2 | |
record_id | ID of the record to be synced | Text | | |
to_from_id | sync site ID to send the record | Long | | |
store_id | if store-specific data, the store ID for the record; blank → send to all sites | Text | | |
record_data | (deprecated) | BLOB | | |
table_num | Internal table number of the record | Long | | |
sequence | Integer for correct sync ordering | Long | | |
table_id_num | Internal field number for the ID field of the record's table | Long | | |
merge_ID_to_keep | ID of record being kept in merge | Text | | |
merge_ID_to_delete | ID of record being deleted in merge | Text | | |
Field name | Description | Type | Length | Ind |
id | | Text | | |
category | | Alpha | 80 | |
type | “ci” for customer invoices
“pi” for prescriptions
“cc” for customer credits
“si” for supplier invoices
“sc” for supplier credits
“sr for repacks
“bu” for builds
“in” for inventory adjustments | Alpha | 3 | |
Field name | Description | Type | Length | Ind |
id | | Text | | |
note | | Text | | |