Question:
How and where is the data on my document stored? Can you help me understand the schema?
Answer:
If you are just an average user, probably not. This is way too complicated. But if you are trying to write a custom report or create custom SQL code, the information here may help. The data from a document is stored across many tables. Most of the tables begin xsfDoc*, but a few do not.
All of the data for a document can be retrieved from the data store using the document key xsfDocHeader.DocMasterKey. This key is passed to dbo.p_GetDocMaster and it returns all the related document data.
For some, that explanation provides all the breadcrumbs they would need, but for others an explanation of the component tables used to store a document may clarify things futher.
- xsfDocHeader—this is the cornerstone of the document. The title, document number, and project are stored here. In the UI, this corresponds mostly to the document header section of the document.
- xsfDocRevision—this is the revision anchor of the document. Every document has at least one revision, but documents can have more revisions also. Whenever you gather data for a document you must take revisions into account. The view vsf_RevisionInfo materializes from the DocMasterKey to the current DocRevKey. You will see this used often in report queries. In the UI, this corresponds mostly to the Detail tab on the document (but sometimes the Note/Scope and Email tabs too).
- xsfDocRevItem—this table is simpler than most. It provides the mapping between a specific document revision and what items are in that revision of the document.
- xsfDocItem—this is the basic item information: descriptive fields, drop downs, and dates. In the UI, this corresponds mostly to the Items tab.
- xsfDocItemTask—this is the financial information for an item: cost code, account, expense and revenue, GL and related coding. Technically, there can be multiple item tasks per item. You see this on Change Orders: there is one xsfDocItem per Change Item, and one xsfDocItemTask per line in the Change Item Budget window.
- xsfFileAttach—there is one record per attachment to the document. In the UI, this corresponds to the Attachments tab.
- xsfForecast—For Budget, Forecast and Period Distribution Doc types, a set of records is stored per document revision representing the financial snapshot. In the UI, this corresponds to the BFA.
- xsfRoute—one record per routing on the document. In the UI, this corresponds to the Route Details tab.
The above make up the core of a document. The remaining tables are more ancillary and are presented in alphabetical order.
- xsfComment—one record per comment. Comment TopicKey may be linked to the DocRevisionrKey or a DocItemKey. In the UI, this corresponds to the comment/remark grid on the Notes tab and the comments grid on the Item detail.
- xsfCompliance—one record per compliance item on the document. In the UI, this corresponds to the Compliance tab.
- xsfDocAddr—one record per address. In the UI, this corresponds mostly to To/From/Address tabs. Remember Site address types and other address types exist.
- xsfDocAlert—one record per alert type being tracked for this document. Alerts are only enabled for the document if there is a record here. In the UI, this corresponds to the Alert pop up dialog.
- xsfDocDates—one record per date type being tracked for this document. In the UI, this corresponds to the Dates tab.
- xsfDocPosting—one record per financial posting for the document. In the UI, this corresponds mostly to the revenue and expense posting buttons.
- xsfDocSession—one record per access session for the document.
- xsfDocSingleUser—one record per document indicating who (if anyone) currently has exclusive access to the document.
- xsfInclusion—one record per inclusion, exclusion, etc. on the document. In the UI, this corresponds to the Incl/Excl tab.
- xsfMeetingAttendee—one record per attendee on the document and also used for Liens/Splits. In the UI, this corresponds to the Attendee/Liens tab.
- xsfRouteTransmittal—one record per routing that has a transmittal.
Sometimes gathering certain data is so common that there is a view or function to help
- dbo.v_ItemTaskAggregates—this view returns one row per item on the current revision of a document
- dbo.v_ItemTaskDetail—this view returns one row per item budget line on the current revision of a document
- dbo.vsf_RevionInfo – this view resolves the current revision of the document
KBA-01489; Last updated: October 12, 2016 at 8:24 am