I am building out a repository for Standard Operating Procedures (SOPs) (dbMasterRepository
) which go through various stages of approval (and once approved, incrementing the document’s ID number upon being flagged for re-evaluation). The SOP document itself is made up of a canvas column which has the narrative along with filtered views of Linked Resources (dbLinkedResources
), Defined Terms (dbDefinedTerms
) and Comments (dbCommentLog
).
When a new document is created a department is selected (from a select list) along with the document type (select list: Policy, Procedure, Resource) and based on the options chosen, the next available document number is generated, (dbDocumentNumbers
).
In all cases down the line (comments, linked documents, defined terms, etc) I utilize select lists with the source being dbDocument Numbers
.
There are two things I’ve noticed:
- In a few cases I am unable to reference the fields I need (as they reside only in
dbMasterRepository
. - This has brought to light a potential problem in referring from one table to another, specifically the need to use
thisRow
in some cases while requiringdocumentID
(display column ondbMasterRepository
) in order to get the desired outcome.
My main question then becomes, in a scenario like this where the document numbers (the primary identifier of each document) are created on an initial table (dbDocumentNumbers
) how should one structure other lookups? For example, should comments on dbCommentLog
be tied to dbDocumentNumbers.documentID
or dbMasterRepository.documentID
? I currently have them all referencing dbDocumentNumbers
and while it is all working as desired, I have two competing issues that I can only solve if I change the source of the Select List Tables (and subsequent formulas that will now reference thisRow
or documentID
or visa versa). I know this was long-winded and lack enticing graphics, to draw respondents in, but hopefully it piques someone’s interest.