ultimate portal filter / query

[Jodin]

i am looking to use a massive portal filter/query in fm7. i have 6 fields of various types i want to filter with, and i want the ability to select "all" from each of the fields. i'm having trouble getting my head around a decent solution here and am still struggling through the new relationship model.

it's a 1 file system and the portal will show records from just one table (of course). i just can't find a nice way to make the huge portal filter work.

each record in the bugs table has the following properties, amongst others:

status - can be 1 of 4 text choices from value list

target - can be 1 of unlimited text choices from value list

prior1 - can be 1 of 4 numeric choices from value list

prior2 - can be 1 of 4 numeric choices from value list

vfound - can be 1 of unlimited text choices from value list

vfixed - can be 1 of unlimited text choices from value list

i envision the 6 fields with pulldowns above the portal, and then an "all" box under each one (or selectable from the value list). the filter would default to "all" on the 6 fields, showing all bugs, and then the user could drill down (or query) the bugs list by selecting specifics from the pulldowns.

short of running a find in the background and marking the records to show in the portal i can't think of a better way to do this. any help is appreciated.

[Ender]

Hi Jodin,

You can do this by using calculated match fields, where each field has the original field value plus "All". It would look like this:

FirstName_Calc (calculation, text result) = FirstName & "All"

The parent key can simply be a global field with the value list (with "All" as a choice,) or you might allow the filters to be blank. To allow blank filter values, use an unstored calc:

FilterFirstName_Calc (calculation, text result) = case(isempty(gFirstName); "All"; gFirstName)

One thing to note, "All" could be a poor choice as there might be names that use "All" as the first few letters. This is prabably not an issue with exact match relationships (=), but will be if you want to use a type ahead technique to show matches that start with "All". This could be corrected easily enough by adding some unusual characters to the All text, like "[All]" or something.

[Ender]

I forgot to talk about the relationship for this. Once you have your calculated match keys and parent keys, you will need to make a multi-criteria relationship that the portal will use. The relationship will look something like this:

Main::FilterFirstName_Calc = Main 2::FirstName_Calc

AND Main::FilterLastName_Calc = Main 2::LastName_Calc

AND Main::FilterStatus_Calc = Main 2::Status_Calc

...

Also, your number fields can be filtered in the same manner as text fields, just make sure your calcs have text results so that "All" will be recognized.

Date and time fields might need to be done differently. Either converting them to numbers or using range relationships.

[Jodin]

yep that worked great. thanks!

this is literally the first project i've done in v7 so i'm still making messes trying to do everything 10 ways. sometimes v7 makes me feel like a newbie again -/

[deego55]

i just set up this technique. it works well when a value list is based on custom values, but:

How do i add the [ALL] option to a field whose value list is determined by the contents of another field? i have a YEAR field that i want to filter on, but need the [ALL] option as well.

thanks for any help!