Discussions

Expand all | Collapse all

Populate field based on table relationship with three matching fields

  • 1.  Populate field based on table relationship with three matching fields

    Posted 06-28-2017 12:33
    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?


  • 2.  RE: Populate field based on table relationship with three matching fields

    Posted 06-28-2017 12:46
    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]) 


  • 3.  RE: Populate field based on table relationship with three matching fields

    Posted 06-28-2017 14:03
    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. 


  • 4.  RE: Populate field based on table relationship with three matching fields

    Posted 06-28-2017 14:21
    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.


  • 5.  RE: Populate field based on table relationship with three matching fields

    Posted 06-28-2017 17:31
    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.


  • 6.  RE: Populate field based on table relationship with three matching fields

    Posted 06-28-2017 17:34
    OK, thx for letting me know that you got it working.