Portal multi-criteria logic

[GrantSymon]

Hi,

I want to create a multi-criteria portal and I'm having trouble with the logic.

I have 3 ordinary text fields in a DB. I want to use global fields based on their indices to 'constrain' or 'filter' the portal.

IOW ... I want to enter a word in the 1st global and see all matching records in the portal. When I enter a word in the 2nd global, I'd like the portal to 'constrain' the matching records and the same for the 3rd global.

That's hard enough for me ... and I haven't managed it yet ... BUT!

I'd also like it, if I could leave the 1st and/or 2nd globals blank and find matching records based on the 2nd and/or 3rd fields.

Can anyone give me an idea of how I should go about this?

Many thanks,

Grant

[Ender]

Sounds possible, but we'll need to know more about what you're filtering on if you want specifics.

[GrantSymon]

Hi Ender,

thanks for the reply!

Sorry ... perhaps I wasn't clear enough ...

I will use the indices from the ordinary text fields to make 'dynamic' value lists for the global fields. So each ordinary field has a corresponding global field, using its index.

I didn't want to get too specific in case it made people think too narrowly. IOW, maybe this isn't such a good way to go about it and if I don't mention it, then perhaps someone will suggest something simpler/faster/better.

Grant

[Ender]

Portal filtering with globals as parent keys is not uncommon and is easy enough to do. But rather than resort to abstract names like "Field1", "Field2", I thought you might provide us with a general description of the kind of data you're filtering. It makes the discussion easier if it's about something tangible.

It's also important to know if you want to filter by a complete text string, or if you want to allow for partial matches on text (whether "David" should match only the "David"s in the related table, or also the "Davidsons".)

[GrantSymon]

Hi Ender,

okay ... I tend to try to simplify as much as possible when I'm asking this sort of question. I often find that full descriptions of people's databases are confusing. I find them hard to follow. However ...

I have a DB of names and addresses, with some further info categories.

1 is Region, 2 is Category and 3 is Job_Description. These are the 3 fields whose indices I will use for the global fields.

As for matches. Partial would be best.

Grant

[Ender]

So the general idea for portal filtering is to have your globals on the parent side match up with the real field on the child side. I still don't know your structure, so I'll just use Interface as the parent table and Name as the child table. So the relationship for this basic setup would look like:

Interface <=> Name =

Interface::gRegion = Name::Region

AND Interface::gCategory = Name::Category

AND Interface::gJobDescription = Name::Job_Description

BUT, you had two additional things that will require you to build calculated keys. First, the requirement that if a filter field is left blank, all records should match for that filter. This can be done by using a multi-line key on both sides of the relationship. On the Interface side, the keys would be:

cRegionFilter (calculation, text result) = case(isempty(gRegion); "All"; gRegion)

cCategoryFilter (calculation, text result) = case(isempty(gCategory); "All"; gCategory)

We'll save the JobDescription filter for the next part.

Anyway, on the Name side, you'll need multikeys to match both "All" and whatever is in the field:

cRegionKey (calculation, text result) = Region & "¶All"

cCategoryKey (calculation, text result) = Category & "¶All"

Using these calculated filters and keys allows you to have a blank entry in one of the globals, and all the Names will match for that filter.

Now back to the Job Description. While all of your filters could be built allowing partial matches, this is the one that made the most sense to me, as the others could be simple pull-down lists (the Job Description could have a wider range of entries, and therefore it may not be desireable to use a pull-down menu to select them.)

There are two methods for allowing partial matches. One uses a range relationship on the text, and the other uses multi-keys. The range method works well if the fields you're matching on contain simple numbers, dates, or one-word entries, like First Name, Last Name, Middle Name, etc., but it doesn't work well if the field contains text with multiple words, like Full Name, Address, and Description. You can download the Type Ahead demo on databasepros.com to see how the range method works.

For multiple word fields, where a partial filter entry should match on any of the words in the field, you'll need multi-keys. A simple "exploded key" takes the text and adds a line for each sub-string, like this:

"Grant" becomes:

Grant

Gran

Gra

Gr

G

This allows an entry in the filter field of "gr" to match records containing "Grant" and "Groovy".

This multi-key could be constructed entirely within a calculation on the match side, but since you have FM 8 Advanced, there's a better way: Custom Functions. There are several custom functions that do this simple exploded key, here's one:

http://clevelandconsulting.com/support/viewtopic.php?t=49

Unfortunately, this exploded key still won't do partial matches on multi-word fields. If you tried to explode "Vice President", you'd end up with:

Vice President

Vice Presiden

Vice Preside

Vice Presid

Vice Presi

Vice Pres

Vice Pre

Vice Pr

Vice P

Vice

Vice

Vic

Vi

V

Which is still fine for matching "Vi", but doesn't match "Pres".

For this, you need a custom function to explode each word in the match field, like this:

Vice

Vice

Vic

Vi

V

President

Presiden

Preside

Presid

Presi

Pres

Pre

Pr

P

Fortunately, there are CFs that people have built that do this. Here's one that I like:

http://www.briandunning.com/cf/36

One thing that this doesn't handle is the possible filter entry of "Vice Pres". So my recommendation is to use both the ExplodedString() and the ExplodedKey() CFs to build the key on the match field. The field definition would look like:

cJobDescriptionKey (calculation, text result) = ExplodedString(Job_Description) & ¶ & ExplodedKey(Job_Description) & ¶ & "zzzAll"

That last bit will allow the All match for a blank entry in the filter field. The corresponding filter calc in the Interface table will be:

cJobDescriptionFilter (calculation, text result) = case(isempty(gJobDescription); "zzzAll"; gJobDescription)

Okay, now with the calculated filters and calculated keys, the relationship will look like this:

Interface <=> Name =

Interface::cRegionFilter = Name::cRegionKey

AND Interface::cCategoryFilter = Name::cCategoryKey

AND Interface::cJobDescriptionFilter = Name::cJobDescriptionKey

Anyway, I hope that makes some sense. You'll have to decide just how much of that you'll need based on what your actual fields contain.

[Edit:] Important note: these multi-keys add overhead to the table on the match side. You should avoid using them on fields containing long text strings.

Edited November 19, 2005 by Guest

[GrantSymon]

Hi Ender,

thanks for taking the time to give me such a full and helpful reply!

I have the portal working pretty much the way I want (too much fiddling with UI while I'm at it). I don't have many possible entries so I stayed with the simplified version. I was stumped for a while, with not 'un-checking' the 'Do not evaluate if all referenced fields are empty', but I finally realised why the calcs weren't working.

My intention is to use a technique I've used once before, with FM6, to get the index from one field available for another, but I imagined that this might be easier now in FM8 ... however, I can't find anything to suggest it is.

Do you know of any simple way of accessing a given field's Index, for pasting into a different field ?

Grant

[Ender]

My intention is to use a technique I've used once before, with FM6, to get the index from one field available for another, but I imagined that this might be easier now in FM8...

I'm afraid I don't understand what you mean. Maybe you can describe what you need.

BTW: I like your portrait photography. :clap:

[GrantSymon]

Hi Ender,

well .... My intention, using the relationship you've helped me with, is to make the index for the normal fields available to the global fields used for the Portal.

IOW, The DB has 3 fields, Region Category and Job, which are used on one side of the relationship, with the global fields on the other. Rather than having to remember what is in those fields, I intended to show the index from them, when clicking for entry into the corresponding global field. (I'm actually surprised that this isn't readily available in FM8. 'Get Index' or something).

I cobbled together a way of doing this back in FM6 and I suppose it's perhaps less of an issue now that I can open up a new window, with related value-lists in it, thus simulating the index. Hmmm, might be simpler and more elegant in the end.

Any suggestions though ... can it be done simply?

Grant

(P.S. ... thanks for the compliments on my pix) :)

[Ender]

Oh, I see. Yes use the related value lists.

[GrantSymon]

Hi,

a looong time ago (this thread is pretty ancient) Ender helped me out a great deal with this portal filtering solution. Unfortunately, I've 're-structured' my files slightly, by separating Contacts from Companies (I now have separate tables for them) and consequently, I've 'broken' my portal filtering because 2 of the 4 filtering criteria (fields) now reside in a related file.

This is what I have :

Region, Category : in the Companies file

Job, Work/Play : in the Contacts file.

Question :

Is there any way to have my portal filtering work with 2 of the 4 fields being in a related file?

I guess I could have some kind of script to automatically copy the info into a field in the Contacts file, but it seems like a terrible 'kludge'. Is there a better, more elegant and less prone to error, solution?

Many thanks for any help/info!!

Grant