Filtered Value List

[ron G]

I am using FM 12 Adv in OSX and Windows

 

I have a MEMBERS table that has:

PKMemberID  etc

ActiveInactive (that holds the value 'Active' or 'Inactive'

 

I need to base a value list on just the 'Active' Members.   I suspect I make another TO of Members but I can't 'get' how to setup up the 'filter' for my popup that will show just the Active members...

 

I am mentally stuck.  Can someone give me a swift kick to get me started?

 

Thanks

 

Ron

[Ocean West]

Here is one way to do it. 

 

member.fmp12.zip

[ron G]

Thank you for the file.  I can see that it works. 

 

But I must ask "Is the 'isActive' field used as a filter?  That is , is FM filtering for isActive Member content ONLY when there is something in the isActive field and disregarding isActive when it is ""?

 

Also, it looks like this technique only works if the isActive field is present in the  is the 'Values from the first field'.  (Which makes sense if FM is 'filtering' according to this field)  But, the downside is that it looks like FM will not let me SORT according to Member::name.  That puts the VL in a random or 'as entered' order.   I need it sorted because the user will be looking through up to 150 names.

 

Thanks again for ideas.

 

Ron

[ron G]

From reading other forum posts I 'got' that I needed to create a calculated field in the members table.  The criteria changed slightly from Active/Inactive to Deceased or 'not deceased'.

 

So, I created a calculated field called NoDOP (No Date of Passing) which had the formula if(Isempty(DOP);Members::Name_LastFirst).  If the Member is not deceased his DOP (Date of Passing) field is empty.

 

Then I based the popup on this calculated field with the left panel being PK_MemberID and the right panel being NDOP (Which only holds the name of not deceased members). 

Baboom... A value list that shows only 'not deceased members'.

 

Thanks to Ocean West for giving me the 'kick start'.

[Ocean West]

the reason why i do this the  isSomething  creates a single field for that data type - i could resolve it to boolean  1/0 but that only tells me part of the story when i want to get a List ( table::isSomething ) not only do I can get the list of known parent ID's for that item.

 

In my solution I too have a status for Deceased - typically in this case i have a DOD a date field for date of death or date of deceased, then have  isDeceased flag field that only populates with the UID number if there is a valid date in the DOD field. 

 

-

 

Another technique - can be used if the list you are seeking say only a few hundred rows - (don't use if your solution has thousands of rows as it could be a performance hit ) but just use SQL function to query the list of IDs based on other criteria ( isActive isDeceased, isMarried ) and then you can just use that return separated result as a key for a valueList  

 

some techniques can be seen here http://www.filemakermagazine.com/videos/interdependent-value-lists.html