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 [2016/11/30 07:24] – [Main tables] markadmin:tables_fields [2021/10/08 11:15] (current) Gary Willetts
Line 1: Line 1:
-===== Field descriptions ===== +{{indexmenu_n>199}} 
-{{entry>80.Admin/7.Field descriptions|*}} +====== 25.22Field descriptions ====== 
- +\\ \\ For descriptions of individual mSupply fields please refer to section **[[tables_fields:]]** 
-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. +|  //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) | +
- +
-==== 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: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: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_5|Custom stock 5]] | Holds the details of the values that are selectable in the 5th user field when receiving stock  | +
-| [[.:tables_fields:custom_stock_6|Custom stock 6]] | Holds the details of the values that are selectable in the 6th user field when receiving stock | +
-| [[.:tables_fields:custom_stock_7|Custom stock 7]] | Holds the details of the values that are selectable in the 7th user field when receiving stock. +
-| [[.:tables_fields:custom_stock_8|Custom stock 8]] | Holds the details of the values that are selectable in the 8th user field 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 | 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 |  |  | +
-| table_num | Internal table number of the record | Long |  |  | +
-| table_id_num | Internal field number for the ID field of the record's table | Long |  |  | +
- +
-^ 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: [[.:tables_fields:foreign_keys|Diagnostics]]     Next: [[.:tables_fields:foreign_keys|Finalising transactions]]//+
 ---- struct data ---- ---- struct data ----
 +pagestatus.status    : 
 ---- ----
  
  • Last modified: 2016/11/30 07:24
  • by mark