Log in to create and rate content, and to follow, bookmark, and share content with other members. Not a member? Join Now!

AnsweredAssumed Answered

Newbie Question about Groups and sums

Question asked by johnjohn on Feb 17, 2015
Latest reply on Feb 18, 2015 by philmodjunk

Like • Show 0 Likes0
Comment • 3

Title

Post

Hi to all,

I am total newbie on this so sorry for the stupid question.

In my struggle to understand filemaker i have the following scenario

I have a table with the following records and fields

............field1...........field2

rec1.....1/1/2014.........3

rec2.....1/1/2014........5

rec3....3/3/2014........12

I need to create another table that i keep the grouped sums of the above table and updated every time a new rec created.

like..

rec1.......1/1/2014.....8

rec2.......3/3/2014....12

similar the sql command select field1,sum(field2) from table GROUP BY field1

How can i do this?

No one else has this question

Outcomes

3 Replies

philmodjunk Feb 18, 2015 10:25 AM
Mark Correct
Correct Answer
It's quite possible that you do not need the second table at all. You can generate a summary report with one row of data for every unique value of Field1 with a sub total placed next to it. See this thread for a tutorial on summary reports: Creating Filemaker Pro summary reports--Tutorial

But you can also define a table where field 1 is the date shown and used as a match field to set up a relationship in Manage | database | relationships linking the two tables by date:

Table1::DateField = Table2::DateField

Then Table2::field2 can be defined as a calculation field: Sum ( Table1::Field2 ) to compute the total.

It is also possible to set up calculation fields that use ExecuteSQL to compute such a an aggregate value.
Like • Show 0 Likes0
Actions
johnjohn Feb 18, 2015 12:51 PM
Mark Correct
Correct Answer
Thx for the info...

I would like the table option i need the data to munipulate them (do some other callcs - send them to external charting engine....)

The problem now is on how to populate the new table with unique values from the Table1::DateField..
Like • Show 0 Likes0
Actions
philmodjunk Feb 18, 2015 1:20 PM
Mark Correct
Correct Answer
What you describe does not sound like reasons to have the second table. Your other calcs might possibly be done as part of your summary report design and if you have to export this data for the "external charting engine", you can export the summarized data such that you get one row of exported data per date just as you would have in your summary report.

There are many ways to populate such a table. Here's just one:

Define your relationship to match by date fields, but also double click the relationship line and select "allow creation of records via this relationship" for the second table.

Then this script step, performed from each record of the original table:

Set Field [Table2::DateField ; Table1::DateField ]

Will create new records in Table2 without creating any duplicate records. Please note, however, that you may need to take additional steps to handle situations where a record in Table1 is deleted or the date is modified to be a new date, leaving an "orphan" record in Table2.
Like • Show 0 Likes0
Actions

Newbie Question about Groups and sums

Title

Post

Outcomes

Related Content

Recommended Content