Tweak a schema so the record ID doesn't replace a name

[21st Century Man]

Howdy all:

Here's the problem with the schema as-is (see attached): If a STAFF person's status changes from active to inactive, their STAFF record ID replaces their name in the EVENT portal since it violates the condition between T.O.s (for the value list); change their status back to active and their name returns.

For historical record purposes, what I would like to do is have their name remain in the portal irrespective of their status in STAFF. What do I need to change in the schema to make that happen?

Thanks in advance for your help!

Rich

 

Test.fmp12

[comment]

2 hours ago, 21st Century Man said:

I would like to do is have their name remain in the portal

I don't think you need to change the schema for this. Just place the name field from the Staff table inside the portal over the ID field and make it non-enterable.

There are other things in your file I did not get (e.g. why is there a (duplicate) name field in the join table,) but that's beside the current point.

 

[21st Century Man]

Thanks--I deleted the Name field from PRESENTER.

I had a separate Name field "hiding" behind the pop-up menu (using Hide Object When) so it would appear in a Find but not during browse mode. I'm still getting the same problem where the ID shows up--instead of the name--when a Staff member's status is changed to Inactive, so I'm sure my wiring is wrong. 

I've attached a new version of the test file for easier viewing since the font was too small in the first one.

The pop-up menu is so users can choose active staff (from STAFF) since the proposed value list can be very long if it includes active and inactive people. (Hence the key-status connection between the Event and Staff tables in the Relationships Graph.)

With that same pop-up menu, I want to grab the foreign ID from the Staff table and "push" it into the Presenter table.

Later, if a user wants to perform a Find for a (specific) staff person in the portal, I want a pop-up menu to show only those staff people stored in Presenter. (After all, why have names of people in the value list that don't have records for them?)

So therein lies the problem: How can one field in the portal have a value list of active staff names yet be able to allow a user to choose a name stored in Presenter? I'm thinking it's the PresenterChoice__r value list setup, but what I might be trying to do is impossible with one field so I'm looking for an elegant way out.

Am I making sense, or do I need to go back to my English for Dummies book? : )

 

Test2.fmp12

Edited April 3 by 21st Century Man

[comment]

4 hours ago, 21st Century Man said:

still getting the same problem where the ID shows up--instead of the name--when a Staff member's status is changed to Inactive

I suggest you take a look at the attached file (which is your original file reduced to the minimum + my suggestion).

Do note that there are two fields stacked inside the portal.

ActiveStaff.fmp12

 

4 hours ago, 21st Century Man said:

if a user wants to perform a Find for a (specific) staff person in the portal, I want a pop-up menu to show only those staff people stored in Presenter.

That's a problem. If the field is formatted to use a value list showing only active staff, it cannot be used to select inactive staff. You will have to find a way to use another instance of the field in Find mode. Or switch to completely different selection method (e.g. a card window).

 

 

 

 

3 hours ago, comment said:

a way to use another instance of the field in Find mode.

Here is a rather simple one:

ActiveStaff+Find.fmp12

 

Edited April 3 by comment

[21st Century Man]

Beauty! Thank you VERY much--I really appreciate it.