Expand all | Collapse all

Vlookup Question

  • 1.  Vlookup Question

    Posted 12-04-2017 15:44
    I have 3 tables that are related to eachother.

    First table is a list of projects, second are contractors related to the project, and third a list of violations & costs. 

    In the third list, I have broken down the price of each violation per contractor. When a violation is picked, I want to automate the cost of the violation, normally in excel I can do this with a vlookup and or index/match.

  • 2.  RE: Vlookup Question

    Posted 12-04-2017 15:56
    In order to do that, you will need to have a Key field of the Violations table which is a concatenation of the Related Contractor and the Violation.  ie you are saying that the different contractors pay different prices for the same violation.

    For example 

    List("-", ToText([Related Contractor]), [Violation])  I am assuming here that the Violations are just a multiple choice field and not a table of Violation types.

    Now, Normally the Key field of a table may not be a formula field.  So you have two choices.  The low tech way is to make a regular text field field called [Contractor - Violation Key field] and make a form rule to populate that when the record is saved by making it the same as the formula field.  The alternative is to use the API to set the Key field to that formula field.  API_SetKeyField ... and you just type the API into the browser URL bar.  https://help.quickbase.com/api-guide/index.html#setkeyfield.html

    Then on the Violation occurrences table  (I assume actually you have a 4th table of the actual violation occurrences) you would replicate that Key field  by formula and look up the cost of the violation.

  • 3.  RE: Vlookup Question

    Posted 12-05-2017 16:37
    I'm having trouble understanding option #2, Option #1 makes the dropdown list way to big for it to be use-able unless there is a way to filter to just the contractor related. 

    I'm sorry but can you explain option 2 a bit easier

  • 4.  RE: Vlookup Question

    Posted 12-05-2017 16:39
    Option 1 and 2 will have the same length for the list.  Are you saying that you have different violation rates for the the same Violation type across different contractors?  

    or is the fine for say no safety boots the same for all contractors.

  • 5.  RE: Vlookup Question

    Posted 12-05-2017 16:47
    The violation rates are different for the same Violation type per contractor. 

  • 6.  RE: Vlookup Question

    Posted 12-05-2017 16:49
    Then I do not understand your question.  These violation rates will be auto populated once you load up that table, so what do you care how long the drop down list is?

  • 7.  RE: Vlookup Question

    Posted 12-05-2017 17:01
    Let me rephrase, 

    In my violations table, Should each column be individual contractors or rather a field name be contractors and have individuals rows with contractor.

    I've attached a screenshot below.

    Option A would be a smaller table but this is where I would need the vlookup reference.

    Option B is how my table is set up currently with repeating Violations per contractor, but I can't seem to auto-generate the costs this way either.

  • 8.  RE: Vlookup Question

    Posted 12-05-2017 17:25
    Option B is the way to go.

    I have described the setup required to auto populate the cost.  We can either work though this slowly together on this forum or contact me for one on one assistance via the contact info on my website QuickBaseCoach.com

  • 9.  RE: Vlookup Question

    Posted 12-05-2017 17:51
    I've sent an inquiry