Filtering a Value List from another File

[Conner]

Hi, FMP Fans!

I've got one for ya:

Setup

- Two Databases (Employee and Bug)

- Records in the Bug db have an Employee field

- This field uses a Value List that looks at the Employee field in the Employee db via the "Use value list from another file" Value List option

- The Employee db has a field called Status that is marked either "Current" or "Former". In several layouts and Value Lists, the Employee db shows only "Current" employees by scrips that run a find for "Current".

Question

Can I have the Value List in the Bug db show only "Current" employees so I don't have to maintain two Employee Tables in both databases?

[Fenton]

I know of 2 ways. This is the simpler, but adds a bit of extra data to the table. In Employee create a calculation field:

_cEmployeeCurrent, =

Case ( Status = "Current", Employee )

You'd want the Status field to auto-enter "Current" on creation; you don't want it empty, unless you add that to the calculation.

Then create a value list based on that field, in Employee. Then reference it from Bug.

BTW, it really should be an EmployeeID, not a name that you're using, considering that Employee is a table of its own, hence can have an auto-enter serial ID. An ID uses less data, and is not vulnerable to misspellings and name changes.

[Conner]

<------ Feels Dumb

Thanks for the great solution!

-Conner

[Conartist]

I am attempting to do a similar thing in my database. My database tracks events. An event is marked as "current" if it is less than 30 days from the closing date of the event. I want to use a filtered value list for data entry such that the drop down list only lists show names for shows that are marked "current". I tried to follow your instructions but I'm not getting good results. Here are the fields involved:

Showfacts:Show Name

Showfacts:Active_Status = Calculation(If ( End Date - Get ( CurrentDate ) ≤ -30; "Not Active" ; "Active" )

Showfacts:Show Name_Active = Calcuation Case (ActiveStatus = "Active" ; Show Name ; "Not Active" )

If I'm understanding the logic correctly, the Show Name_Active field will drop in the Show Name when the Active_Status field is set to "Active". When the Active_Status field is set to "Not Active" the Show Name_Active field will drop in "Not Active". Then when I create a value list on the Show Name_Active field it should list all the Show Names for "active" shows and then a "Not Active" entry. This sounds good to me - it is better than the current situation which is a huge drop down list of all show names in database.

This isn't working as expected. First I had to change my two calculation fields to be indexed for them to be eligible to be used in the value list. (I'm concerned about this as I'm not really confident on what Indexing really means and I think calculation fields should generally be unstored.)

My current result is a list of 2 shows and the Not Active entry. I have 19 currently active shows. No idea why its grabbing only those two shows and not the others. I feel like I'm missing something.

[comment]

A value list is basically the same thing as an index. For a field to get indexed, it MUST be stored.

However, a calculation using Get ( CurrentDate ) CANNOT be stored - because then it won't update.

The way out of this conundrum is thru a relationship, where the unstored is entirely on the "left" (parent) side of the relationship. In this case, you could use an unstored calculation =

Get ( CurrentDate ) - 30

and define a relationship comparing the calc with the EndDate on the other side. Then you can define a value list using only related values.