20.03. Field descriptions

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)
Agreggator	Stored metrics for use in reports

Foreign Keys

Get your Foreign Keys here.

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