Problem with layout and summary fields

[barryfh]

Could someone please explain to me whether the following problem with summary fields is due to 1) how I’ve (mis)designed the database (should it be multiple tables instead of the current single one; if so, how should the tables be designed), 2) how I’ve designed the summary fields, 3) both, or 4) something else? If the database needs redesigning to fix the following problem, how can I incorporate reporting grand totals for each fiscal year in addition to totals for each month?

The goal of the database is to track numbers of events and participants at each event, summarizing these two quantities each month and each year for each of seven categories of participants (“administrators,” “anyone,” “others,” “students,” “scholars,” “U.S. students/scholars,” “volunteers”).

There currently is no unique identifier in the database because there are no unique individuals.

Some of the events occur regularly within and between months. Others occur regularly only between months. Others occur intermittently.

The problem is illustrated in the screen capture file named “disaggregated report” in the attached zip file.

The problem began in the month of March when the data included separate values for two or three of the above categories for a given event; e.g., beginning in March, we had separate counts for “students” and “scholars” for certain events whereas in previous months the numbers were lumped together (and categorized as “anyone”).

In other words, beginning in March, we have multiple records for a single event.

To prevent a single event being counted more than once (i.e., when an event had separate counts for the multiple categories of attending individuals), I redesigned the database so that a single record for a given event tracked each category of individuals separately via separate fields.

The unintended side-effect of this is that the disaggregated report is giving incorrect counts as a result of lumping together some of the above categories rather than listing them separately. This is shown in the screen capture file named “disaggregated report” in the attached zip file; under “April,” the fourth (unlabeled row) is incorrect as is the first one, and instead there should be separate rows for “US students/scholars” and for “volunteers.”

Hope the above is sufficient information. Otherwise, I will be happy to clarify as requested.

Thanks very much in advance!

summary_fields_problem.zip

[Søren Dyhr]

My initial hunch tells me that eventhough the registration contains several facts per record, should these be split up in several records, and then summarized.

This could be done by pilcrow stacking the fields in internal calc'variable and then by means of Get(CalculationRepetition) and an export to normalize the data.

Next issue is that some of the events are recuring, which points in direction of multi criteria relations, to convey the same records show up several times - But I think I read it like it was the starting date should shown at???

Finally is the same reporting, the only difference is that, one uses a body-less layout.

Anyways, I gave it a stab ...look at the attached template and let me hear what you think??

--sd

test.zip

[barryfh]

Hello,

Many thanks for taking time to reply.

I still need to look at your file. Meanwhile, though, your explanation is beyond my skill level ("plicrow stacking the fields in internal calc variable and then…").

Body-less layout?

Will get back after inspecting your file.

Have a great weekend!

[barryfh]

Hello Søren,

I have inspected the file you provided.

Could you please explain to me; how does it work and solve the reported problem in my initial post?

Many thanks!

[Søren Dyhr]

This is the main problem??

In other words, beginning in March, we have multiple records for a single event

By splitting several registrations in one record for an event into several atomic ones anyway, and them summarize! But perhaps isn't the model sufficient enough - If you have the same event recuring 4 times inside march, will the event counter be fooled!

But the selfjoin relation can be a multicriteria and the script can emliminate the empties, by deletion... instead of the omitting in the present version.

But I need more explanation!

--sd

[barryfh]

Hi Søren,

Many thanks again for your time and help.

Could you please clarify what you want me to explain? I think I understand what you want, but maybe not.

In any given month, we could have an event (e.g., "orientation" for scholars) that occurs more than once (and it occurs in more than one month). We need to count each unique occasion that the event happens in that month, even though the name of the event is the same. So if "orientation" for scholars happens 3 times in March (each of which is attended by different individuals), we need to count that as 3 events and count the number of scholars who attend each "orientation."

Hope that helps!

I have inspected your test file further and have more questions in addition to the ones in my previous replies.

Custom functions in the template—Blanker, SplitIntoRepeat—how did you create them, how do they function?

Two tables in the template—test, reciever—how do they function? How did you enter the records in reciever?

The table containing the events' records was missing a field for Students, so I added it. Also added Scholars & "¶" & to the ValueStack field in the test table.

Then I imported my real records into a copy of your file in which I first deleted your 4 records. I also manually computed the summary values for December and January (I will check the other months when I have more time available).

In this new test file's aggregated report, the numbers of participants are correct (great!) but the numbers of events are off.

December actual=7 report=5

January actual=30 report=15

In this new test file's disaggregated report, all of the numbers of participants and their numbers of events are correct (great!). Only the numbers of events for the months are off. Also, the disaggregated report is missing the values for Students (presumably this has to do with the number of repetitions for two calculated fields; I ran out of time for playing with that to confirm).

How do I fix this?

Also, how do I get grand totals of number of events and number of participants for each year?

I have attached this version of your test file.

I will need to add many more fields for data collection purposes (e.g., the names of the people who organized each event, the location of each event, etc.). However, they will not be used for summary reporting or statistics (at least for now!).

Thank you again very much!

test.fp7.zip

[Søren Dyhr]

Alright recuring event's! It's the tougher of the ways. One way to solve it right away is rename these in a similar way as this.

http://www.databasepros.com/FMPro?-DB=resources.fp5&-lay=cgi&-format=list.html&-FIND=+&resource_id=DBPros000121

However - I'll deal with the quick answer now, and then modify the template accordingly later, tomorrow posibly?

Custom functions in the template—Blanker, SplitIntoRepeat—how did you create them, how do they function?

It's easiest to enter file>Define>Custom Functions... and study how they're done and what they do. "Blanker" replaces a field with a void when the entry is 0, while SplitIntoRepeat is a typecaster from a pilcrowdelimited list into a repeating field.

How did you enter the records in reciever?

This is what the repeater is used for, becasue when you import between tables, are you prompted if you wish to have all repeating values in hopefully dito in the reciever or you wish to split into separate records.

I hope I won't keep in unbearble suspense until tomorrow, where I need to expand the repeater as well as fledge the selfjoin relation with further a criteria. The omission of the empties isn't enough as I said, they do simply need to get deleted in the imported set.

--sd

Edited April 22, 2007 by Guest

[barryfh]

Hi Søren,

Many thanks again for taking time to help.

Did I explain and answer your question satisfactorily?

Thanks also for the link to "serialize by category." I might have more questions for you after I have studied it.

I also will have a look at File>Define Custom Functions>Blanker and SplitIntoRepeat.

Your FileMaker and database skills are much more advanced than mine, and I would like to learn from this so that I can be more self-sufficient in the future.

As such, perhaps my biggest (most general) question is this. How did you know/decide that your test.fp7 would meet my needs in this project?

If this is documented in FileMaker's user guide, I must have missed it!

Thanks and cheers!

[Søren Dyhr]

How did you know/decide that your test.fp7 would meet my needs in this project?

I didn't, it's more that I pick up a challenge to see if I by the means of the skills I have availiable or by inventing something along the path can nail down the core to the problem.

It's just about the only way you can get outfitted to later make proper improvisations in the future tasks, you might get involved in. Similar will you expirience if your ramblings are made public enough, will someone more skilled jump into the debate, with just neater and cleaner solutions.

Take a look at the changes to the template and see if I have interpreted your problem correctly?

--sd

testsugg2.zip

[barryfh]

Hi Søren,

Thanks for the reply.

Understood.

Will do and will answer your question as soon as I've studied your new file.

Thanks!

[Søren Dyhr]

Oops, just caught myself in a tiny error, the value list is build upon dates, which is wrong... should be the eventnames, provided the event occures several times the same day!!!

--sd

Edited April 24, 2007 by Guest

[barryfh]

Hi Søren,

Thanks for the message.

Each event occurs only once on any given day.

I am still inspecting your revised file, double-checking its values against manual counts. Will reply as soon as I have completed the double-check.

Many thanks again!

[Søren Dyhr]

Well my wordings were a little slippery... some of the participants in the events might seek several events the same day, hence the urge to correct this slight error!

--sd

[barryfh]

Hi Søren,

Many thanks again for your time and help.

I've inspected the custom functions, "new script," and the two tables. I'm still missing the logic of how this all works.

I've also compared the numbers generated by the two reports with my manual counts of events and participants in February and March.

Your new file's counts are correct for the numbers of participants for each event in February and March. Excellent, well done!

The new file's counts are also correct for the events attended by each category of individuals within each month. Trés bien!

Only two additional fixes remain, both of which concern the numbers of events.

1. For March, the actual number of unique events is 24 rather than the 42 in the disaggregated report.

I'm guessing that some events in March are counted 2 or 3 times because more than one category of individuals attended those events; for example, record #77. The count needs to be based upon unique events during the month rather than on the counts of categories of individuals attending each event.

This problem almost certainly will happen for April and subsequent months, also. Due to reporting requirements, we began collecting data for the separate groups (scholars; students; etc.) at several big events in March; for those same events in previous months, we merely counted everyone together and represented them as "anyone."

2. The values at the top of the disaggregated report (128 events, which is incorrect due to the above problem; 4184 participants) are totals for all of the months. Are these totals for the fiscal year 2006–07? Beginning with July 2007, will a new set of totals for fiscal year 2007–08 display at the top of the report?

Again, thank you very much and cheers!

Barry

Edited April 25, 2007 by Guest

[Søren Dyhr]

I'm guessing that some events in March are counted 2 or 3 times because more than one category of individuals attended those events; for example, record #77. The count needs to be based upon unique events during the month rather than on the counts of categories of individuals attending each event

Allright I found the 24 events in march!

Take a look!

--sd

testsugg3.fp7.zip

[barryfh]

Hi Søren,

Many thanks again.

I will inspect/test and report back to you as soon as I can.

Cheers,

BF-H

[barryfh]

Hi Søren,

I really appreciate your time and help.

The new file (testsugg3) indeed fixes the total number (counts) of events for March in the disaggregated report.

However, the total number (counts) of events for January and February are incorrect now.

January actual=30 report=27

February actual=24 report=21

Also, the grand total number of unique events for fiscal 2006–07 is missing from the top of the disaggregated report in this new file (the grand total number of unique events did display in the previous file, although I believe it was incorrect).

In the new file's aggregated report, the monthly numbers of unique events are missing. Only the monthly numbers of participants display. This is a regression from the previous file; its aggregated report displayed monthly numbers of unique events (although some of the values were incorrect).

Again, thank you very much and cheers!

BFH

[Søren Dyhr]

January actual=30 report= 27

February actual=24 report =21

D*mn (pardon my French) - well some other kinds of mixing event's is going on apparently, it need to inspect it further... but as such is it a good indicator what needs to be done... Some events are recuring and others are not how are they easily distinguished - not by the name alone??

Should I put some efford in that first, or would you like me to explain what I've done so far? You might come up with something when understanding what's going on.

But I hope you can see that posting your question without data, was too far a fetch to get proper reply ...others might have seen what was comming, but I didn't I'm afraid - but I've learned a few things, thanks to you - which is great!

--sd

[barryfh]

Hello Søren,

Regrets for the delay in replying (thought I had, but evidently not).

I'm open to both your suggestions; can you inspect further and explain to me what you've done so far?

The only way I can think of, based upon my lesser developer skills than yours, to distinguish each event is to configure FMP to assign a unique ID to each event rather than using name alone.

Please let me know if it would help for me to upload a file containing data. Regrets for any inconvenience.

Many thanks again for your time and help on this!

Barry

[Søren Dyhr]

to distinguish each event is to configure FMP to assign a unique ID to each event rather than using name alone

Bravo! This the kind of conclusion I needed to hear, because as it is by now are things mixing on the names alone, ideally should each event be pushed into it's own table, and the reporting should be on ushered or piped in values via the ID's...

Ha, ha! You really made me think of you with mixed emotions, but life contains such incidents as well ...it can't all be smooth sailing.

So indeed throw me some new data, after having the event being broken out into it's own table.

--sd

[barryfh]

Hi Søren,

Many thanks for replying.

I will be happy to repare a new file. First, though, could you please explain to me the logic behind having the event in its own table? And could you please give me more details about exactly how you want the file structured?

I would like to prepare the file properly the first time, rather than making some mistake that spends time needlessly. Hope this makes sense.

Mixed emotions?

Thanks,

Barry

[Søren Dyhr]

First, though, could you please explain to me the logic behind having the event in its own table? And could you please give me more details about exactly how you want the file structured?

Before I get too deep into an explanation, should you watch this video:

http://www.filemakermagazine.com/videos/data-tagging-classification-vs-organization.html

...because what I have done previously was a scripted normalization, which would be completely irrelevant if you structure your data, the way the film does it. I have made the templates entry of data as I would prefere it was done in the first place instead of mixing values in one field.

By having what I feel is a better structure on data, does it also mean that eventhough an event has more attendance over a period of time will it keep it's unique ID, and this does indeed come in handy here:

http://www.filemaker.com/help/FunctionsRef-345.html

...eventhough the filtering have many holes to sift a value thru will the cartesian relation only give one of each occurence since the ID is unique and serialized!

I appologize to having replied with the cruize control turned on, by saying: 1 NF violation and not having the stamina to protest properly - I should have kept myself on the straight and narrow and not provided you with crafty workarounds that confused myself as well.

--sd

participants.zip

[barryfh]

Hi Søren,

Thank you for the prompt reply, and for your time.

OK, I will study the video and see whether I can understand what you want done in restructuring the file given that these topics are completely new to me. It likely will take a couple of days.

For efficiency, I would prefer to restructure the file correctly the first time rather than making mistakes and having to rework it.

In reading your last post, I have only a vague idea of what you want done. What you've written makes sense to you (and other advanced users) because you're already familiar with what you want done.

Therefore, could you please give me any additional relevant explanation that would increase my probability of getting it right the first time?

Many thanks!

BFH

[Søren Dyhr]

Therefore, could you please give me any additional relevant explanation that would increase my probability of getting it right the first time?

Strictly speaking is it this:

http://en.wikipedia.org/wiki/Database_normalization

But, hopefully am I not generalizing too much here? ...the majority of filemaker developers have learned this like small children adabt a language, it's not something with coloumns of grammar ...they just do it, and the more exercised, the better they nails the finer details! But perhaps is a good place to start here:

http://www.digfm.org/ref/FM7_key_concepts.pdf

--sd

[barryfh]

Hi Søren,

Thank you again for the prompt and helpful reply.

As indicated in my signature, I am a novice with regard to FMP development (although I have ~5 years of experience as an end user).

I have used various databases (client/server; mainframe) with multiple tables for the past 15 years and understand how they're related. However, db theory (e.g., normalization) is beyond the scope of my experience.

I will study the information in the links you've given me, and see how much of it I understand.

Meanwhile, is your plan for me to rebuild my existing database so that it has multiple tables instead of a single one? If so, how are you deciding what fields will be in which table so that the reports' output will contain correct calculations?

[barryfh]

Hi Søren,

I just looked over the tables and relationships in "participants.fp7."

FYI: they are beyond my level of understanding for how you designed them and how they function.

[Søren Dyhr]

is your plan for me to rebuild my existing database so that it has multiple tables instead of a single one? If so, how are you deciding what fields will be in which table so that the reports' output will contain correct calculations?

Yes it is, the next part of the question is way to complicated to answer briefly, when you didn't discover that managed to sneek in an extra table in previous attempts to nail your problem - I might only be able to explain it consicely with words in danish, however I can only suggest you find a source to read up upon many-to-many structures, and perhaps something that tells you why normalized data is easier to summarize on.

The nearest I can come up with, that explains normalization with Filemaker as focus, and why we bother at all - is this:

http://www.amazon.com/Advanced-FileMaker-Techniques-Developers-CD-ROM/dp/1556228597/ref=sr_11_1/103-6679574-7986224?ie=UTF8&qid=1178728426&sr=11-1

But the problem is that it deal with fm5.5 problems, many of which have become much more easy to solve with the newer versions.

But as such are you tought your way thru the normal forms by following this books methods strictly:

http://www.amazon.com/Database-Design-Mere-Mortals-Hands/dp/0201752840/ref=pd_bbs_sr_1/103-6679574-7986224?ie=UTF8&s=books&qid=1178867320&sr=1-1

...but it isn't written with filemaker in mind at all!!!

--sd

Edited May 11, 2007 by Guest

[barryfh]

Hi Søren,

Thanks again for replying. I really appreciate your time and help. Your English is much better than my Danish. ;^)

The new file ("participants") has incorrect total number (counts) of events for January and February.

January actual=30 report=14

February actual=24 report=12

March actual=24 report=11

Another problem with events in "participants" is related to the counts for the categories of participants. In January for example, the report lists 1 event attended by Admins, 4 by AnyOne, 3 by Scholars, and 9 by Students. If you add these numbers (1+4+3+9), you get a total (17) that is different from the report's summary total (14) and is different from the actual total (30). It looks as if this problem may be happening for the other months as well (rather than specific to January).

So this leads me to wonder whether "participants" has a design problem or a calculation problem. If it is a design problem, will the fix involve a minor change or a big one that will lead to my needing to watch a different video and/or learn different aspects of database theory than the above mentioned ones?

I have to be careful about this because of a deadline.

Thanks again and have a great weekend.

BFH

[Søren Dyhr]

The new file ("participants") has incorrect total number (counts) of events for January and February

I'm fully aware of it, but with the data you've provided, have events wearing the same name been made incidents of the same event. This means you need make more records in the event table and move the incidents that needs to belong to it instead occures under it in the portal. This is the only way it can get it's own ID to make the number larger.

I might have merged too many records into the same portal, but it's just becasue no special distinction was made between them, to notify if they were a single or a recuring event - garbage in garbage out! It's not the data nor the tablestructure you can blame here, it only count the number of different event id's in each grouping.

--sd

[barryfh]

Hi Søren,

Because of timeline issues, we found a FileMaker developer at my university who rebuilt the database and solved the problem.

Thank you so much for all your time and help on this.

Barry