Ok, this seems basic, but I'm having trouble figuring out a way to make it work...

In a layout that displays Rerservations (only the previously selected one), I would like a portal that displays and allows selection of contacts to be included in the reservation. So far, everything works when joining tables like this:

Contacts::ID x SelectedReservation::ID

But as the contacts list grows, it became arduous to scroll and search all the time. What I need now is a "Quicksearch" kind of thing...

So here is what I tried:

In the contacts table:

- added a "SearchString" text calculation field that merges information to look for. the calc goes like:

Company & " " & Name_First & " " & Name_Last & " " & Phone1 & " " & Phone2

so that ideally, it would work whether you search for First Name, Last Name or even phone number or whatever.

- added a global "gSearchString" in which you can enter text you are searching for.

- added a number calculation field "k_one" always equal to 1

- added a number calculation field "C_Display" that will return 1 if the value of "gSearchString" is empty or matches the contents of "searchstring", else returns 0. Formula is:

Case ( gSearchString=""; 1;PatternCount ( SearchString ; gSearchString );1;0)

All this so that both the fields return 1 when what you search for is in the contacts information.

- Then I created a self join where

Self_Contacts::C_Display = Contacts::k_one

At first, It seems that the calculations all work fine as I display values in the portal from Contacts table occurence (but of course, all contacts are displayed and not only the matching ones), but nothing ever displays in the Self_Contacts portal. Why?

I suspected storage to be the reason why it doesn't work (but it seems there is nothing I can do about it), so note that:

k_one is stored, all indexed

C_Display is unstored, and Filemaker wouldn't let me try to store it.

(I also tried to reverse the related fields, but it does not work either. Then I get sometimes all the contacts showing even if C_Display = 0 and sometimes none if GSearchString matches no record... couldn't understant why either)

So what should I do? I am sure you guys have seen someting like this before and can help.

And there is also this other minor problem: for now, the calculations and portal contents only seem to update when I click somewhere on the screen, outside the fields/portals. Ideally, I would like it to update as I type in the gSearchString field... I know I can use script triggers (on keystroke) to do that, but then what do I need to tell the script to do? Commit Records? It doesn't seem to work well, as it gets the selection out of the field every time I hit a key... any Idea?

Note that I also tried another idea, like: "change layout", "Perform Find" and "go to original layout" kind of script, but Ideally, all this should be done without quitting the Reservations layout, and it didn't work either, as all the contacts were still shown in the portal from Reservations layout even if the records from Contacts layout were filtered correctly... I didn't understand this one either.

Yet I was able to make from scratch things that seem much more complicated... I can't believe I get stuck on such a detail, please help!!!

http://filemakerinspirations.com/2009/01/google-like-search-through-relationship-filtering/

Good enough. This works now!

But I still can't understand why my way didn't... and the portal still doesn't update as I type the search criteria.

Anyways, though not perfect, this solution works and it should do the thing.

Thank you!

ah, other links I missed there contained the answer for updating as I type, so now it IS almost perfect...

Unlike the PatternCount function though, this way of defining calculations doesn't find the same records. For example, suppose there is a record named "Martin" whose phone number is "123-4567", it will be found if you type "Mart" in the search field, but not if you type "rtin". Neither will it find it if you search for "4567" but it will when you search for "123-".

Kind of frustrating.

Edited November 19, 200916 yr by Guest