Value list in join table not working

[Lea Alexander]

I have 3 tables: Person, Session, and Contract where Contract is a join table.  

I am in the Session table layout, in a Contract portal.  I have a field – Trainer Name in the Contract table – where I want to select the name of a person from a list of people in the Person table.  The Trainer Name field is a dropdown list using values from the Trainer value list “Person::__k_p_Person and Person::Name_Full”. I have tried numerous configurations with table instances and I either cannot alter the Trainer Name field at all, or I get a “?”.  

After the Contract record is created, I want it to show up in a Contract portal on the person’s record in the Person table.  

Each person will teach multiple sessions.
Each session has multiple trainers.
I have foreign keys in the Contract table for Person and Session. 
 

 

[Lea Alexander]

I changed the "trainer name" field to the fk_person field.  I think the problem I'm having is linking the Contract record to the Person table.  I am still just getting a "?" on the dropdown.  The Contract table records are being created through a portal on a Session table layout so fk_Session is being automatically entered.  But how does the fk_Person field get populated so that the Contract data is linked to Session and Person?

[doughemi]

Create a new TO of the Person table named PersonList and relate it to Session. If you want to see every person in the Person table in your dropdown, use the X relation operator between any fields. If you want to only see a subset of the Person table, create a relationship which filters the list. Then create the value list using only related fields in PersonList.

[Lea Alexander]

Thanks Doughemi.  I created the PersonList TO and linked Session pk to PersonList/session fk.   And the field using the dropdown is Contract::Person kf.  Dropdown still not working.  Which fields do I connect and what field should I use in the Contract table - Person kf or Trainer Name?

Really appreciate your help. 

[doughemi]

"Dropdown still not working" doesn't give us a lot to go forward on.

• Does the dropdown show the list of persons?
• What does or doesn't the dropdown do?
• Are you creating a new Contract record, or does one already exist, and you're adding a new person to it?
• Could you describe  the workflow you are modeling?

[Lea Alexander]

The drop down shows "3 ?".   "3" is the pk of the first person record

The drop down shows "3 ?" on existing Contract records and new Contract records. 

I have imported Session records and Person records.  I am creating the Contract records via a portal on a Session layout.

 

[Lea Alexander]

Progress...I can get a list of Person pks also displaying values from second field Name_full .  However, it sorts in pk order, rather than by last name. 

If I select - show values from second field only I get  "?" in the dropdown.

 

 

[Lea Alexander]

I found the problem - calculation was set to return a number rather than text.