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.
Both excellent suggestions.
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.
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:
and set Properties::ConstantActive = UsersFromPropertiesActive::Status (in addition to the other criteria.)
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.