Vlookup Question

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.

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

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.

The violation rates are different for the same Violation type per contractor. 

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?

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.

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

I've sent an inquiry