Field descriptions [mSupply documentation wiki]

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)

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
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
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	Type	Length
id	Text
units	Alpha	60
comment	Text

Previous: Asset Management for East Timor Next: Backup