Forum Discussion

RebeccaLutz's avatar
RebeccaLutz
Qrew Trainee
5 months ago

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

     

     

     

  • 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

     

     

     

  • MariaPeralta's avatar
    MariaPeralta
    Community Manager

    I love the start of this response because it is 100% true. When people would ask me, "Can we do X with Quickbase?" I disliked having to answer because sometimes they thought I was patronizing them - but I wasn't. If you have unlimited time (or $) there isn't anything that can't be built using Quickbase. The question to ask is "Given my constraints, can we build this in time to meet my needs?" Ask this question and we can start laying out the ask and what it will take to meet it. 

  • Thank you Mark!

    I started to set up the middle join table as you had described but became confused quickly.  Now I understand what I need to do.

    Yes, I have documents that use many terms, and those terms go to many documents.  The same thing happens with the references...one document has many references, and one of those references may be used in several documents.

    The idea of labeling fields as primary and supporting really helps straighten things out.

    And yes...it will be a long process to connect all of the links together for our documents, but it sure will help us streamline things and keep us in compliance in the end.

    Again, thank you for your help!

    • MarkShnier__You's avatar
      MarkShnier__You
      Icon for Qrew Legend rankQrew Legend

      Ok, once you get all the functionality working, and you see the easy way you will be able to determine which terms are in which documents on which documents are used another documents, we can discuss ways of how to create those join records more quickly.