Tracking References and Definitions
I am relatively new to QB, but am excited about the possibilities for tracking multiple things.
I have a table containing the names of documents within our system (primary). Each of those primary documents reference other documents. In addition, each primary document has a set of abbreviations/terms.
I have built a table (table 1) with the documents and their identifiers. There is a second table (table 2) that has a list of universal abbreviations/terms.
In a third table, I would like to choose a single primary document from table 1. Then I would like to choose multiple documents from table 1 that the primary document references in the text. Then I would like to choose the multiple abbreviations/terms from table 2 to associate with the primary document.
Example: POL-0001 has a reference to SOP-1001, POL-1003, SOP-1004, and SOP-1005. It also has 17 abbreviations/terms that are used in the document. I would like a table that has all of these items listed for POL-0001, so I can trace which documents are affected by changes.
There are more than 100 terms and over 100 documents to choose from for association. (From what I have read, this eliminates the multiselect option). If I use a table reference, I would have to create as many lookup fields as there are terms for each document. (Same with references).
Are there other ways to approach this?
Thanks!
Becky
Welcome to Quickbase. All things are possible.
There are two steps in getting this to work. The first step is to get the relationships and table set up correctly. And then the second step will be to make it easier for you to get the data entered. So first things first.
Do you have a situation where one Document has many Document Terms, but also of course, one particular standard document term is going to be used on many documents.
So what we do is we set up a Many to Many relationship using a middle or so-called "join" table. We create a table called Document Terms.
so Documents < Document Terms > Standard Terms
ie
One Document has many Document Terms.
One Std Term has Many Document Terms.
The middle table that you will set up for this will initially have no fields. But when you set up the relationship he will look up the document name down into the document terms table and also look up the standard term from the terms table down into the Join table.
That will allow you to view a document and see an embedded report of all the document terms (ie a list of terms) and also will allow you to view a standard term record and see all the document terms (ie documents) it's used on.
When it comes to linking the documents, I gather you have a single table which holds all the documents but these documents are related to each other. But the same thing will hold no doubt, you will need a middle table called some thing like Document Usage.
This can be a little confusing because the documents are related to them self.
But you will make that middle table for Documnt Usage.
Then again you will make two relationships and you have to be careful about your field naming here. You will have one relationship where one document has many document terms and then again you will make two relationships and you have to be careful about your field naming here.
You will have one relationship where one document has many document terms and you will rename the field for related document to be called Related Master Document and look up the Document Name and call it Primary Document Name.
Then you will make another relationship but in this case you will call the field Related Supporting Document and the lookup field will be called supporting Document Name.
I'm not really trying to sell my services here but if you're new to Quickbase and you have a small budget, it may be quite worth your while to scrape up enough consulting funds to support about 3 hours of consulting time.
The set up I'm describing will work wonderfully for your purposes but the show stopper will be it will be a little bit painful to enter these connection records. There is a much faster way to do that using native Quickbase but it's a bit too complicated to describe here.
If you want to discuss this one on one, feel free to contact me at mark.shnier@gmail,.com or via QuickbaseCoach.com
