Portal Filtering (from list of values)

[Trinity32244]

Hi all,

I think that I am making a task harder than it needs to be. From my "Home" table I have a portal of related "External Companies". These "External Companies" have a related "Address" table for entering multiple addresses. From the "Home" table I want to filter (via temp field in "Home" table) the "External Companies" by fields in the related "address" table. For example, from the home table I would like to filter the portal of "External Companies" by a city name. My first thought was to add a calc field in "External Companies" to get List() based on related "Address" table and then filter by this. The problem is that the filter does not work unless all related cities are entered instead of any matching city. I can create a work around but was curious if anyone knew of way way to get all field values of a related table into a parent table that can be then filtered? Thanks in advance as this seems like a simple task that the answer is staring me in the face but I am wasting a ridiculous amount of time to create these work arounds.

[comment]

The problem is that the filter does not work unless all related cities are entered instead of any matching city.

Not sure what you mean by that. Are you trying to filter the portal, or the underlying relationship?

[bcooney]

I don't understand the use of a "Home" table. What purpose does this serve?

Given your stated need to "filter" companies by any with an address in a specified city, simply find in Addresses and gtrr (match found) Companies or find in a portal of addresses on a Company form. It's the addition of this "Home" table that is, imho, adding unneeded complexity.

[Trinity32244]

Thanks for replying. Although, I used a specific example I have run into this many times for other applications as well. The "Home" table is the "Internal Company" that has been selected ( this solution is for a company that also processes for its two sister companies so the solution has information based on the "internal Company" they are working out of at the time). I can create a work around and have through creating more T.O. and relationships but I really like the addition of portal filtering because I always found it extremely tedious ( and makes solutions much harder to follow later) to create multiple added relationships just for viewing or sorting purposes. I have been trying use the portal filtering as much as possible just for organization purposes really. I can get the addresses in list form using a temporary calculation field in the "External Company" table using List(). This pulls in a carriage return list of each city associated with this this external company. But to filter by this list it would have to match. I never use repeating fields but from what I have read about them it would be perfect for this application (as a search will produce a match in any repetition and I imagine the portal filtering would as well). Do you know how to get the list into a repeating field or how to make a repeating field calculation that will get the cities directly and put them into repetitions. Thanks

[comment]

I am still trying to understand why what you describe doesn't work. None of the extra steps you describe - a temporary calculation field using List() or a repeating field calculation - should be required.

If I understand your requirement correctly, you want to enter a city into a global field in the Home table (which is actually OurCompanies table?), and see only external companies that have an address in that city. If so, you only need to set up the filter as =

IsEmpty ( Home::gCity )

or

not IsEmpty ( FilterValues ( Home::gCity ; List ( Addresses::City ) ) )

[comment]

I have been trying use the portal filtering as much as possible

Keep in mind that portal filtering can get slow with a large related set.

[bruceR]

A portal filter is a filter on the record set defined by the underlying relationship. What is that relationship? Also, the list function doesn't belong in a portal filter. Each record in the base relationship is evaluated in turn by the filter calc.

[Trinity32244]

Thanks to all who replied. I did try the suggested solution and got it to work. I have very little need or experiencing in creating carriage returned lists and did not see many functions for them. Like normal FM has a function that did exactly what I needed and I just needed to look-up FilterValues and how to use it which was "Comment's" suggestion. I figured there was something right under my nose and that was it. I can't thank ya'll enough for helping me with this one as it will save me a ton of time (and frustrating confusion later).

For Anyone else who stumbles on to this thread:

The Problem:

I have 3 tables for this example Internal_Company > External_Companies(or Contacts) > External_Company_Addresses.

Each Internal_Company has many External_Companies which have many External_Company_Addresses. In a Internal_Company layout you can view the related External_Companies via a portal. For this portal you can filter the results by a few options selected(or entered) from fields in the Internal_Company layout. One of which was "city". Since I am two tables away I had to create a field in the External_Company table that is a calculation that uses the List() function which gets all related Addresses cities and creates a carriage return list. Now I needed a way to filter the portal. The problem was that I could not simply use the created field that has the list of cities to = the filter fields created (i.e. filter_city_selection = related_city_list) because you would have to match the carriage returned list exactly instead of matching one item from the list.

The Solution:

In the portal filter I used:

FilterValues ( Internal_Company::filter_city_selection ;External_Companies::related_city_list ) ≠ ""

Thanks again to all who commented

[Trinity32244]

Took me a little bit to completely figure out how to run the portal filter calc so figured I would post what I got to work to save time for anyone interested. Basically if the filter field is not empty evaluate and if it is empty make each portal record true for that filter.

Here is my calc (please dis-reguard the unorthodox naming scheme) for the portal filtering that worked:

If(

Edit_Internal_Company::External_Company_Filter_Company_City ≠ "";

FilterValues ( Edit_Internal_Company::External_Company_Filter_Company_City ;View_External_Companies::External_Company_Address_Cities_List ) ≠ "";1=1)

and

If(

Edit_Internal_Company::External_Company_Filter_Company_State ≠ "";

FilterValues ( Edit_Internal_Company::External_Company_Filter_Company_State ;View_External_Companies::External_Company_Address_State_List ) ≠ "";1=1)

and

If(

Edit_Internal_Company::External_Company_Filter_Company_Zip ≠ "";

FilterValues ( Edit_Internal_Company::External_Company_Filter_Company_Zip ;View_External_Companies::External_Company_Address_Zip_List ) ≠ "";1=1)

........ect

[comment]

This:

If ( test ; 1 = 1 )

is a VERY strange way to write:

test

Also, it is best to use the IsEmpty() function for the test. And you are using If() where the logic calls for OR. This makes it even harder to follow (if one survives your naming scheme...).

[bruceR]

I agree with Comment. Though you say this works, it looks like a bit of a logical mess and it would be better if you could post a copy or clone of your file so a number of areas could be significantly improved.

[Trinity32244]

I really appreciate the follow up. I am self-taught and more of a hardware or lower level microprocessor software designer so I know that what I posted probably looks to be a complete mess to someone who knows what they are doing in FM. I know just enough to be dangerous and usually don't get help from anyone so I appreciate the suggestions and I look at them closely. It always surprises me when others are willing to help just to help and I wanted to post what I did in an attempt to save someone else time but I realize I may do more damage than actually helping anyone :-) Because my naming scheme is poor I have really taken to the portal filtering for relationship graph organization. I only need to use FM about once every few months and it always takes a minute to remember and get back into it.

For the comment about the IsEmpty() : I will changed my calc. Thanks I remember being told before now this is more standard than using ="" .

For the comment about if( test; 1=1) vs "test" : I am not sure what is meant by that. I do not see a function label "test". As I understand from reading the help file the portal filter result must be boolean so because each one of the portal filters are isolated (and in-addition too) my logic was to go down the list of portal filters 1 by 1 and give it a logical true statement if not-empty and match the expression value. Each portal record must match all filters entered to remain in the portal and not hidden. So basically, if portal record matches all portal filters that are not empty show else hide.

Thanks again

[comment]

For the comment about if( test; 1=1) vs "test" : I am not sure what is meant by that. I do not see a function label "test".

By "test" I mean an expression that returns true of false. Filemaker has no Boolean data type, and any number other than 0 is true. Conversely, a Boolean test returns either 1 or 0.

As I understand from reading the help file the portal filter result must be boolean

Exactly, and that's why =

If ( not IsEmpty ( foo ) ; 1=1 )

can be written simply as =

not IsEmpty ( foo )

[Trinity32244]

I definitely see where I created some confusion. This filter on the UI is meant to filter down the complete list of external companies by as many filters as the user wants. This means that I have to run a test in the portal filter that says "if portal filter field is empty automatically accept this part of the evaluation but if not empty evaluate including/excluding". Also each filter field is independently constraining the found set not extending the found set (to use FM terminology) which means each found item has to meet all entered criteria not any entered criteria. I see where the formula was extremely difficult to follow so I re-arranged it to see if this would make more since for someone editing the db later on. I have changed the naming scheme for example purposes (the db relationship graph has a ridiculous amount of notes which is why I don't focus on the naming scheme other than a prefix of View or Edit to let you know if that relationship was designed to view info or edit info). Please let me know if this would be a little easier to follow. Thanks

If(

not IsEmpty ( Internal_Company:City_Filter ) and FilterValues ( Internal_Company::City_Filter ; External_Companies::Address_City_List) = ""; 0;1)

and

If(

not IsEmpty ( Internal_Company::State_Filter) and FilterValues ( Internal_Company::State_Filter ; External_Companies::Address_State_List ) = "";0;1)

....ect

Just to follow logically with the evaluation : If (filter field is not empty) and filter field does not match any on the list then fail else pass

It uses and instead of or because it is constraining the found set instead of extending the found set.

[comment]

I am afraid you are still missing my point:

I have to run a test in the portal filter that says "if portal filter field is empty automatically accept this part of the evaluation but if not empty evaluate including/excluding".

This can (and IMHO should) be restated as:

Show this record when:

(a) filter field is empty;

OR

( one of the grandchild values matches the filter.

If you have more than one filter field, then you need to AND several of these conditions:

(

IsEmpty ( Parent::FilterA ) 

or 

not IsEmpty ( FilterValues ( Parent::FilterA ; List ( GrandChild::ValueA ) ) )

)



and



(

IsEmpty ( Parent::FilterB ) 

or 

not IsEmpty ( FilterValues ( Parent::FilterB ; List ( GrandChild::ValueB ) ) )

)



...