Script to return sums from found sets to variables

[aerugo]

Hi!

I'm new to FileMaker, so bear with me!

I want my script to find a set of records, based on two variable criteria and one hard-coded criterion. The two variables - which are a range of dates and a name, are chosen by the user and passed on to the variable $$totalquantity at the press of a button which triggers the script.

The script should then carry out a search, using these two variables as criteria in two given fields, and only include records that contain the letter A in another field (a product code).

I then want to use the SUM function to add add the numbers in field "Quantity" for all returned records, and pass this sum into a variable called "totalquantity".

It then navigates to a layout called "Report" where I display some data based on these variables.

This is the script I have come up with:

SCRIPT 1:

Set Variable [ $$findcompany; Value:Search::Company ]

Set Variable [ $$daterange; Value:Search::Startdate & "..." & Search::Enddate ]

Perform Find [ Specified Find Requests: Find Records; Criteria: Transactions::Date: “$$daterange” AND Transactions::Company: “$$findcompany” AND Transactions::Code: “A” ]

[ Restore ]

Set Variable [ $$totalquantity; Value:Sum ( Transactions::Quantity ) ]

Go to Layout [ “Report” (Search) ]

The problem is that this sets the variable $$totalquantity to the sum of all values in field "Quantity" for the desired company and date range, but ignores the constriction to only include records with a code containing "A". The strange thing is, that if I construct a set that should not return any records with code A, I get an error prompt saying that no records match the find request. It seems to be the SUM function that is not behaving here.



I tried a script that first navigates to "Transactions", where the records are, and then runs an identical search.:



SCRIPT 2:

Set Variable [ $$findcompany; Value:Search::Företag ]

Set Variable [ $$daterange; Value:Search::Startdate & "..." & Search::Enddate ]

Go to Layout [ “Transactions” (Search) ]

Perform Find [ Specified Find Requests: Find Records; Criteria: Transactions::Date: “$$daterange” AND Transactions::Company: “$$findcompany” AND Transactions::Code: “A” ]

[ Restore ]

Set Variable [ $$totalquantity; Value:Sum (Transactions::Quantity) ]

When viewing the "Transactions" layout in table-view, I can see that the search and constraint is successfully performed and only returns records where the "Code" field contains "A". But when I use the "Data Viewer" to monitor $$totalquantity, it does not calculate the sum of all returned records - it just returns the "Quantity" value of the first record returned. But I can clearly see that the find request returns TWO fields.

So to sum it up -

In "SCRIPT 1", SUM seems to ignore the constraint of " AND Transactions::Code: “A” ".

In "SCRIPT 2", SUM will only return the value of the top record.

What is going on here?

[comment]

To summarize the found set, use a summary field - not the Sum() function.

[aerugo]

Ok, thanks! That worked for SCRIPT 2! $$totalquantity is set correctly.

However, I'm still having problems with SCRIPT 1.

I have changed the second to last line in script 1 to:

Set Variable [ $$totalquantity; Value:Transactions::ReportTotalQuantity]

Where ReportTotalQuantity is a summary field which is the sum of "Quantity".

The result is the same as before - $$totalquantity becomes the sum of all values in field "Quantity" for the desired company and date range, but ignores the constriction to only include records with a code containing "A".

[comment]

ignores the constriction to only include records with a code containing "A".

That sounds like you have a problem with specifying the correct find criteria?

[aerugo]

The criteria in scripts 1 and 2 are, as far as I can see, identical. And the constriction is apparently being taken into account, since an error message is returned if I try to do a search where no records contain A in the "code" field.

The only difference is the order, and what layout I go to:

script 1:

find

set variable

go to layout (report)

script 2:

go to layout (transactions)

find

set variable

[comment]

If the found set contains only "A" transactions and the layout where you perform the find is a layout of the Transactions table and the summary field is defined in the Transactions table, then the summary field's value will NOT include any records that are not coded as "A".

Slightly off-topic, I am not sure why you need to collect the values into variables, etc. - why not produce the report directly from the Transactions table?

[aerugo]

Ah, ok, I see now! The field where the script is initiated is not a layout of the transactions table. That was the problem. Thanks!

Well, to be honest, this was just the method that felt most intuitive. I'm completely new at this. How could I go about producing a report directly from the transactions table? It has to use the same functionality as this, that is choosing a company and a date range in a view, and then displaying the information. The information is total quantities and total cost over the time period, as well as broken down based on what letters are in the "code field".

Something like this:

Company X - January 1st to February 28th 2012

Total quantity purchased:

Total cost:

Quantity of A purchased:

Cost:

Quantity of B purchased:

Cost:

Etc.

Could I show this in an easier way without messing around with variables?

[comment]

Sure. Find the transactions within the date range, then sort them by company and by code. Use a list layout of the Transactions table, with two sub-summary parts: by company and by code (I am assuming there is more than one company), and no body part. Place an instance of the summary field in each sub-summary part.

[aerugo]

That was way easier! Thanks a lot, I really appreciate it! :)