admin:tables_fields

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revision Previous revision
Next revision
Previous revision
admin:tables_fields [2019/09/04 13:33] louadmin:tables_fields [2021/10/08 11:15] (current) Gary Willetts
Line 1: Line 1:
-{{indexmenu_n>30}} +{{indexmenu_n>199}} 
- +====== 25.22. Field descriptions ====== 
-===== 20.03. Field descriptions ===== +\\ \\ For descriptions of individual mSupply fields please refer to section **[[tables_fields:]]** 
-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. +|  //Previous:  **[[admin:updating]]** | | Next: **[[other_stuff:]]** //  |
- +
-==== Main tables ==== +
- +
-^ Table ^ Description    ^ +
-| [[.:tables_fields:transaction|Transactions]] | A transaction record is stored for each invoice | +
-| [[.:tables_fields:trans_line|Transaction lines]] | one transaction line record is created for each line on an invoice | +
-| [[.:tables_fields:item|Items]] | An item record is created for each product or service purchased or supplied | +
-| [[.:tables_fields:item_line|item_line]] | Each batch of stock at a particular location has one item_line record | +
-| [[.:tables_fields:name|Names]] | Each patient, customer or supplier has one name record | +
-| [[.:tables_fields:purchase_order|Purchase Orders]] | One record is created for each purchase order to a supplier +
-| [[.:tables_fields:purchase_order_lines|Purchase Order Lines]] | Each record is an order for one item on a purchase order | +
-| [[.:tables_fields:quote|Quotes and Quote Lines]] | Quotations submitted by suppliers | +
-| [[.:tables_fields:tender|Tenders and Tender Lines]] | Tenders created to invite quote submissions from suppliers | +
-| [[.tables_fields:requisition|Requistions]] | Tables that store customer submitted stock and order data | +
-| [[admin:tables_fields:stock_take|Stock take and Stock take lines]] | Stock takes and their related records | +
-| [[.:tables_fields:store|Store]] | Information about a particular virtual store | +
-| [[.:tables_fields:drug_register|Drug_Register]] | Drug Registration records (for use in the Drug Registration module) | +
-| [[.:tables_fields:aggregator|Agreggator]] | Stored metrics for use in reports | +
- +
-==== Foreign Keys ==== +
-[[admin:tables_fields:foreign_keys|Get your Foreign Keys here.]] +
- +
- +
-==== Other tables ==== +
- +
-^ Table ^ Description    ^ +
-| [[.:tables_fields:abbreviation|Abbreviations]] | Stores abbreviation codes and their expansion for dispensing directions | +
-| [[.:tables_fields:account_code|Account_code]] | Stores account codes associated with each item | +
-| [[.:tables_fields:authorisation|Authorisations]] | Tables that store authorisers and pending authorisations | +
-| [[.:tables_fields:backorder|Backorders]] | One record for //Customer// backorder. | +
-| [[.:tables_fields:bill_of_materials|Bill of Materials]] | One record for each mSupply user | +
-| [[.:tables_fields:contact|Contacts]] | One record for each contact | +
-| [[.:tables_fields:currency|Currency]] | One record for each curency | +
-| [[.:tables_fields:drug_interaction|Drug interactions]] | Drug interactions and Drug interaction groups | +
-| [[.:tables_fields:item_category|Item Categories]] | One record for item category | +
-| [[.:tables_fields:label|Labels]] | Miscellaneous labels | +
-| [[.:tables_fields:location|Location]] | Each record contains one shelf location. | +
-| [[.:tables_fields:log|Log]] | Each record contains one event recorded by mSupply | +
-| [[.:tables_fields:user|Users]] | One record for each mSupply user | +
-| [[.:tables_fields:item_dept|Item Departments]] | The departments that an item can be assigned to +
-[[.:tables_fields:item_direction|Item Directions]] | The default directions that are assigned to items | +
-| [[.:tables_fields:item_note|Item notes]] | The notes that are attached to items | +
-| [[.:tables_fields:item_warning_join|Item Warning Link]] | A join table that stores which items have which warnings | +
-| [[.:tables_fields:name_category|Name category tables]] | Tables that store the categories that can be assigned to names (customers, suppliers, donors, manufacturers etc.) | +
-| [[.:tables_fields:prescriber|Prescribers]] | One record for each prescriber | +
-| [[.:tables_fields:regimen|Regimen records, Regimen and Regimen Lines]] | Everything to do with storing ARV regimen records | +
-| [[.:tables_fields:reminder|Reminders]] | A reminder is an event with a due date- like a to-do | +
-| [[.:tables_fields:store|Stores]] | Stores and join tables that control access per-store | +
-| [[.:tables_fields:warning|Warnings]] | The warnings which can be attached to items | +
-| [[.:tables_fields:custom_stock|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    ^ +
-| [[.:tables_fields:number|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: [[clients:east_timor_assets|Asset Management for East Timor]]     Next: [[admin:backup_and_restore|Backup]]//+
 ---- struct data ---- ---- struct data ----
 +pagestatus.status    : 
 ---- ----
  
  • Last modified: 2019/09/04 13:33
  • by lou