Summarizing Data

[TattyDon]

I wonder if someone might be able to point me in the right direction. I have a database where the main data is in one table which stores timecard information. It’s linked to several other tables but that probably isn’t too relevant.

I designed it to enter and view data at line item level i.e individual days and the interface that I have for that works well showing a week at time at day level.

What I really want to do though is have another layout which gives me a week by week summary. I could then click on a button to go the more detailed view.

My question is how best to do this. I have messed around with summary parts but haven’t really got them working – I’m not really sure though that that is the best way to go about it. I’m wondering if there is a way of creating a table that takes the line item data and rolls it up to the higher level. So if it finds 10 entries for week 49, it would hold one entry for week 49 in the new table and also calculate the relevant total hours booked by job code or employee.

Does anyone have any advice – or indeed books or articles that might help? Most of the books are great at the detail but lacking in examples on how you should design and present the data. I’m part way through David Kachel’s White paper for FMP Novices which is excellent and pretty unique in it’s nature but is still more about the concepts than examples..

Thanks in advance

John

[comment]

If this is just for viewing records by the week, then all you need is a relationship. It can even be a self-join relationship (there's no point in having a table of just dates). Use a global field to select an arbitrary date, have two calculation fields to determine the week's starting and ending date, and define the relationship to include only records in that week.

[TattyDon]

No, that's not the problem. I can do that - that's how the entry interface at day level works. What I don't know how to do is to summarize the data so it shows all the records for one week as one record. e.g week 1: sum of hours charged.

[comment]

You just summarize the related data using aggregate functions.

[gmmac]

Hi there,

I do something like this for my invoicing--giving week rather than daily totals. But it only works as a report--summarizing the weeks hours and $$. I did this by creating a summary part that sorts by the field "week ending" which is a calc field that looks like this (my week starts on Sunday):

If(DayName(Date) = "Sunday"; Date + 6;

If(DayName(Date) = "Monday"; Date + 5;

If(DayName(Date) = "Tuesday"; Date + 4;

If(DayName(Date) = "Wednesday"; Date + 3;

If(DayName(Date) = "Thursday"; Date + 2;

If(DayName(Date) = "Friday"; Date + 1;

Date))))))

I have 2 summary fields "week hours total" and "week rates total" which total number fields; the same summary fields are used in a grand trailing summary for grand totals.....

It is difficult to come up with ways to summarize data in a layout rather than in a report--if you play around with the "GetSummary" function you may get results. Sorry I don't have time to work on it now....if I have any new ideas (or memories!)will repost.

My file is admittedly a bit of a hack (since it's mine not a client's ) but if it might help I can send it along.

Hope this helps.

[comment]

... sorts by the field "week ending" which is a calc field that looks like this (my week starts on Sunday)

If(DayName(Date) = "Sunday"; Date + 6;

If(DayName(Date) = "Monday"; Date + 5;

If(DayName(Date) = "Tuesday"; Date + 4;

If(DayName(Date) = "Wednesday"; Date + 3;

If(DayName(Date) = "Thursday"; Date + 2;

If(DayName(Date) = "Friday"; Date + 1;

Date))))))

Which could be written simply as:

Date - DayOfWeek ( Date ) + 7 

Or, to calculate the week's starting Sunday:

Date - DayOfWeek ( Date ) + 1

[gmmac]

Ah, yes. A hack, as I said, and I am always making things more complicated than necessary. Thank you!

Meanwhile, I have made a table layout with a new related table joined by a new (date) "week ending" field (which joins to the [calc] week ending in first table). Other new fields use the GetSummary function to get week totals, and if you don't mind sorting every time you enter the layout, John, it does what you want it to do.....

you do have enter in existing week-endings into your new table, but if you have data for each week you could auto-enter by calculation pretty quickly. Or maybe someone else has a good idea for that. I have to stop procrastinating now myself!

[TattyDon]

Thanks for the comments guys. I got round this in the end by relating the tables (which was the first piece of advice offered!) Until I looked at the timebilling starter solution which came with FM9, I hadn't grasped the concept that through a relationship you could have a one to many relationship. Through that I can get my summaries with my layout showing a parent record and child records through a portal.

I had got bogged down in the layout parts bit - thinking that I needed to use sub summary sections to get any sort of total. As was pointed out by Gmmac, this didn't work for me because you then couldn't use the resulting report in browse mode!

I haven't tried the GetSummary function yet but that looks interesting as well.

Finally, thanks for the quick formula for working out the start of the week. My code looked liked Gmmac's version. I was sure there must be a logical formula but couldn't see it!