multi-field filter

[gephry]

I know this has been discussed before but I can't find the posts...

I have a table HISTORY in which I would like to place a portal to show certain records in HISTORY. I want the portal to be filtered by three different global fields.

FILTER FIELD RELATING FIELD

gContact_NameFirst <===> Contact_NameFirst

gContact_NameLast <===> Contact_NameLast

gContact_IDnumber <===> Contact_IDnumber

(g meaning the global fields that I'm using to filter the portal. gContact_IDnumber and Contact_IDnumber are both number fields, while all others are text fields)

Basically the portal is not working. If I eliminate 2 of the criteria, it will work. With all three criteria enabled, I enter an ID in gContact_IDnumber but nothing in either of the other filter fields, nothing comes up. If I enter data in all three fields (data that exactly matches records in the table HISTORY), still...nothing happens.

I'm creative but feel that I shouldn't need to reinvent the wheel. Suggestions?

[Ender]

For multiple filters like this, only records matching all of the criteria would show up in the portal (I don't know why you're having trouble when all three are populated). In general, you need to have calculated keys on the parent side to allow for blank filters (or "All" selections if using value lists). Then on the child side, you need calculated keys if the corresponding filter field on the parent side could be blank (or "All").

The calc on the parent side would look like:

cFilter (calculation, text result) = case(isempty(gFilter);"zzzAll";gFilter)

and the match field on the child side would look like:

cMatch (calculation, text result) = field & "¶zzzAll"

If it's also desireable to allow for partial entries ("And" matching "Anderson", etc.) then there are some additional things that you'd need to do. I describe one technique here:

http://www.fmforums.com/forum/showpost.php?post/183594/

[gephry]

Ahha! I was thinking something along those lines. And I've figured out THE problem.

The problem was that I was doing everything from within the table HISTORY. I was doing it correctly, it just was not able to do all the computations correctly because I was trying to use global fields...of which you cannot index. So I created a ViewHISTORY table with only one record in it, with the fields (not global!) Filter_NameFirst, etc, and the new calc fields (not global!) calcFilter_NameFirst, etc.

I then used the Explode() functions and it works marvelously. Thanks!

I was going through another option in my head, which sadly doesn't work as originally planned. I'm going to give it more thought, but due to lack-of-indexing, I don't know if it'll work. Without going into too lengthy of an explanation, here's the idea:

To define one of the HISTORY calculation fields:

If( PatternCount( NAME, FILTER ) > 0; FILTER & "¶" & NAME; "¶All" )

whereas NAME is either Contact_NameFirst or Contact_NameLast from the HISTORY table, and FILTER is either Filter_NameFirst or Filter_NameLast from the ViewHISTORY table.

That would check ANY of the letters/letter combinations within whichever of the NAME fields you are defining (i.e. entering the letter "a" into the FILTER field, it will show all names with the letter "a" in them, not just NAMEs beginning with the letter "a"). The only problem is, the FILTER field is either an unrelated field in another table (ViewHISTORY in my case), an un-indexed calculation field, or an un-indexed global field.

This would be great if it'd work....but I doubt it will. The only other option is to create an ExplodeInternal() custom function which would explode all letters and combinations thereof within the string......which can get lengthy.

[Ender]

Oops, you've lost me. :

[gephry]

Haha, I was afraid of that. Alright...

We all know that user data entry is not consistant, right? Let's say someone entered "William Smith" and another someone entered "Bill Smith" -- both applying to the same guy.

With the solution you gave me, it's only effective if you his last name (and in my solution, his ID number, too). But what if you had multiple "Smith's" and wanted ONLY Bill/William's records (without knowing his ID number)?

The ExplodedKey() and ExplodedString() that are described in the link you posted only parse the data from the beginning of the string.

W

Wi

Wil

Will

Willi

Willia

William

and

B

Bi

Bil

Bill

The solution that I was thinking was an actively updating PatternCount() field definition. The person enters "ill" into the Filter_FirstName field, and since "ill" is found in both "William" and in "Bill" -- both sets of records will show up.

With me?

The way I was going to do that was have the calcContact_NameFirst (part of the relationship between the two tables) look for the FILTER text "ill" within the Contact_NameFirst (Is "ill" in "William", if so make calcContact_NameFirst contain "ill", you’d get the same results for "Bill").

The problem is that I'm hitting a wall when having the related portal fields actively doing calculations (depending on the FILTER data) to determine their contents. They cannot be indexed, therefore they cannot be used to make a relationship for a portal.

So! There are two methods to which I am entertaining this idea.

1. Passive field contents: Keep the relationship and fields as you described to me because it's working just fine. But, then, I would add another custom function...let's call it ExplodeAll() --- this will explode all letters in the name.

W

Wi

Wil

Will

Willi

Willia

William

i

il

ill

illi

illia

illiam

l

ll

lli

llia

lliam

l

li

lia

liam

i

ia

iam

a

am

m

And the same for "Bill" -- so both would match for letters i, il, ill, ll, and l.

So, the code from your original example in the linked post above:

calcPortal_FirstName (calculation, text result) = ExplodedString( Contact_FirstName ) & ¶ & ExplodedKey( Contact_FirstName ) & "¶zzzAll"

And since ExplodeAll() creates the data that ExplodedKey() creates in addition to the permutations above, we would replace ExplodedKey() with ExplodeAll( Contact_FirstName ) to the field definition.

calcPortal_FirstName (calculation, text result) = ExplodedString( Contact_FirstName ) & ¶ & ExplodedAll( Contact_FirstName ) & "¶zzzAll"

Problem with this solution: the calcPortal_Firstname field will probably contain a LOT of data depending on the Contact_FirstName’s contents. It's possible to make this function divide up each word in the string (like how ExplodedString operates) so you have permutations of "William" and "Smith" in addition to permutations of "William Smith". That would help to expand the range of related entries.

2. Um, darnnit, I forget. Haha, I’ll remember in a while. I’ll guess this was Active, which is pretty much what I was describing at the beginning of this post.

The problems with active field contents are: that you cannot create a field that calculates its contents through a relationship; and you cannot index calculation or global fields. Those are the dampers I’m running into.

I’ll continue mulling this over in the meantime, I feel like this can be done. If not, it’s a fun mind-bender in the meantime.

Make sense now or did I manage to make your brain more soup-like? :

[Ender]

1. I came up with just such an algorithm some time ago in this thread:

http://fmforums.com/forum/showtopic.php?tid/149072/

Check out the AnyMatch2 demo.

Somewhere in that thread I also talk about efficiency. It's important to consider how many lines (recursions) would be needed with any of these algorithms, because the more lines needed, the slower it will be to evaluate. The other important consideration is reducing results with too many matches (it may not be very useful to search for "a", as that likely matches a good chunk of the record set). My proposed solution to both of these issues with regards to the ExplodedPermutations() algorithm, is to not exploded sub-strings shorter than 3 or 4 characters. This reduces the number of lines significantly and also reduces "false positives" on short search strings. We get into the performance of these things a little more in this thread:

http://fmforums.com/forum/showtopic.php?tid/151620/

2. Not sure what you're smoking--er, "thinking" here. :