Populate field based on table relationship with three matching fields

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have a main table called records, then I have a table called fee Sch.  I need to be able to add a field to the main table that shows me the fee sch from the fee sch table.  The issue that I have, is that they have to match on three fields.  How do I make it say give me the Fee Sch that matchs the records table on field1, field2 and field3?
Photo of cowannbell

cowannbell

  • 680 Points 500 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
You will need to make the Key field on the Fee Schedule table be a field which concatenates the three fields. One way to do that is to have a formula calculate that string and a form rule to change a data entry Key field to to the value of that calculated field.

Then in a relationship to the transaction details table, use a formula field to calculate that same string.

The string formula would be like

List("-",
[Fee schedule factor field 1], 
[Fee schedule factor field 2],
[Fee schedule factor field 3]) 
Photo of cowannbell

cowannbell

  • 680 Points 500 badge 2x thumb
Okay, I get the concatenate field.  I did a formula field in both tables to concatenate the three fields together so they would match. I made the key field of fee sch table the formula. Now I'm a little unsure how to do the next part.  I want a field to auto show the correct fee sch number based on the two new concatenate fields. 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
You will need to create a data entry field for the Concatenated Key field and populate it with the values from the formula field.   You can use grid edit for that for the initial load up.

Then go to the field list and make that data entry field the Key field for the Fee Schedule Table.

Then make a relationship to the transactions table and lookup the fee.

Once you get that working, please post back and I will explain snap shot fields.
Photo of cowannbell

cowannbell

  • 680 Points 500 badge 2x thumb
Okay this is what I did.  I added a field to the Fee Sch table that had the three fields concatenated.  I added this as a text field and not a formula field so I could make it the primary key field.  I think added a formula field on the main table that concatenated the same three fields together.  I then did the relationship between the two and use those two fields as the reference field and key field.  I was then just able to pull the fee sch as the a lookup field.  It works perfect now.  I wanted it to change based on when the fee sch table is updated, so I didn't need to use snap shot.  Thanks.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,316 Points 50k badge 2x thumb
OK, thx for letting me know that you got it working.