4 Replies Latest reply on Nov 29, 2008 9:02 PM by denJ

    Scripting complex searches



      Scripting complex searches


      Hi. This is a repost from over a week ago. Anyone care to comment?


      I have a solution now which has worked fairly well for 5 or more years but now I would like to update it, add some functionality and perhaps find a better way to script.


      I have a database of users ('UsersDB') which includes each user's criteria for searching a real estate listing database (#rooms, #beds, towns, price range, etc.)


      For each user, based on their search criteria, I search 'propertiesDB', the database of listed properties. The result set is then emailed to the user.


      Currently my script goes through a somewhat tedious process for each user of copying each individual criterion from UsersDB one at a time (#rooms desired, for example), then switching to propertiesDB, restoring the previous find request (or initiating a first request), pasting the criterion into the appropriate field of the find request and then switching back to usersDB, copying the next criterion and repeating this process for each criterion (#bedrooms, then towns, property type, etc) until the find request for the particular user is completely built up.


      Six of the user criteria fields can have multiple items, for example, 'multi-family' and also 'single-family' properties or zip codes x,y,z, etc. This can result in some rather long and complex "OR" searches in Filemaker.


      Is there a better way of scripting this to get a user's complete set of criteria into a find request or is this a limitation of Filemaker Pro?


      I could, for example, use a script to import all of a user's criteria into the property database but then each criteria item would still need to copied and pasted into a find request, according to my current method.


      I appreciate any and all responses.


        • 1. Re: Scripting complex searches

          Hey, Buddy,


          Let me toss two ideas your way.


          1)  Rather than using "copy" and "paste" (which, in my experience, can get mucked up with the clipboard not being able to go as fast as FileMaker is using it), use variables.  That way, you can go to the user table, collect the whole set of criteria at one time, then go back to properties table and perform the search.


          Given your specific needs, I think you'll want to stick with doing a search on the first variable, followed by "Constrain Found Set"s on each of the remaining variables.  Otherwise, it would take you hundreds of "New Request"s.


          2)  If you're managing both databases in FileMaker and can control how the data gets entered (using popup menus, radio buttons, etc.), you could potentially build a relationship between the user and the properties that will pull for each user all matching properties without having to go through a search.


          The key would be that, in the properties table, you'd have to have a calculated field for each feature that includes the value, followed by a carriage return, followed by "Any."


          For example, in "Type of Home", users probably have checkboxes where they can select "Single Family" and "Townhome".  But, they might also have a checkbox that just says "Any".  Over in the properties table, if you make a field that takes the home's type + carriage return + "Any", then you're covered.


          And, of course, for some items, you'll have inequalities.  Property number of rooms should be greater than or equal to what the user specifies, etc. 


          Set up a many-to-many relationship, and you'll be able to pull up a user and see all of his or her matching properties in a portal. 


          Hope one of those two ideas will put you on a productive path. 

          • 2. Re: Scripting complex searches

            Both excellent suggestions.
            Thank you.


            Using variables should be natural and obvious, I guess. Funny how I didn't consider it until you mentioned it.


            But the second idea is especially appealing. I have complete control over data input so this should work very nicely. It is much more direct than iterating through searches. Plus it seems like an excellent way of learning more about relationships and portals as well.


            thanks again


            • 3. Re: Scripting complex searches

              Glad to hear.  If you're looking into the second option, remember that in the table you're "peering into", the fields involved in the relationship must be "stored".  In this case, the fields in the Properties table, many of which will be calculations, must all be stored.


              Actually, when you're done there, you might want to look at creating a portal in the Properties area to view all users who fit the property.  Then, if you find out that a property's price is being lowered (for example), you can easily shoot out an e-mail to all users who match.


              You'd have to create a second "table occurence" of Users called something like "UsersFromPropertiesActive".  The relationship between this and Properties would be nearly identical to the one you set up above, but with one additional set of criteria.  You'd have to be sure that you're only viewing users whose status is marked as "active" so that you don't send e-mails to someone who has already found a home to buy.  To make the match, create a calculation field in Properties that has the formula:


              = "Active"


              and set Properties::ConstantActive = UsersFromPropertiesActive::Status (in addition to the other criteria.)


              Good luck!

              • 4. Re: Scripting complex searches


                This is great.

                I love the "Any" trick above. Don't know if I would have thunk of that one...

                I saw someone do something similar a few years ago in FM Developer magazine but they didn't explain it and I didn't quite understand how it was done. Now I know.


                Works like a charm!


                This latest suggestion is also excellent. I look forward to implementing it. All of this is so much easier than my old, much clunkier method.


                Looking forward, I have a question--how to assemble the emails? Is it better to script a loop through the portal rows or use a calculation field? I haven't attempted it yet so am not sure yet what is possible or more desirable.