Conditional Value List - Dealing with ID field and Active/Inactive records.

[Alex117]

Hi, maybe someone can help me with this question. I have three tables set up:

 

Instructor table

Instructor PK field

Name field

 

Programs Taught table (join table) <----- for many-to-many relationship

Instructor FK field (Instructor PK)

Program FK field (Program PK) <----- pop-up menu

Hire Date field

 

Program table

Program PK field

Name field

Record State field ("Active" or "Inactive")

 

Layout and portal:

I have a portal showing the related records from the Programs Taught table on a parent layout based on the Instructor table. The portal has a pop-up menu with a conditional value list to select an existing program from the Program table and set the Hire Date that the Instructor began to teach that program.

 

Value List setup:

The value list is set up with two fields under the settings, the first field is the Program PK and the second field is the Program Name. I've set it up to show only the value from the second field (Program Name) in the pop-up menu, since the IDs from the PK field can be hard to reference. The value list is also based on a relationship to only show the programs that have a value of "Active" in the Record State field.

 

Issue:

The list is dynamic and works fine, but the problem I'm facing comes when a previously existing record found in the portal contains a program on the pop-up menu field that has now become "Inactive". If a program that is now "Inactive" previously existed in a portal record, the pop-up menu ceases to show the actual Program Name for those records as it used to and instead now shows the Program PK serial number value.

 

How can I keep the conditional value list, to still show me the Program records that are only "Active", but also maintain the older portal records unaffected by future changes and displaying the actual Program Name value at all times as opposed to the Program PK serial number whenever a change is made and that given program is no longer part of the "Active" dynamic value list?

 

Many thanks for any help.

 

Alex

Conditional Value List - Active records.fmp12.zip

[Matthew F]

The problem comes from combining a "dynamic" value list and the option to "Show values only from 2nd field" on your Pop-up menu.  Once an ID no longer falls within the dynamic range of the "active" value list, the field will no longer be able to display the 2nd value. 

 

A solution is to overlay the Program name field on top of  the program ID field.  If you set the name field to not allow entry during browse mode then the underlying Program ID field pop-up will still be activated when the user selects the field.