Vlookup Question

  • 0
  • 1
  • Question
  • Updated 11 months ago
  • Answered
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.
Photo of James Cho

James Cho

  • 110 Points 100 badge 2x thumb

Posted 11 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of James Cho

James Cho

  • 110 Points 100 badge 2x thumb
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
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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.
Photo of James Cho

James Cho

  • 110 Points 100 badge 2x thumb
The violation rates are different for the same Violation type per contractor. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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?
Photo of James Cho

James Cho

  • 110 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
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
Photo of James Cho

James Cho

  • 110 Points 100 badge 2x thumb
I've sent an inquiry