Unindexed field search tips

[TJ53]

Hi,

I'm performing a search on an unindexed field and it takes forever ... it has improved a little bit after I included a "replace field contents" script step before, so the search is done in a numeric field. Still it's slow ...

I'm sure this topic has been treated here before, that I didn't find much info. Are there any tips or workarounds for performing finds in unindexed fields?

Any help would be greatly appreciated, thanks!

[Fenton]

The main tip is to Find on any indexed fields first. Then go back into Find mode, enter criteria for your unindexed field(s), and use Constrain Find. Your first Find will be fast. If it narrowed down the found set, then the unindexed Find will be faster, proportionally.

[bcooney]

I've noticed that if I include a stored indexed field in any Find request along with any unstored, the Find is much quicker. It's as if FM is smart enough to do its own Find, then Constrain in a single Find request. Interesting.

[David Jondreau]

Replace[] has weaknesses in a multi-user environment.

With more details we might be able to give more help. What is the field referencing that makes it unindexed? What are you trying to find?

[Vaughan]

Replace also updates every record, which will change the mod date and time information every time the script is run. Kinda makes the mod date and time a bit useless for auditing record information edits.

[Søren Dyhr]

Why is there a search in unindexed fields in the first place?

I try to recall the points in the speech Chris Moyer made at the 2005 devcon?

--sd

[TJ53]

Hi, thank you all for your responses! I'm not sure if this can be done with FileMaker, doe to performance issues ... I have a database with buildings, floors and apartments (see attached sample).

Now, the user wants to be able to specify a square feet range and then find all apartments, or (this is the hard part) COMBINATION of apartments that meet the specified range.

That is, let's say that the user specifies 300 - 400 square feet range as the search criteria. Then the database should find the following apartment records: (I'm using first building of sample file "533 3rd ave")

Floor 1, apt 2 (350 square feet)

Floor 2, apt 1 (100)

Floor 2, apt 2 (30)

Floor 2, apt 3 (200)

Floor 3, apt 1 (100)

Floor 3, apt 3, (100)

Floor 3, apt 4 (50)

Floor 3, apt 5 (100)

I found a custom function in the Brian Dunning website that calculates permutation of elements ( http://www.briandunning.com/cf/208 ) but in this case I would need combination of elements since order is not relevant. In any case, I think that a search in that field (custom function) would take for ever, also because there are other fields that have to be taken into account for the search criteria (such as the "availability date"), that would make the custom function depend on global fields (used for the user's search criteria).

So I'm thinking of first constraining the found set and then using a loop that omits or keeps each apartment if it meets the criteria, after having been compared it with the rest of the apartments within the floor.

Some floor can have up to 20 apartments, so there are too many different combinations to process, so I wonder ... is this doable with FileMaker?

If someone has some advise I would really appreciate, thanks very much!

Buildings.fp7.zip

[David Jondreau]

You're really not searching for apartments, you're searching for floors.

I think what would be helpful would be a stored repeating field that contains all the different possible square footage combinations for that floor. A custom function would get you there, though the one you reference doesn't do what you need though.

Then you need to get from there back to apartments.

That can be done in a couple ways, depending on how you deal with multiple permutations on the same floor.

For example, should you be showing the 300 ft possibilities for Floor 2 and Floor 3 separately than the 350 ft possibility in your example?

[TJ53]

I think what would be helpful would be a stored repeating field that contains all the different possible square footage combinations for that floor. A custom function would get you there, though the one you reference doesn't do what you need though.

Thank you DJ, I'm creating a recursive custom function, that seems to be the way to go!