4 Replies Latest reply on Jul 18, 2010 4:55 AM by TheGor

# Calculation with selected Records from one Table

### Title

Calculation with selected Records from one Table

### Post

Hallo all,

I am new in Filemaker but have some experience in SQL but dont now how to do that in Filemaker.
I want to calculate some selected records inside a table see below.

 Timestamp StationCode In Out 11.7.2010 0:00 62753 1267147 1178922 11.7.2010 0:00 62754 2042639 1963830 11.7.2010 0:00 62755 526172 575644 11.7.2010 0:00 62756 846324 814993 11.7.2010 0:00 62757 771005 732987 11.7.2010 0:00 62758 865027 873640 12.7.2010 0:00 62753 1269803 1181184 12.7.2010 0:00 62754 2042689 1964010 12.7.2010 0:00 62755 526172 575644 12.7.2010 0:00 62756 846424 814993 12.7.2010 0:00 62757 771211 733143 13.7.2010 0:00 62753 1270687 1183020 13.7.2010 0:00 62754 2042689 1964010 13.7.2010 0:00 62755 526172 575644 13.7.2010 0:00 62756 846424 814993 13.7.2010 0:00 62757 771231 733143 13.7.2010 0:00 62758 865027 873640

I WANT:
(In - Out) from 13.07.2010 and (In - Out) from 11.07.2010 where StationCode = 62753
(1270687 - 1183020) - (1267147 - 1178922) = -558

In my Layout i want to define Time Perion and Sation Code, how to get this working in Filemaker?

Thanks Igor

• ###### 1. Re: Calculation with selected Records from one Table

In FileMaker, queries are finds.  And you can use relationship to filter your queries.  So go to your graph (File > Mangage > Databases and select your Relationships tab.  Select your table occurrence and click ++ to duplicate it. Then use cursor to drag line from Station Code to Station Code (it will establish relationship 'filter' on Station Code using = ). This is called a selfjoin so for this example, name it sj.

Now create a calculation in your table as:

Let ( [

first = sj::out - sj::in ;

last = Last ( sj::out ) - Last ( sj::In )

] ;

last - first

)

There are other approaches but it would depend upon 1) your FM version, 2) how you want the results displayed (whether you want to see each calculation result when view the Station Code records or whether you want a report.

If you want a report and the results to look something like this ...

July, 2010

62753 ... 558

62755 ... 90

62756 ... 110

TOTAL July, 2010 ... 700

August, 2010

62753 ... 300

(etc)

... then we would need to also join on month/year and you will need to create a find for the time period and then create a columnar list layout grouped first by month/year and then by Station Code with sub-summary.

• ###### 2. Re: Calculation with selected Records from one Table

Hi LaRetta,

how can I define in this way start and end date? My result should be displayed im "Form View"
becouse of better visualisation i will use some grafics in background.

1) FM 11
2) Want the results in Form View

Maybe it is simpler when you see what i mean:

Here is my example_file

Thanks Igor

• ###### 3. Re: Calculation with selected Records from one Table

Great example!  I've uploaded your file with the changes.  I'm not clear whether you want the start to be EQUAL to the timestamp you enter or if you want simply the earliest Station Code entry after the start time you entered.  Same with end.  And it seemed confusing that the In is greater than the Out, so this can't be time seconds.

I'm sure you can adjust to meet your needs; if not please let us know and we can help you adjust it.  One thing - you will want to remove the dash from your field name because FM (and many database programs) don't like it. :^)  You can download the revised file here:

http://www.4shared.com/file/DYDf3x64/exampleREV.html

UPDATE: For some reason, I cannot use links - sorry about the format of it.

• ###### 4. Re: Calculation with selected Records from one Table

This is extra good! :-) thanks LaRetta!

Now i understand how to use self-join, FM rocks :-)