Newbie Question about Groups and sums

[john341]

Hi to all,

 

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

In my strugle 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....2/2/2014.....12

 

I nedd to create another table that i keep the grouped sums of the above table and updated every time a new record adds like..

 

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

rec2....2/2/2014..12

 

similar the sql command SELECT field1,sum(field2) FROM table GROUP BY field1

How can i do this?

 

[comment]

Why would you want/need another table - when you can summarize the one you already have?

 

Here's a quick-n-dirty way:

http://www.filemaker.com/help/13/fmp/en/html/add_view_data.4.10.html#1046958

 

And here's the starting point for producing a full report:

http://www.filemaker.com/help/13/fmp/en/html/non_toc.46.18.html#1077799

[john341]

Hi again,

i need the data to manipulate them for other calcs or to send them to external web chart so i need them as values to a table.

[comment]

i need the data to manipulate them for other calcs or to send them to external web chart

 

You should ask about that - and not assume that it requires another table.  A classic XY problem.

[john341]

Hi again,

Again i need to know a way to send the data to another table it is not only for that case....This is just a generic case.

[comment]

There are many ways to do that - and none that are really simple. And for a good reason, too: in a relational database, everything is geared to prevent having the same data in more than place. So I am not sure what to recommend, esp. since you don't tell us why (or when) you need this.

 

Consider, perhaps, exporting the data - sorted and grouped by the date field, with a summary field providing the sub-totals - then import the result into the other table.

 

Keep in mind that whatever you do, the process cannot be easily automated. You need to take an action (e.g. create a new record in the other table) every time your data is modified. Filemaker only gives you tools (script triggers) to initiate an action as a result of users' actions (on a specific layout).

[john341]

Thx for the info,

It is just a study on what ican do with filemaker and how.

As fo rthe data i can think several ways that i can use them  like a portal with only days and subtotals or subtract or add to the sums or use them for charting purpose (html chart not in filemaker)

and they are just a little percent on what you can do.

So is there a way that i can do that?

[comment]

So is there a way that i can do that?

 

Yes. Which one do you want to take first (they don't all necessarily have the same answer)?

 

For example: to send data to an external charting engine, you'd probably want to start by using the ExecuteSQL() function to collect it - just as you said in your original post. This one is easy, because the user has to initiate the process expressly (I presume) - so you can do nothing until that happens, and put all the required logic in a script that the user will call at will.

[john341]

I prefer to just have them on a table i have managed to create one table with a field relation Table1::datefield = Table2::datefiled and a field2 = Sum(Table1::Field2) but i cant figure it out how to populate the new table with unique values from Table1::datefield.

As for the executesql cant figure out again how to populate records as every result i have from the select option it send it to just one record and doesnt populate the table.

[comment]

I prefer to just have them on a table

 

That is your privilege.