Dynamic relationship or something ..

[David Holmberg]

I'm creating a training diary solution. I have a layout showing statistics for the training. It contains maybe 30 fields looking like this:

sum(Relation1::sport1)

next field

sum(Relation1::sport2)

and so on .....

The relation looks like this:

field 1

(activityuser & " " & year(activitydate)

field 2

(globloginuser & " " & globyear)

On the layout the user can choose year number. But I want the user to be able to choose if he shall get stats from a year, month, week or between two dates just checking a checkbox and filling a globalfield. All on the same layout. Can I get the relation to change depending on what the user chooses?

Or will I have to write all the stats calcs like this:

Case(

checkboxyear=1;sum(relation1::sport1);

checkboxmonth=1;sum(relation2::sport1);

and so on ...

Hope I'm clear enough ..

[-Queue-]

I think you may be able to use GetField( ) for this one. But I'm not really sure what your structure is. Could you post zipped/stuffed clones of the relevant files?

[Ender]

To get stats from "a year, month, week or between two dates" means matching keys in the parent file with different keys in the child file. I don't think this can be done with one relationship. Let's look at the data for the keys:

Parent file's key User_Week (when week chosen=23 and current user=hsimpson):

hsimpson 23

Child file's key User_Week

bsimpson 1

hsimpson 1

msimpson 1

bsimpson 2

hsimpson 2

...

bsimpson 23

msimpson 23

hsimpson 23

bsimpson 23

...

Now the parent file's key for User_Year:

hsimpson 2003

Child file's key User_Year

bsimpson 2003

hsimpson 2003

msimpson 2003

bsimpson 2003

hsimpson 2003

...

bsimpson 2003

msimpson 2003

hsimpson 2003

bsimpson 2003

...

The key in the parent file is not hard to calculate based on whatever is "checked", but combining data for the match field is another matter. Hmm...maybe if the match field contains each of the possible matching criteria, and it's clear which is which:

User_Match = activity_user & " week" & weekofyear(activitydate) & "[par]" &

activity_user & " month" & month(activitydate) & "[par]" &

activity_user & " year" & year(activitydate)

And the key in the parent file would be something like

activity_user &

case(gRadioChoice = "Week"," week",

gRadioChoice = "Month"," month",

gRadioChoice = "year"," year") &

gUserInput

Just might work. The date range thing is another matter.

[-Queue-]

If Ender is on the mark, then the parent key can be simplified.

activity_user & " " & Lower(GetField("gRadioChoice")) & gUserInput

[David Holmberg]

It works, thanks guys. I wasn't anywhere near this solution myself so it feels like the guidance was sent from heaven !!

Still it's really important for me to use the date range,

[Ender]

Well, date ranges are complicated, so put your thinking cap on and read this:

http://onegasoft.com/tools/smartranges/index.shtml

I got them working in a couple places, but it took some work. In FM7, date ranges are a simple matter.

[David Holmberg]

I'll get right on to it