More than two 'whose' criteria.

[Phrogz]

Summary

I need a way to filter/find records based on 4-5 criteria, but FM7 is ignoring items in the whose clause beyond the second.

Details

I have a table which among other fields has category, subcategory, year, month, and price fields. I need to be able to bring the total of all prices over into an (existing) excel spreadsheet, with a unique subtotal for each combination of the category, subcategory, year, and month. Further, I need to get a 0 subtotal value even if no records for a specific subcategory or month have been entered.

I was doing this using Filemaker 2.1 (!) and applescript, but using Classic for FM2 has become a problem. So I'm trying to port the existing script over to use FM7.

Although there may be a better/faster way to get the subtotals (feel free to suggest one!) the way I have been doing it is to use AppleScript to show all records for each unique combination, and either looking at the value of a calculation field for the total of all found records, or using 0 if the try block fails to find any records.

(Aside: in FM7, the total field is no longer bringing back a useful value; I worked around this by doing the sum manually in AppleScript by looping over the price of all records. If the total field can be fixed, it would be nice.)

The Problem (finally)

Assume I have data like this:

category  subcat  month  year   price

cat1      bar     1      2004   99

cat1      foo     1      2003   99

cat1      foo     1      2004   15

cat1      foo     1      2004   35

cat1      foo     2      2004   99

cat1      foo     3      2004   99

cat2      foo     3      2004   99

I want to find all "cat1", "foo", 1/2004 records. Using the following AppleScript:

tell application "FileMaker Pro"

tell table "Main" of database 1

show (every record whose

[cobra]

Hi Phrogz,

In terms of alternatives, have you tried using Summary fields and Sub-Summary parts in your layout? For the sub-summary parts to show up, you would need to sort the data by the equivalent sub-summary part sort fields and view the results in Preview mode.

Depending on what you need, this might work quite well. Ensure that you format the data based on the current layout and check group-by for you summary fields (both options are in the export dialog).

If this works for you, this will allow you to export all the data you require in one go (rather than having to specify a bunch of different find requests via AppleScript).

For your applescript solution:

Have you tried defining the search as a script in Filemaker and then getting Apple Script to execute it via the do script command?

If you have to pass variables to FileMaker, use AppleScript to set some global fields (in your FileMaker db) and then use those in your script.

Enter Find Mode

Set searchfield1 to globalfield1

...

Set searchfieldN to globalfieldN

Perform Find

If you wanted, you could use AppleScript to set one global field to all the values you require in your search. Use a delimiter ("|" usually works well) to parse the field (using the middle function) for the parts you require for each search field.

So, instead of Set searchfield1 to globalfield1, you would use:

Set searchfield1 to Middle(globalfield, 1, Position(globalfield, "|", 1, 1)-1)

Set searchfield2 to Middle(globalfield, Position(globalfield, "|", 1, 1)+1, Position(globalfield, "|", 1, 2) - Position(globalfield, "|", 1, 1) - 1)

..

Set searchfieldN to Middle(globalfield, Position(globalfield, "|", 1, N-1)+1, Length(globalfield) - Position(globalfield, "|", 1, N-1))

(Where N is the total number of fields you wish to search by)

Hope this helps!

Cobra

[bruceR]

1. It looks like you have found a product bug and it needs to be reported to Filemaker using send feedback.

2. You do not need to use whose clauses to find records in Filemaker. You can use applescript to script standard finds:

tell layout "Whatever"

delete every request

set NR to create new request

set cell "X" of NR to "1"

set cell "Y" of NR to ">2"

set cell "Z" of NR to "2..37"

end tell

find in file "YourFile"

But this is really just a standard subsummary report which you can do without applescript even in FM2.1. You can export the summarized results if you want to use them elsewhere.

[Phrogz]

Hey Cobra, thanks for your responses. One question:

In terms of alternatives, have you tried using Summary fields and Sub-Summary parts in your layout? [...] If this works for you, this will allow you to export all the data you require in one go (rather than having to specify a bunch of different find requests via AppleScript).

I thought about doing this, but I didn't have any idea how I could get the data out; say I find the right year, month, and category, and get a bunch of subtotals for each subcategory. How do I use AppleScript to determine which subtotal value goes with which subcategory? How do I determine if a subtotal for a specific subcategory doesn't exist (because no records for that subcategory have been entered yet)?

[cobra]

Hi Phrogz,

Sorry for the late reply. This is the approach I thought you could take:

1. Create a form that formats your query results (on your subcategory items table) along with the (Subtotal) summary fields you require.

2. Run the query, view the results in your form, and export the results to a file. In the export dialog, choose to group by the appropriate fields (year, month, category, subcategory) and format the export based on the current layout. You need to be in preview mode for the export to work properly.

3. Create another form that runs a query on your subcategory table to find subcategories with NO items (sales?) during a particular month/year. This form should be formatted in the same way as the form above. You will also need to create one global field on your subcategory table. This should always be set to zero and is used as a dummy field for your subtotal (i.e. you position it where the subtotal summary field is placed on your first form).

4. Run the query and export the data to another file. As with step 2 above, you should group the data using the appropriate fields and format the data based on the current layout. You need to be in preview mode for the export to work properly.

At this stage you have two seperate files. The first shows data for categories and subcategories with items (or sales) for that month/year. The other shows Categories and subcategories that have no items sold.

5. Open both files in Excel.

6. Write a macro or AppleScript that determines where the contents of the second spreadsheet need to be placed within the first spreadsheet. Once you have the data in one file, you can import it into your existing spreadsheet.

If the sample output in your original post is close to what you're actually doing, this shouldn't be too difficult.

You can run a single script in FileMaker that exports the two files and then executes Perform AppleScript script step to import the data into Excel, parses it, and then imports it into your existing spreadsheet.

In terms of knowing which subtotal is for which subcategory (or category), this is determined by the formatting of your form.

Unless I'm not getting what you need to do, the solution above should produce the results you require.

Hope this helps!

Cobra