Limit items in a value list

[Tom Kennedy]

I have a employee table that has a termination date field. I want to create a value list that only shows the employees with a blank termination date. Any help will be appreciated.

[Ocean West]

Hello Tom,

Create a new field that is a boolean of the date field isEmpty(Datefield) this is 1 if empty, create a constant field in your table constant = 1 then create a relationship from the constant to the calc field. Use this relationship as basis of your value list, or any table you wish to have context to the employee value list.

[Tom Kennedy]

Thanks for the advice. Your way is certainly simpler than the one I had implemented.

Question: Do I share the EmployeeDropdown table occurrence with all of the tables that has the Employee ID in them or is it better to create separate EmployeeDropdowns for each table?

[Ocean West]

if your using anchor buoy approach you would add a relationship for each table you wish to generate a value list, since it is based on context of where you are starting from you need to add also the constant to each table so that you can create a relationship - i am probably not explaining it well as i am at a friends helping them move... }:(

[Tom Kennedy]

Thanks.

[Fenton]

Alternatively, you could just create 1 value list which would work from anywhere. Do the relationship, as Stephen said, but do it as a self-relationship of the main Employee table occurrence (TO). Then, in the value list, specify,

Starting from: Employee TO

Since the relationship uses a constant on the originating side, it does not depend on the "current record of where you are context" to work, it just needs the "table occurrence context" of the Employee TO, which you can specify in the Value List dialog.

This "Starting from" is a very cool feature of a Value List, so you don't need several for this situation. It has to be based on something "constant" however.

[ P.S. You could also create a calculation field in Employee,

Case ( IsEmpty (DateTerminated); EmployeeID )

Uncheck:

[ ] Do not evaluate if fields are empty

HOWEVER, if you use "[x] Show values from a 2nd field" in the Value List, AND "[x] Show only values from the 2nd field" (the name field) it will still show ALL Employees. Because FileMaker uses the 2nd field for the index of what to show, and that 2nd field is not (yet) conditional. So you'd have to ALSO do the same calculation for a "employee name if not terminated" in order for it to work properly. Hence, the self-relationship method is better in this case. ]

[Emma in England]

Thanks for the advice on this thread which helped me solve my own similar problem. I am adapting an existing database, and confess to being utterly confused by the numerous table occurrences and relationships existing! Getting a drop down list of active employees took me forever, and involved adding 'Active' tags into tables that seemed to make no sense. However, it worked in the end. Maybe one day it will all come clear??

[Fenton]

I don't know if it "all" becomes clear (not to me anyway), but most of it does. Most things in FileMaker are very logical, relationships in particular, as they follow the rules of mathematical Set Theory. Though there is more than one way to arrange them on the Relationship Graph (big controversy :-).

What I was trying to point out in my earlier post is that it is possible to avoid having things like "active tags into tables that seemed to make no sense," by taking advantage of the "Starting from" choice in the Value List dialog. If what you're filtering by can be solved by a relationship with a constant on the originating side, then you only need one such relationship, originating from the data table where the date resides; Employees in that case.

Since it is constant, which means "evaluates the same from any record in the specified originating table occurrence to the specified target table occurrence," the current table-record context does not matter; the above value list can be referenced from anywhere. A value list is not necessarily related data from where you are (unless the current context DOES matter).

[P.S. If you need to show a portal, or get/read related data, then even if it is a constant relationship, you will still need "active tags into tables," and a relationship from the current table occurrence, because you must have current context to use a relationship (as opposed to just a value list.]

[Emma in England]

I thought it would work as you described - self-join Employees to Employees with an auto enter field 'Active' matched to a status field which MAY be 'Active'. That value list worked fine on the Employees layout, but not, for some reason, on 'Briefs'. There are so many table instances whose original purpose is not clear to me, I'm sure that's what the problem is with this particular database.

But these posts gave me hope, and at last it worked. I just wish I understood why, but I think it will come eventually!

[Joseph31]

I have been looking all over for something like this... Can you show me an upload example of something like this. I am having trouble following it. I have got it to work sort of but it only works when the broker that I am on is checked. The conditional list does not show up unless the broker previously selected is checked complete? Why?

My Setup

Broker_Table:

Broker_Co

Questionnaire_Complete (Check Box)

Etc...

Policies_Table:

Broker_Co

Etc..

Relationship:

Policies::Broker_Co = Broker::Broker_Co

There is a pull down menu on the policies page which has a value list that get info from the broker table.

Where do i put the constant and where is the relationship go in your example?

Thank you,

Thank you,

Joseph

Edited November 13, 2008 by Guest