Difficulty working with summary totals across layouts to create complex table

[Rramjet]

I am not sure if I can explain this well enough but here goes...

I have a database where a number of clients are recorded in a Master table.

Each client can participate in a number of different programs (for which a child Program table is created).

Each client can then have a number of separate contacts with the company in each program, (which are recorded in a Sessions table via a portal in the Program Layout).

Thus one client could for example have 10 sessions in Program A and 4 sessions in Program B (each on a different Date).

Each session also records the Duration (mins) for that session.

I want to be able to summarise the number of sessions over a specific set (group) of programs by month and year and display the information in a table.

Across the top of the table the column headers read: For the year xxxx -

Monthly Total Sessions; Monthly Average no. Sessions; Year-to-date Total Sessions; Monthly Total Duration; Monthly Average Duration; Year-to-date Total Duration

Down the left side the rows titles read:

Green (ie; summary of Programs A, B & C totals)

Red (ie; summary of Programs D & E totals)

Blue (ie; Program F totals)

etc... (there are 15 of them in all)

Now...

GetSummary only works if the parts are sorted according to the specific break field so that simply does not work.

However,

In a specific Program Session Layout I can create Month(Date) and Year(Date) fields in order to indicate the Month and Year fro the date of the session.

I then create both a "Month_Select" and a "Year_Select" field so that I can set these to the specific month and year the data is required for - and thus I can calculate:

Number_of_Sessions [calculation field] = If(Month=Month_Select and Year=Year_Select; Count(Session_ID); 0) {I could also use the Case function} and

Number_of_Sessions_Total [summary field] = Total of N_Sessions

and this gives me a month and year subtotal according what I enter into the Month_ and Year Select fields.

I can then use the same logic to create total Duration.

Now the problems begin.

I can display these totals on a Sessions layout specific to each program (drawing the information directly from the Sessions table for each).

However, if i want to diplay them all on a single layout (as a precursor to constructing the above table) it simply does not work.

Not only that, if in the "precursor" layout I create Year_Select and Month_Select fields (for that is where I want to be able to "select" this information) and pick them up

by calculation back in the sessions table, the "select" fields change (back in the sessions layouts), but the totals no longer change accordingly

(as they did when I "selected" directly in the sessions table layout - even when I direct the above calcualtions to get the Month_ and Year Select information from that layout).

Now a "way around" this could be to create the "precursor" layout/table full of global fields and a script that goes to each of sessions layouts

and in turn copies then pastes the summary totals back into the global fields.

But two problems arise:

1. I cannot get the copy/paste functionality to work if there is no layout present

(ie; I cannot simply point to a table and copy select/paste select the relevent field if a corresponding layout with the fields is not extant -

and if a layout IS present then I have to use Go to layout 1 > Copy field > Go to layout 2 > Paste field). (I cannot get Set field to work...)

2. I cannot get the Year_ and Month_Select variable to affect a change in the Sessions totals layout fields from the "precursor" layout anyway...

(the sessions Month_ and Year_ Select fields pick up the information change okay, but the summary totals do not reflect the change...)

I am frying my brains over this one.

In the past I have worked around this problem by creating individual month fields for each category of summary data I want

(and in one case ended up with over 1400 fields - plus a copy and paste script to boot - plus all the extra layouts to operationalise this procedure...)

...but I do NOT want to repeat that process ever again... there must be an easier way to do it surely?

..probably missing something really simple (again) but any suggestion would be greatly appreciated...

Best regards, Rramjet

[Rramjet]

Okay, I am part way there...

From: http://fmforums.com/forum/showtopic.php?tid/213200/ I get:

"Build a cartesian relationship from Table C to Table A using the "x" relationship which allows Table C to "see" all records in Table A."

That works... (my "precursor" table now has a cartesian relation between all the different Program Session tables. This requires of course that another instance of each session table is created but I can then create a calculation field in the "precursor" that = the session field (Total_N_by_Month_and_Year or Total_Duration...) in the Session_2 (cartesian) tables. (remember the Total_N...were themselves summary calcs in the session tables drawing from If(Month=month_select and Year = year select; N; 0))...

EDIT: Actually, it works only in the first instance...but then won't update to reflect some changes in the sessions table (the problem seems to be the selection variables - adding and deleting records DOES affect change that is then reflected in the "precursor" table... but when a change is affected via the selection variables, that change in totals is NOT reflected in the precursor table...hmmm...)

Now I can display all the different Program Session totals in the one file (which is related directly to the Master table of clients via a SN and by cartesian to second instances of session tables)

Hopefully I can now summarise those fields in this table...

I still have the problem of the "selection" fields, when changed in the "precursor" table (and picked up in the Session tables) not affecting the required change in the sessions tables... I still have to go individually to the sessions layouts to change them there. A workaround could be to have a copy/paste script that copy/pastes the selection field info into the various session layouts...but that's clunky...must be a better way...

Edited May 22, 2010 by Guest

[Rramjet]

...stranger and stranger... If I affect a change in the sessions table...and then go back to the "precursor table (which has 0 records) and create a new record and then toggle the green "Found" button in the FMP toolbar... then the new record then reflects the changes made...

Perhaps I will have to script this somehow... Delete the old > create the new > show the new...

EDIT: interestingly, if I change the selection field before I delete the old, then I create new, then toggle the "Found" button, this seems to force the selection fields to operate properly back in the sessions tables...

Edited May 22, 2010 by Guest

[Rramjet]

Final update:

Master Table (Client demog. etc info, SRN)

Program Table (linked to Master by SRN)

Session table (Linked to Program table by SRN - shows date and type of all contacts made in a specific program and shown on Program Layout via a portal)

Report table (linked to second instances of all Sessions tables via "x" - cartesian relationship)

In Sessions table create all the totals variables needed. In my case each is conditional on a Month and Year and Activity type (of session)

Activity_1_Duration (calculation) Case(Month = Report::Month_Select and Year = Report::Year_Select and Activity_type=1; Duration)

Activity_1_Duration_Total (summary) Total of Activity_1_Duration

In the Report table construct calculation fields that pick up the Totals fields from the sessions table

There are no records in the Report table and all options for creating and deleting via the relationship are turned off (left blank)

A script is triggered (Set Script Triggers... > OnObjectModify)set on both the Month_Select and Year_Select fields on the Report layout (both fields have a dropdown list: month numbers and years)

Delete All records [No dialog]

New record/Request

Omit Record

Show Omitted Only

This is important because FMP seems to need this "toggling" of the new record to get the selection fields operationalised back in the Sessions tables and also so that the new record (in the Report table registers the changes made after the selection variables have been selected and operationalised in all the different tables.

The delete all records is necessary because every record created "remembers" its own selection and totals fields and cannot be changed via the selection variables(this might be useful for some - but I find it confusing).

Anyway, this all means I can have a complex report table (with as many rows and columns of any differing calculable nature as I want - no need to sort by anything), drawing information from lots of different databse tables with different clients and session info, all in the one place.

The only downside is that the larger and more complex the report table, the longer it takes FMP to calculate the result... can't have everything I suppose... :

Hope this might be useful for others.

Regards

Rramjet

Edited May 24, 2010 by Guest

[bruceR]

"can't get copy / paste to work"

Don't use copy / paste, use set field.