Total in DB

[Rear]

Hi!

I've got one database for my timesheets. I have a couple of records per week. One of the field is a number. Now I'd like to put the total of that field at the bottom of my layout.

Now if I wish to see only week 48, I would do a find. But the total will be of that week only. But I want to have the total of all records, even the ones not showing (because of the FIND). Is it possible to do that? I'm using FM pro 5.5v1 for Windows

Sébastien
sroyal@videotron.ca

 

[Schlogg]

You could use a self-join such as -
g_One Global text initialised to 1.
JustOne text auto-enter '1'
SJoin = g_One to JustOne
Define DisplayTotal = Sum(SJoin::NumField)

Cheers,
Paul J.

 

[Rear]

Thanks Paul, But I've never done a self-join.

Can you detail all the steps necessary to perform what you just suggested?
Here are the fields I use:
- Hours (daily hours)
- Total Hours (summary, total of Hours)


Sébastien

 

[Rear]

Schlogg,

Have you read my last reply??

TIA


Sébastien

 

[Schlogg]

Sorry Sebastien.
I'm not sure I can add much more. Are you familiar with 'normal' file-file relationships? A self join is more or less the same but the relationship is from a file to itself.
Define 'g_One' as Global text. Initialise it to 1.
Define 'JustOne' as text with auto-enter '1'. (You will need to use Replace to set all the existing records to '1'.)
Define a relationship 'SJoin' linking Timesheets file to Timesheets file matching g_One to JustOne.
Define 'DisplayTotal' as a calculation field equal to
Sum(SJoin::NumField)
'DisplayTotal' will always hold the total for all records in the file.





Cheers,
Paul J.

 

[Rear]

Schlogg,

I'm already using "normal" relationships. But this one is hard to follow!

I've tried what you said, but the: Sum(SJoin::NumField)
gives me an error, since Numfield doesn't exist...

Here is the complete story about my application:
- Only one file holds all the working hours in a year;
- Field (number) Accum_week holds the over time in a week

Now, when I sort for a particular week, I'd like to total all of the Accum_week, even if only one is showing on the screen.

TIA

Sébastien Royal

 

[Schlogg]

I used 'NumField' as the field you want to total because I'm not sure just which field you are trying to total.
Sum(SJoin::Hours) will give you the total of all 'Hours' in the file.
How do you get 'Accum_week'? You imply it's defined as number but is it in fact a Summary field?

Cheers,
Paul J.