The following tables list the main data 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)

Foreign Keys

Other tables

Table Description
Abbreviations Stores abbreviation codes and their expansion for dispensing directions
Account_code Stores account codes associated with each item
Authorisations Tables that store authorisers and pending authorisations
Backorders One record for Customer backorder.
Bill of Materials One record for each mSupply user
Contacts One record for each contact
Currency One record for each curency
Drug interactions Drug interactions and Drug interaction groups
Item Categories One record for item category
Labels Miscellaneous labels
Location Each record contains one shelf location.
Log Each record contains one event recorded by mSupply
Users One record for each mSupply user
Item Departments The departments that an item can be assigned to
Item Directions The default directions that are assigned to items
Item notes The notes that are attached to items
Item Warning Link A join table that stores which items have which warnings
Name category tables Tables that store the categories that can be assigned to names (customers, suppliers, donors, manufacturers etc.)
Prescribers One record for each prescriber
Regimen records, Regimen and Regimen Lines Everything to do with storing ARV regimen records
Reminders A reminder is an event with a due date- like a to-do
Stores Stores and join tables that control access per-store
Warnings The warnings which can be attached to items
Custom stock fields Holds the details of the values that are selectable in the 5th - 8th user fields when receiving stock

Tables used internally that you most likely shouldn't be interested in

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

Ship method

Field name Description Type Length Ind
id Text
method Alpha 80

Sync out

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. For example a remote sync site might have multiple stores. If this sync_out record if for a transact record, the Store_ID that it's for.
Can be blank
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

Transaction category

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

Transaction Notes

Field name Description Type Length Ind
id Text
note Text

Units

Field name Description Type Length Ind
id Text
units Alpha 60
comment Text

Previous: Asset Management for East Timor Next: Backup

  • admin/tables_fields.txt
  • Last modified: 2018/06/06 03:14
  • by craig