Summary Fields and ODBC queries - WTF

[SJM]

I have had adequate success using ODBC to send simple SQL queries to FMP. But I'm now having difficulties querying tables that have Summary field types.

When I run a simple query like "SELECT `company name` from Clients" the FileMaker interface mostly locks up (as it always does on ODBC queries) but I can see a dialog about calculating Summary fields. Eventually my program making the query raises a spectacularly uninformative exception*, although FMP continues to chew through 99% of the CPU until I eventually kill it.

If I delete all Summary fields in a test copy of the database, the query works as well as it should. Through experimentation I've found I can sometimes get away with deleting most but not all of the Summary fields.

My conclusion: Any attempt to query a table through ODBC causes FMP to calculate all Summary fields on all records in the table. Because it does this before returning a single result, the fetch call eventually does the ODBC equivalent of a time-out. (Additionally, the solution I've inherited has some extremely poorly thought out Summary fields that are calculating in exponential time with the number of records.)

Is my conclusion accurate? Other than deleting the Summary fields (not an option for some of them that are used in reports or scripts), I have thought of one other possible solution, but unless I'm mistaken it doesn't appear to be an option.

I thought that I might be able to move the Summary fields to another table and use related fields to get whatever it is calculating. But when specifying a Summary it doesn't have the option of using a Relationship when selecting what field to total or whatever.

Does anyone know a general way to get around this situation? Have I completely mis-diagnosed what is happening? If so, why does deleting the Summary fields fix it? If not, why is FMP so retarded as to do all this unnecessary calculations that have nothing to do with the query?

Steve

* dbi.operation-error: [FileMaker][ODBC FileMaker Pro driver][FileMaker Pro]Unknown error in FETCH

[Wim Decorte]

Your conclusion is correct. What you can do is to use an intermediate file that you populate with the data you need for your SQL query. That way you can set the data field in the intermediate file with the result of the summary field in the original file.

Not always very pretty but workable.

HTH

Wim

[SJM]

Hello Wim,

I'm afraid I don't quite follow your suggestion.

>What you can do is to use an intermediate file that you populate with the data you need for your SQL query.

So if the Clients.fp5 table has the offending Summary fields, and I need to query the `company name` and `company phone` fields (which are not Summarys) from this table, then I set up a table Clients_sub.fp5 with two fields to mirror the two I need and relate the tables? And then query Clients_sub to make an end-run around the Summary calculations? I thought ODBC does not support querying related tables... (I'm pretty sure I'm confused here.)

>That way you can set the data field in the intermediate file with the result of the summary field in the original file.

I don't want the result of the Summary field, I don't care about it. I just want to be able to query those fields without having it calculate the Summary field.

I had hoped I could move the Summary fields into a related file, but the Options for such fields don't seem to support Relationships. Then I thought above you were suggesting that I could mirror the (non-Summary) fields I want in a related file, although I'm confused about how exactly to do that. But the last part of your suggestion, I do not understand.

Thanks for the response, though! I'd be thrilled if you care to elaborate.

Steve

[SJM]

I am now able to describe more clearly my general idea for how to move a summary field into its own table in order to make the original table faster. Please respond with any problems you more knowledgeable people forsee with this plan. I will be testing it today and this weekend.

Suppose I have a table Invoices, with field Invoice_Amount, and summary field Total_Invoice_Amount, which is the Sum of Invoice_Amount. Also assume table Invoices has a field that functions as a primary key called "Record_ID".

1. Open file Invoices and Save a Copy As "Invoices_Summaries"

2. Define a relationship called "Invoices_Summaries" from Invoices to Invoices_Summaries equating Record_ID and allow creation of related records. (So creating new records in Invoices will create new records in Invoices_Summaries.)

3. Open file Invoices_Summaries, and define a relationship called "Invoices_Mirror" from Invoices_Summaries to Invoices equating Record_ID

4. In Invoices_Summaries change the field Invoice_Amount from a number to a calculation, and set its value to be "Invoices_Mirror::Invoice_Amount".

6. Delete all other fields in Invoices_Summaries, leaving only Record_ID, Invoice_Amount, and Total_Invoice_Amount

7. In table Invoices, change reference to Total_Invoice_Amount to references to Invoices_Summaries::Total_Invoice_Amount

8. Delete Total_Invoice_Amount from table Invoices.

I now have no Summary field in Invoices, so queries should be much faster, but I can still access the values of those Summary fields through the relationship Vendor_Invoices_Summaries.

What do you think?

Thanks,

Steve