Hello,

Its been a little while since I posted last…everything is going OK, until now.

I really need some suggestions on how to go about this new problem.

I currently have a form that I would like to develop in FM.

The form is a monthly calendar which has numerical stats for about 25 questions.

Currently the form is laid out in Excel and looks like this (of course with the grids)

NOTE: I cant get the example below to align properly…so if you will imagine that each bit of data is in its own grid in the Excel sheet (the numbers would of course be under the listed dates).

.............. July 1 July 2 July 3 July 4

Question 1: 44 0 0 1

Question 2: 8 33 5 3

Question 3: 12 7 22 18

I need to be able to add the totals up for each month for each question.

I would like to get some ideas as how to go about creating a calendar layout of this nature, as I have drawn a complete blank and cant get my head around how to go about it. I need the calendar layout to be able to extend at least 5 or more years ahead.

Or is there an easier way to accomplish this without trying to do it in a calendar format?

Once again, thanks.

James

Edited June 21, 200718 yr by Guest

One way to do it is to have a global field for the "beginning date".* Then a series of unstored calculation fields, gDate +1, gDate+2, gDate+3, etc., for as many as you want to show columns.

Then create a self-relationship from each of these to the date field of the data, AND QuestionID=:QuestionID. I'd recommend that you create a separate Table Occurrence Group (TOG) and table occurrence (TO), of your data table, not connected to the normal "anchor" table occurrence for that table. This keeps the Relationship Graph from being clogged up by all these "display" relationships.

Assuming the numbers you show are just the "answer", then the fields across the layout (under their gDate1... column header) would just be: gDate1::Answer, gDate2::Answer...

If it is not just the answer, but a sum, ie., multiple records for each question, with a number field to add up, then you need a Sum() calculation field for each of the columns.

Sum ( gDate1 relationship::answer)...

*The beginning date in your case would be the 1st of a specified month; which would make it a calculation itself based on a global choice of month (number) & year. To see the "next" or "previous" month you'd just have to increment the choices either up or down. You can have arrows to do that, as well as boxes to enter a Month & Year.

If you don't want to show extra data and column headers for shorter months, then the last few (remember Feb) gDate calculations can check that they're still in the same month; or use a Date() function, with Day+1..., to do the dates; that's probably better in this case.

Since the column headers are calculations with a Date result, you can use FileMaker's format options to show the date any way you want.

Something like that. There may be more clever and less relationship intensive methods, using repeating fields.

Edited June 21, 200718 yr by Guest
Format column headers

Thank you,

Im not sure I can pull all that off at my current working level, but I will give it a good try.

J