Dynamic Portal Filtering While You Type

Using FileMaker 11’s new Portal Filtering feature, you can now show filtered views of related records in portals without having to define new relationships. What’s better than that? How about dynamically filtering your portal data while you’re typing letters into your search field! With a very simple script (4 steps), a custom function and a script trigger, you can search data in your FileMaker portals the same way you search for music in iTunes!

Place a global search field above your portal. Add portal filtering that uses a custom function you will create. Add a script trigger calling a script that simply refreshes the page. And, you have a dynamically filtered portal that updates as you type.

The project consists of 5 basic steps:

  1. Create a global search field.
  2. Create the custom function (copy and paste).
  3. Set the portal filter calculation (copy and paste with some modifications).
  4. Create the script (4 script steps).
  5. Set a script trigger.
BTW: Don’t let the length of this article intimidate you too much. There is a great deal of technical description of the custom function which you can safely skip if you choose. The custom function can easily be copied with no understanding of how it works. However; reading the description may be useful to you if you wish to make changes or just get a better understanding of custom functions and recursion.

Step One: Create a Global Search Field

Note: This step is the same as Step One in my article, Live, As-You-Type Search in FileMaker, which discusses a similar technique for list views.

Create a text field in any table. In the field options, set the new field to use global storage as shown in the figure below.

Place a global search field with a script trigger in the header of your list layout for live search-as-you-type functionality

Under the storage tab in the field options, select the "Use global storage (one value for all records)" check box.

Tip: To keep your data tables clean and better organized, I recommend having a separate table to hold global fields that are not directly associated with a particular table. Global fields can be accessed from any context whether or not a relationship exists between the tables.

Step Two: Create the Custom Function

To achieve the affect we are looking for, we will set a portal filter which will use a custom function that we will write that will find records that have words that begin with the search criteria typed by the user. The reason we need a custom function is that we will need to iterate (loop) through each word in the fields we want to search and look for a match on any word entered as search criteria. For instance, if the user types “port art”, we want to find records with the text “article about portal filtering”, but not “airport art“.
Our custom function will take three parameters:

  • needles: This is the search criteria (the needles we are looking for in the haystack).
  • haystack: This is the text we are searching. It will most likely be the contents of one or more fields.
  • start: This is a number representing the word within haystack where we will start searching. When calling this function, we would almost always pass the number 1. This will be used to iterate through all the words in the haystack.

The function will return true if, for each word in needles, there is at least one word in haystack that begins with that word. Otherwise, it will return false.

The function, which I am calling FindWordPartsInText, is as follows:

If (
	start > WordCount ( haystack ) ; 0 ;
	Let ( [
		needle = LeftWords ( needles ; 1 ) ;
		word = MiddleWords ( haystack ; start ; 1 )
		];

		Case (
			Left ( word ; Length ( needle ) ) = needle ;
			If (
				WordCount ( needles ) < 2 ; 1 ;
				FindWordPartsInText ( RightWords ( needles ; WordCount ( needles ) - 1 ) ; haystack ; 1 )
			) ;
			FindWordPartsInText ( needles ; haystack ; start + 1 )
		)
	)
)

At this point, you can copy and paste the above function and skip to step three if you wish. Don’t forget to add the three parameters listed above in the order listed and name the function FindWordPartsInText. If you give the function a different name, you will need to change the two calls to that function in the function text above.

If you are curious to know just how this function works, read on…

Function Details

In short, we start by comparing the first word in the needle with the first characters in the first word in haystack. If that match fails, we move on to the next word in haystack. Once a match is found, we move on to the next word in needle. If a match is not found and we reach the end of the haystack, we return 0 or false. If we reach the last word in needle and find a match, then we return 1 or true. This is done recursively by calling the function within itself.

Quick Introduction to Recursion

A recursive function is a function that calls itself in order to iterate or loop through a problem. There are two main features that are necessary for an effective recursive function:

  1. A base condition that returns a simple value and does not call itself;
  2. Defining condition that breaks down the complex problem into simpler problems, then calls itself with simpler values leading it closer to the base condition.

The defining condition must always lead to the base condition eventually in order to avoid infinite recursion.

Our function, FindWordPartsInText, is somewhat complex in that it has double recursion. It will loop through each word in haystack for each word in needles.

There are two base conditions:

  • The first base condition is start > WordCount ( haystack ). Which tells us that we have reached the last word in haystack, and did not find a match, so we return false.
  • The second base condition is when both Left ( word ; Length ( needle ) ) = needle and WordCount ( needles ) < 2 evaluate to true. This indicates that we have found a match and reached the last word in the search criteria (needle).

There are also two defining conditions:

  • The first defining condition exists when we set our Let variables, needle (note needle is different than our parameter needles) and word. Then if a match is not found, we make the recursive call with the parameters needles, haystack, and start + 1. By adding 1 to the start parameter, we are telling the function to look at the next word and getting closer to the base condition of start > WordCount ( haystack ).
  • The second defining condition exists when we find a match Left ( word ; Length ( needle ) ) = needle, which tells us that the first letters in the word we are searching are the same as the the search criteria. If this evaluates to true, and there are still words left in needle (WordCount ( needles ) < 2 evaluates to false), then we make the recursive call with the remaining words in needles, thus bringing us closer to our second base condition.

Step Three – Set the Portal Filter Calculation

Quick Introduction to Portal Filtering

FileMaker introduced a new feature in FileMaker 11 which allows us to specify a calculation to filter the records that appear in a portal. Portal Filter ScreenshotWhen selecting the check box to “Filter portal records” or clicking the “Specify…” button, we are presented with the standard calculation window. The calculation entered will be evaluated as true or false for each related record that would normally appear in the portal. If true, the record will appear, if false, the record will not appear. A simple example of how this can be useful is in a Task Management system where you want a portal in the project detail layout which shows only open task records. In this case, the calculation might look something like this: tmProject_tmTask = “Open”.

To set up the portal filter, simply open the calculation window for your portal filter mentioned above and enter a simple calculation that calls the custom function we created in the previous step:

IsEmpty ( Globals::SearchField )
or
FindWordPartsInText ( Globals::SearchField ; RelatedTable::Field1 & ¶ & RelatedTable::Field2 ; 1 )

You will replace Globals::SearchField with your global search field that you created in Step One, and replace the RelatedTable::Fieldx text with fields from the table in your portal. You can have as many fields there as you want, just join them with & ¶ &.

Step Four – Create the Script

Our script needs to do three things: commit, refresh and put the cursor back where it was found. It will look something like this:

Set Variable [ $cursorpos ; Get ( ActiveSelectionStart ) ]
Commit Records/Requests [ No dialog ]
Refresh Window [ Flush cached join results ]
Set Selection [ Globals::SearchField ; Start Position: $cursorpos ; End Position: 0 ]

Of course, your global search field will take the place of Globals::SearchField.

Tip: Maintaining performance and responsiveness is critical in this script since it will be run at each keystroke and will not take advantage of FileMaker indexing. To help with this, you may want to limit the amount of data that is being searched, and consider the “Delayed Search For Fast Typing” technique described below.

Step Five – Set the Script Trigger

In layout mode, right click on the search field and click “Set Script Triggers …” A dialog will appear:

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

In the script trigger dialog, select the action (or event) that you want to trigger the script, and select the script to be triggered.

Select the OnObjectModify action which will run the specified script any time text is entered in or deleted from the field. Click Select and choose the script that you created in Step Four.

FYI: For the purposes of this script, OnObjectKeystroke would give us the same result. Using OnObjectKeystroke would also give you the flexibility to refine your script to handle different keystrokes differently: i.e. enter or return keystrokes. However, OnObjectKeystroke will not trigger when using the Insert Calculated Result script step.

…And You’re Done!

Now anyone can simply begin typing in the field and their portal contents will be refined as they type without even click a button.

Do you want to make it even better? . . . .

Enhancements

Delayed Search for Fast Typing

One of the drawbacks of having scripts and calculations run every time you type a character is that it can cause serious lag time while you are typing. Especially for fast typist, this can be very annoying, especially when doing searches on large data sets and/or working over a slow network connection.

This can be easily remedied using the Install OnTimer Script script step introduced in FileMaker 10. Simply create a new script that sets an OnTimer script to call the script you made in step four (we’ll call it “Task Filter Utility”). The script will simply have one step:

Install OnTimer Script [ "Task Filter Utility" ; Parameter: ; Interval: .3 ]

Now call this script from the script trigger instead of calling the Task Filter Utility script directly. Every time a user types another character, the script will be called again, thus resetting the interval. So the Task Filter Utility script will not be called until the user stops typing for 0.3 seconds. You can of course adjust that interval. I have found .3 and .4 to be good numbers.

One more thing!!! You must also add a script step to the Task Filter Utility script. Otherwise it will run over and over again every .3 seconds:

Install OnTimer Script [Interval: "" ]

Call the Install OnTimer Script step with no script or interval specified will halt the timer.

Multiple Search Criteria

What if you want to use multiple search fields? You can do that too!

To accomplish this, the filter calculation and script become slightly more complex. The calculation will need a condition for each field joined by the “and” operator, and you will want to set up the script to take a parameter indicating the field name. This way it will know which field to return to once it has committed and refreshed.

To continue reading about this, see Dynamic Portal Filters With Multiple Criteria where I walk through this in detail.

If you have any questions or comments feel free to post a comment below or contact me directly.

72 Comments

  1. Jake A.
    Posted October 5, 2010 at 4:09 am | Permalink

    Thank you for this wonderful tip. I will try this on my applications. =)

  2. Glen McKeown
    Posted October 14, 2010 at 9:36 am | Permalink

    Can you set up these tips to be saved/printed as a simple pdf. I can then transfer them to my iPad, so I can then see the tip as I work it through on my iMac – and I don’t have to waste paper

  3. Posted October 19, 2010 at 8:24 am | Permalink

    Great work, thanks!

  4. JB
    Posted October 20, 2010 at 5:25 pm | Permalink

    Thank you. Great. Can you please explain what the relationship graph looks like in this example?

  5. Posted October 20, 2010 at 6:00 pm | Permalink

    @JB – In the specific example I show in the screen shots I have a project table and a task table. The project table has a primary key field, the task table has a foreign key field which matches the primary key in the project table. It is very simple. The specific relationship is not particularly relevant with this method. All that is necessary is that there are two tables that have a valid relationship to each other. The filtering is performed by the portal filtering feature, not the relationship.

    @Glen McKeown – Thanks for the great idea. I will consider compiling a PDF with these tips in the near future.

  6. JB
    Posted October 20, 2010 at 10:58 pm | Permalink

    ah. okay, thank you so much!

  7. JB
    Posted October 21, 2010 at 11:43 am | Permalink

    any chance you could make a simple sample file for download?

  8. JB
    Posted October 21, 2010 at 2:04 pm | Permalink

    ah ha! i did not have the “Flush cached join results” checked!

  9. Peter Wolf
    Posted October 29, 2010 at 3:35 pm | Permalink

    I am from Germany. I have a problem, it does not run. Can yuu send me the databases.

    Thank You Peter

    Dynamic Portal Filtering While You Type

  10. Posted November 5, 2010 at 12:16 pm | Permalink

    @JB – Glad you figured out the problem. I am working on putting together a sample database that will have an example of this tip and other tips and techniques discussed in other articles.

  11. Kevin Mark
    Posted November 14, 2010 at 1:16 pm | Permalink

    Thank you for this. It’s really useful. However, I am puzzled as to why, if I type in “2” entries with “2” and “27” appear, but if I type in “7” only those with “7” appear, “27.” I have only tried this with a couple of examples…

  12. Kevin Mark
    Posted November 14, 2010 at 1:18 pm | Permalink

    I should have added that the numbers referred to were in text fields.

  13. Plonq
    Posted November 23, 2010 at 9:18 pm | Permalink

    Hi, I tried this after another technique seemed quite slow. However whenever I type a character int he search box, it freezes for about 4 seconds which doesn’t seem right. Is there something I’m missing?
    I’m quite new to FM.

  14. Anthony
    Posted December 28, 2010 at 4:12 am | Permalink

    Hi Danny,

    Thank you so much for providing this tutorial. It works great.
    I was wondering, is there any way to have the portal to show no record when Globals::SearchField is blank?

    I’ve tried removing “IsEmpty ( Globals::SearchField ) or” in Step Three but it does not work.

  15. Posted December 28, 2010 at 12:19 pm | Permalink

    @Kevin Mark – By design, the FindWordPartsInText function should return true when the beginning of any word in the haystack is the same as the needle. So, 2 should match 2, 27, 200, 2abc, etc. Adding additional characters will narrow your search results. So, from the set above, typing 27 will only match 27. Typing 7 should not match on 27.

    @Plonq – I would recommend that you implement the delayed search described above and take a look at the Performance Considerations section on in the follow-up article Dynamic Portal Filters With Multiple Criteria. Since the search is performed at every keystroke, it is important that you keep your search criteria as efficient as possible.

    @Anthony – To get an empty search result with a blank search field, add a “not” (without quotes) in front of the IsEmpty statement and change the “or” to “and”.

  16. Eldad
    Posted January 6, 2011 at 10:54 am | Permalink

    Instead of “refresh window” you can just use
    set field[portalField ; portalfield] – this refreshed the portal automaticaly

    It is much faster and no flickering !!!

  17. gert
    Posted January 17, 2011 at 4:08 am | Permalink

    thanks for this script.
    I must be doing something wrong.
    Everything works fine except that…
    this only works if I type the search words in reverse.
    Any idea what is going on?
    Gert

  18. SwissMac
    Posted February 12, 2011 at 8:58 pm | Permalink

    Has someone hacked your site? I don’t understand this bit in Stage 2:

    “In short, we start by comparing the first word in the needle with the first characters in the first word in haystack. If that match fails, we move on to the next word in haystack. Once a match is found, we move on to the next word in needle.”

    Shouldn’t this read “letter” or “character” and not word?

  19. Posted February 27, 2011 at 8:59 am | Permalink

    hello,
    good script nice and great idea but iam from india please send full database ihav got some error

  20. Posted March 26, 2011 at 7:04 am | Permalink

    Dude you are awesome! Thanks for this site. I will visit it regularly. How about creating websites within the Filemaker framework?

  21. Posted April 6, 2011 at 2:26 am | Permalink

    Thanks Danny for a great tut – I was surprised at how easy this was with your help!

    Just wondering though, in your screen grab you have an ‘x’ presumably to cancel the search string, what script have you put on the ‘x’ to make it clear the search and return the portal to original view?

  22. Posted April 6, 2011 at 6:54 pm | Permalink

    @Eldad – Thanks for the tip! Great idea, especially for Window’s users where screen flicker is an issue.

    @gert – I’m a bit perplexed as to why it would only work for reverse searches. Perhaps the custom function was not copied exactly or the parameters are in the wrong order. Let me know if you figure it out or have more hints.

    @SwissMac – That statement says exactly what it should: “comparing the first word in the needle with the first characters in the first word in haystack.” We are actually comparing the first word (term or group of non-space characters) that was typed in with the first characters in each word of the field content. So if “foo” was typed, we check the first 3 letters of each word in the field to see if they are “foo.”

  23. Posted April 6, 2011 at 7:01 pm | Permalink

    @R.K – I have no sample database to send yet. Though one is near completion. If you want to send me a copy of your database (perhaps a clone if there is a lot of data), I might be able to give you a hand.

    @Mr. Shannon Moss – Thanks!! As for using FileMaker as a data source for websites, I am not a fan. I know I might tread dangerous ground when I say this, but in my experience, FileMaker server is fine for internal websites, but just does not perform well for public websites: way too much latency. I tend to use MySQL as the data engine for my websites and ODBC with shadow tables in FileMaker for the administrative functions and/or data synchronization.

  24. Posted April 6, 2011 at 7:03 pm | Permalink

    @Paul – Simple answer: no script, just a single-step button call
    Insert Calculated Result [Select; Globals::SearchField;""]
    This clears the search field and causes the trigger on the field to fire.

  25. Erick Bueno
    Posted April 28, 2011 at 4:16 pm | Permalink

    I see that the relationship it is important, because if your SearchField is not related to the field that contain the info you are looking for, this is not displayed, and also you need to enter the complete sentence.

    Is there anything that I did wrong?

    Thanks

  26. Pete Minich
    Posted May 4, 2011 at 12:00 pm | Permalink

    I was wondering if there is a way to calculate the number of records found using this process?

    I am using this method to search through a list of products to add to a contract. I’d like the user to be able to see if there are 10 qualifying items or 100 with the idea that if it is higher they would add to the search terms instead of scrolling through the list.

  27. Pete Minich
    Posted May 4, 2011 at 12:08 pm | Permalink

    Found my own answer –
    Create a summary field in your child table that counts a key field.

    Then go back to your layout with the filtered portal on it. Copy the portal and change it to a 1 line portal. Resize to your needs and then put the newly crated counting summary field in the 1 line portal.

  28. Erick Bueno
    Posted May 19, 2011 at 12:16 pm | Permalink

    I think this trick does not function in Web. Which could be the best solution if I want to implement in a DB that it will publish using “Instant Web Publishing”?

    Thanks

  29. Shaun H.
    Posted May 25, 2011 at 3:40 pm | Permalink

    Danny,

    This is a great process but when I implemented it, it runs dog slow. It’s chugging along, filtering records. My question is: can this custom function be called by script somehow so that the portal isn’t always trying to filter? I messed with it some but it as I entered the portal fields and left them, it would “think” for a moment, causing a pause. I know my users and this will aggravate them. So I guess I’m asking if there’s a way to filter on demand. Rather like using Google search without the live search. I looked at the other tutorial about filtering portal results, but there were so many limitations.

    Thanks for all this!

    Shaun

  30. Posted June 7, 2011 at 2:29 pm | Permalink

    @Erick Bueno – The relationship between the search field and the portal table should be irrelevant. The search field should be a global field, and therefore, accessible from anywhere. As for complete sentences, there could be a problem with the custom function and/or the portal filter calculation. I couldn’t tell you much more than that without seeing them.

    This solution would definitely not work with instant web publishing. It’s just not possible to implement any “as-you-type” solution with IWP, the user would have to push a button to execute the search. If you are OK with that, any one of the search methods on this site should work. You could do an as-you-type search with custom web publishing and JavaScript, but that would be an entirely different method.

  31. Posted June 7, 2011 at 2:47 pm | Permalink

    @Pete Minich – Thanks for sharing your solution. I implemented it and it works great. I also put a count calculation in the current table so I could display “Showing x of y.”

    @Shaun H. – If you haven’t already, you will definitely want to implement the Delayed Search that I mention toward the end of the article. Also take a look at the next article Dynamic Portal Filters with Multiple Criteria. Toward the end of that article under “Performance Considerations,” I mention a couple other things you can do to make it faster. Also, if are using complex, unstored calculations in your filter, consider making some adjustments so they can be stored, such as using lookups or auto-entered calculations if possible.

  32. Garry N
    Posted August 15, 2011 at 4:53 pm | Permalink

    Very nice. I look forward to viewing this interface on an iPad instead of using the portal scroll bar. thank you so much for sharing.

  33. Uncahuab
    Posted October 13, 2011 at 2:26 pm | Permalink

    Great solution! Thanks.

    I’ve implemented it on three layouts, two of which is simply searching on the last name. Seems to work just fine. The 3rd application is on a project name field and the results seem a bit odd.

    Search works perfectly when the input is the first letters/numbers of the first word. When I input into the search field the first letters of a word in the project name THAT IS NOT THE FIRST WORD the results are erratic. Here are some examples:
    type in “c” (no quotes); it returns:
    Crest Project Fund
    China 2009
    Uganda Covenant Mercies Project
    (there are several projects with “c” as the first letter of a word other than the 1st word that were NOT returned.)

    type in “p” (no quotes); it returns:
    Test Project 1
    Test Project 2
    Uganda Covenant Mercies Project
    (notice it did not return Crest Project Fund)

    Now here is an interesting outcome: If I type in the first character of the search field a special character (e.g. ~!@#$% etc.), the results returned are always perfect and complete! For example, if I type in “~c” (no quotes); it returns:
    2011 China
    Crest Project Fund
    2010 Guatemala Construction
    2011 Guatemala Construction
    2011 Haiti Crisis Response
    Amigos de Jesuscrito Christmas Donations
    China 2009
    Uganda Covenant Mercies Project

    Any ideas why I’m seeing this behavior?

    Thanks.

  34. Posted October 14, 2011 at 10:25 am | Permalink

    @Uncahuab – I have no idea why you’re seeing that problem. I tried testing with the data you listed and couldn’t replicate anything like it. As for the special character thing, in general, they are completely ignored. Since the function uses LeftWords and MiddleWords, such characters are stripped out and treated as word separators. So, there should be no difference between “p” and “~p”.
    I’m grasping at straws here, but perhaps it’s a character encoding issue. Without seeing your database, I’m at a loss. Feel free to send me a copy, and I’ll take a look at it for you.

  35. Uncahuab
    Posted October 14, 2011 at 11:37 am | Permalink

    Danny, problem solved…I was using the same global as both the relational key to the portal data and as the search field. Using a different global as the search field has fixed it.

    Thanks for being a “catalyst.”

    Unc

  36. Todd Gold
    Posted October 26, 2011 at 8:55 pm | Permalink

    I found everytime I tried to copy and paste the function I received a message that there were too many arguments. It was copied 100% correctly…I tried numerous times. What could I have done wrong?

    Thanks in advance for your reponse.

    Todd

  37. Posted October 26, 2011 at 8:58 pm | Permalink

    @Todd – It sounds like you may have forgotten to add the three parameters to the function: needles, haystack, start. If that’s not the case, can you tell me what part of the function gets highlighted after you receive the error?

  38. Todd Gold
    Posted October 27, 2011 at 7:44 pm | Permalink

    I sent an email yesterday that I could not get this to work, but eventually it did! However, I get screen flickering after entering each letter. How can this be fixed?

    Thanks in advance for your prompt reply!

    Todd

  39. Todd Gold
    Posted October 27, 2011 at 9:17 pm | Permalink

    Eldad mentioned a solution to this flickering, but I do not understand how to add that step to the script. WHat I am doign is not working. Can anyone help???

  40. Posted October 27, 2011 at 9:30 pm | Permalink

    Eldad’s solution uses the Set Field script step. Basically, you are setting any field that exists in the portal with itself.
    (i.e. Set Field [ RelatedTable::Field1 ; RelatedTable::Field1 ])
    This will refresh the portal without the screen flicker that the Refresh Window step causes in Windows OS. I haven’t actually tried this method yet as my environment is almost exclusively Mac, as are most of my clients’. The one drawback is that, if you are tracking modification dates and users on your records, this step will cause FileMaker to update the modification date and user for the first portal record.

  41. Todd Gold
    Posted October 28, 2011 at 6:57 am | Permalink

    Thanks Danny. I did not see your other post the other night in reponse to a question, so thank you for that. I have repalced the Refresh Window step with the Set Field step, but that stops the incremental search? I understand from your post that you have not tried this because you use a Mac. Any thoughts?

  42. Todd Gold
    Posted October 28, 2011 at 7:00 am | Permalink

    NEVERMIND!

    Once again, fortuante enough or lucky enough to have found my own solution. I needed to do the set field step on the portal table occurence, not something in a table twice removed.

    Lastly, on the multiple field search, how can the filter work to add a date range or a number field?

    Thanks for all your ideas and your reply!

  43. Todd Gold
    Posted October 28, 2011 at 7:48 am | Permalink

    I hat to bombard you only to answer my own questions, but “ex-nay” on the “post-ay”. I figured it out…felt a bit stupid it was so easy. I also applied the space filters to the mittor record count portal…works like a champ!

    Now I’m testing date ranges….don’t think that will be too difficult….

  44. fastserv
    Posted December 28, 2011 at 1:59 pm | Permalink

    hi,
    I have just implemented this solution, and I really like it.
    I have just a question.
    is it possible to search with the “*”?
    like the standard find.
    for exeample:
    I have the following records in the portal:
    aaaff
    bbbff
    cccff
    I would like to type in the search filed the following:
    *ff
    and the result should be:
    aaaff
    bbbff
    cccff

    Thanks.

  45. Posted December 28, 2011 at 10:17 pm | Permalink

    @fastserv – The short answer to your question is, no – not as described above. It is theoretically possible with added complexity to the custom function. Unfortunately, the level of complexity is beyond that which I can cover thoroughly in a post comment. It would require another layer of conditionals in which, in the event of a wildcard character, would first attempt to match whatever comes before the wildcard character, if if it succeeds, do a simple search (possibly with the Position function) for whatever comes after the wildcard anywhere after the first match. If nothing comes before the wildcard character (as in your example above), it would simply search for what comes after it.
    Hope that helps. If you do decide to dive into this. I’d be interested in your result :). If I can find a little time in the near future. I may try it myself and let you know what I come up with.

  46. fastserv
    Posted December 29, 2011 at 2:39 am | Permalink

    @Danny – thank u for the response. I’ll try to implement it, but I need to learn custom functions first. I’ll let u know if something good come out.
    Please let me know if u do something.

  47. fastserv
    Posted December 30, 2011 at 6:26 am | Permalink

    I have done it.
    But not in a so stylish way. And maybe it is also buggy, need to check.
    If I have time I’ll try to do it in a better way and post here my method.
    Btw I have done 2 custom funciont:
    1 is exactly ur, the other one is like ur, but start to search from the right (end of the word).
    Than in the filter method I have combined the functions.

  48. Posted February 8, 2012 at 6:03 pm | Permalink

    I have copied the custom function and pasted it into the function area, and have replaced the ‘start'(=1) ‘needles’ (=Globals::gServicesCategory) and ‘haystack’ (Services::Category) with the appropriate value or fields but cannot exit the custom function as it keeps telling me there are too many functions listed in the following parameter:

    FindWordPartsInText ( RightWords ( Globals::gServiceCategory ; WordCount ( Globals::gServiceCategory ) – 1 ) ; Services::Category ; 1 )

  49. Posted February 8, 2012 at 9:27 pm | Permalink

    @Bill – The custom function should be pasted as is. Do not replace any of the text within the function. The parameters start, needles and haystack then need to be added to the custom function parameter list.

  50. Posted February 9, 2012 at 4:57 pm | Permalink

    Will this solution work using an intermediate join table? [e.g. Invoices >> Join Table << Services] where I am trying to filter the services records and display them in the portal on the Invoices layout?

  51. Posted February 9, 2012 at 7:54 pm | Permalink

    @Bill – This solution will work with any valid relationship. The nature of the relationship is irrelevant. As long as you can view records through the relationship in a portal, you can use this method to filter them.

  52. Posted February 9, 2012 at 8:05 pm | Permalink

    I must have an error someplace then, as I cannot get it to work properly….

  53. Lucius
    Posted February 14, 2012 at 6:51 pm | Permalink

    Ha ha. After step 5 you say “And Your Done.”

    Here at Hogwarts, we learn that Your is a personal pronoun.

    Perhaps you meant to say “And You’re Done.”

    You see, You’re is an abbreviation for You Are.

    And You Are Done!

    Reparo !

  54. Posted February 14, 2012 at 8:58 pm | Permalink

    Thanks Mr. Malfoy. You’re the second person to catch me on that one (notice my application of your lesson in this sentence). I’m usually a stickler for grammar myself, but when writing a 2000 word technical article, it’s almost inevitable that I’ll let one or two grammatical errors slip. Fortunately, I have keen readers. Saves me the cost of hiring an editor. ;)

  55. Oliver
    Posted February 28, 2012 at 5:34 am | Permalink

    Have you ever tried it with FMGO on an iPad? The script always stops after i entered one letter.

  56. Posted February 28, 2012 at 9:55 am | Permalink

    @Oliver – In FM Go, it isn’t quite as smooth. Every time you pause in typing, it will exit the field, run the script, then go back to the field. It should still work fine, just not very slick.

  57. R o b 'jesus Land Tidd' lewis
    Posted February 29, 2012 at 8:17 am | Permalink

    W H A T ? NO RELATIONSHIP IS NEEDED BETWEEN TABLES AND FILES FOR ACCESSING GLOBAL FIELDS ???

    Tip: To keep your data tables clean and better organized, I recommend having a separate table to hold global fields that are not directly associated with a particular table. Global fields can be accessed from any context whether or not a relationship exists between the tables.

  58. Posted February 29, 2012 at 9:38 am | Permalink

    That’s right Rob.

  59. steveald
    Posted March 9, 2012 at 11:58 am | Permalink

    I was glad to find your tutorial as it appears to accomplish exactly what I want. However, I fear my years of FM experience left me having to look up procedures for a few of your 5 basic steps. As a result, my implementation does not work. A few things that may be at fault include:
    1) The Portal I set up does nothing. I’m sure I must be implementing it incorrectly somehow. But I can’t even enter text into it. Perhaps it’s not even on the correct layout.
    2) My Script does not match yours exactly. The first line says Set Variable [$cursorpos; Value:Get ( ActiveSelectionStart )]. I can’t seem to get rid of “Value:”.
    3) My basic understanding of the processes involved leaves something to be desired. Do you have tutorials that I could follow to help fill in the gaps? Could you either send me the completed database you created for me to analyze or could I send you what I created for you to dissect?

    Thank you.

  60. steveald
    Posted March 9, 2012 at 12:02 pm | Permalink

    One more thing. How “not very slick” would you say it will be in FM Go? My intention was to have this database used primarily in that environment. Would you have an alternative to suggest?

  61. Tim
    Posted April 2, 2012 at 4:18 pm | Permalink

    I love it, but I am having problems.

    When I put a starting position of 1, then it only finds if the word is the second word. It then ignores the first and last words. If I make the Start = 0, then it will find everything up to the point that the word is full spelled. Once the word is full spelled it drops from the list.

  62. Tim
    Posted April 2, 2012 at 4:21 pm | Permalink

    My Bad. FYI I would describe the order of the custom function.
    needles; haystack, start

  63. Krist
    Posted April 3, 2012 at 10:04 am | Permalink

    Thanks @Danny for the great solution. I love it.

    I have to implement it on several layouts.
    I used the Set Field to update the search, but when i switch from one layout to another, the portal rows doesn’t refresh.

    I don’t want to use refresh windows because i want a windows runtime and the screen flickering is terrible.

    Any help would be great.

  64. William
    Posted July 4, 2012 at 8:19 pm | Permalink

    Mate – took me 2 minutes to implement –

    I used my own script – but this example of Portal Filtering with a custom function is pretty good indeed…

    Well done

  65. Posted December 12, 2012 at 1:01 pm | Permalink

    THANKS. This was just what I was looking for. Works great. Bit on tweaking on FMGo (1s on InstallTimer and dropped the SetSelection as brings up keyboard) but is fine. THANKS.

  66. Andrew M
    Posted February 1, 2013 at 5:31 am | Permalink

    For fastserv and others asking about asterisk searches, I tried replaced the filter line

    FindWordPartsInText ( Globals::SearchField ; RelatedTable::Field1 & ¶ & RelatedTable::Field2 ; 1 )

    with

    PatternCount ( RelatedTable::Field1 & ¶ & RelatedTable::Field2 ; Globals::SearchField )

    and it worked with no issues.

  67. Posted February 22, 2013 at 2:21 pm | Permalink

    Thank you so much for this – tried various other methods which were all either slow or ineffective. This one’s absolutely perfect. Brilliant!

  68. Eric
    Posted April 2, 2013 at 12:35 pm | Permalink

    Thank you very much danny

    These are the answers I have been looking for!

    They are really helpful!!

  69. Pierre
    Posted April 9, 2013 at 3:36 am | Permalink

    Thank you so much!

  70. Pablo
    Posted April 22, 2013 at 9:14 am | Permalink

    Danny, thank you so much for this trick. I incorporated it into my solution and it works very, very well.

    What should I do to make it work with dates and date ranges? I’ve tried by creating a calculated field that translates dates to text, and use this field in the find, but it doesn’t work. I know I have to modify the function, but I don’t know how.

    Thank you

  71. Stephen Grassie
    Posted February 20, 2014 at 12:34 pm | Permalink

    This is one of the best filemaker tutorials I have ever read. I’m an intermediate user of Filemaker, and so many of the helpful sites out there are too confusing for me. THANK YOU for explaining so clearly and helping me learn Filemaker!

  72. Michelle Preston
    Posted March 8, 2014 at 4:46 pm | Permalink

    Very nice! Thank you. I was having a hard time getting the filter in the popup in the Invoices starter solution to work. This was a piece of cake!

Post a Comment

Your email is never shared. Required fields are marked *

*
*