Can't Index Field - Help!

[Tigerstrike]

I have a database which has some VERY basic accounting calculations, as follows:

Quantity * Unit Price = Cost

Sum(Cost) = Total Cost

Total Cost + Tax = Grand Total

Here

[comment]

This is not entirely clear.

As you say, a summary field totals up a column of numbers. Therefore, it does not belong to any specific record (row). It "belongs" to the found set.

So what would you expect to find, when searching on this field?

I don't have version 6, but in version 4 AND in version 7 calculations are stored by default.

[Ender]

Hey Tiger, I have an answer to this in your FileMaker Cafe thread:

http://www.maclane.com/ubbthreads/showflat.php?Cat=0&Board=6&Number=497261

Perhaps you can give one thread a chance before posting elsewhere?

[Tigerstrike]

Hi Ender,

My apologies on the double post. I didn't realize the same people frequented both forums, I thought by posting in both spots I might be targeting different audiences - I'll know better in the future.

Here's something to add. I used to be able to index my total price field (the summary field). Every year I duplicate the invoice database I use. Year before last, I could search the INDEXED field. Last year when I created an empty duplicate of the file, the indexing somehow turned itself off. Doesn't that seem kind of strange? That I could but now can't? I haven't changed versions of FM or anything major. It's a head scratcher.

Tiger

[Vaughan]

That's interesting... summary fields can't be indexed.

[Oldfogey]

It's more than interesting! Those calculations will not do what they are, apparently, supposed to do.

Sum(Cost) will equal the value of Cost in each record not the total of values in a column. Unless, shudder, Cost is a Repeating field. The only(?) way, Sum will provide the total of all Cost fields is to use Sum(SelfJoin::Cost).

Sum is not a Summary function. Do you mean Total(Cost), Tiger?

[BobWeaver]

The Sum() function goes way back to the pre-relational days of Filemaker when repeating fields were used to simulate what portals do now. At that time the only function of Sum() was to total repeating fields, or add together multiple fields in the same record. As long as Sum() only references fields in the current record, it should be indexable.

[BobWeaver]

To get back to your original question, there may be another option besides what Ender posted in FM Cafe.

Do you have multiple criteria in your search? If so, you can do a search only on the indexable fields first, to narrow down the found set, and then process the resulting found set to get the final set. Unfortunately, the constrain find operation won't work in this situation (unless they've fixed it in FM7). So, you need to do a looping script to check the field in each record, and omit it if it doesn't match your criteria.

[Ender]

That's an excellent option Bob (I never remember it.) This would work great if the found set can be reduced significantly by the initial indexed find.