Reporting per day

[marinjo]

Hi everyone,

I'm new to FM, started just two weeks ago, and I managed to solve all my issues so far by using this forum (and some other sources). I thought I'll use some 10-15 variables, now ended with >100 fields, global variables, conditional lists, relations, all types of layouts, printing, backing-up...

So to get to the point, my records get admitted at some date and they get dismissed at some date and they have some events occuring or not (checkbox with values 1 or blank). I need a report with number of records with each event checked on a given day (so the conditions for each day are: day admitted <= day tested and (day dimissed => day tested or day dissmissed = "") and event = 1. The report should list days from 1 to 30 (or 31 or 28/29 for February). I got stuck with a table, not being able to fill the fields with calculated results... (check photo)

Any ideas, suggestions? Thanks in advance.

[comment]

 I don't understand your description: suppose you have a record admitted on 5/10/2016, and dismissed on 7/10/2016. And suppose this record has Event A checked. Would this event by counted 3 times in your report, once for each day? That does not seem to be a helpful way to present the information.

You didn't say what this solutions tracks in real life, but I suspect it would be best to record each event as an individual record in another table - preferably with the actual date of the event, if you know it.

[marinjo]

Yep, it's not so obvious, but each record should be counted for each day, and the day that event occured is not so important, it's the number of events that persist on a given day that is important. Let's say that the event says something about the state of the record (kind of severity grading) and the number of records with this level od severity on each and every day is important. Even if the number is 0 it should be reported (which kind of makes subsummaries unuseful).

[comment]

Okay, then. The first thing that needs to be clear here is this: if you want your report to show every day of the selected month, even if you have no records for that day, you will need a reporting table with 31 permanent records numbered serially and global fields for the month and year to report on. The relationship between this table (Days) and your existing table (Records) will be:

Days:cDate ≥ Records::DateAdmitted
AND
Days::cDate ≤ RecordsDateDismissed

where cDate is of course a calculation field =

Date ( gMonth ; SerialNumber ; gYear )

Then you can count the related checkbox field in order to get the number of events that should be shown in each day's record.

Your reporting script needs to look at gMonth and gYear and omit the appropriate number of records from the report (e.g. the last 3 records if it's February in a non-leap year).

 

Edited November 6, 2016 by comment

[marinjo]

You're the best, FM rules!

Just to get things right, I made relationship, and I made a script to populate the table. The script will be executed upon call (pressing the button for report).  I made this simple just to check if I'm getting it right, I'll set "omit" for shorter months later on...

Sorry for posting scripts as a photo...

Thanks a lot. Just as always, this cleared few new things for me...

[comment]

3 hours ago, marinjo said:

I made a script to populate the table.

No, that's not a good idea. Those two fields should be calculation fields, and they should calculate automatically. Otherwise if two users are viewing different months at the same time, one of them will see incorrect data.

[marinjo]

Yes, calculation works better, thanks again.

However, I have one more issue. This version can not add records that are admitted, but no dismissed date is entered (these records are still "onboard" and awaiting dismission in days ahead). Such record should also count in.

So I made a new calculation field in main table that will fill with current date if no dismission date is enter, else it will be the same as the date dismissed. I entered this date in relationship and now I have all functioning well.

Thanks alot! With every new issue solving I'm improving... this even makes some fun! 

[comment]

3 hours ago, marinjo said:

So I made a new calculation field in main table that will fill with current date if no dismission date is enter, else it will be the same as the date dismissed. I entered this date in relationship and now I have all functioning well.

I am afraid it's more complicated than that. The calculation field must be stored in order to serve as a match field in the relationship. OTOH, if it is stored, it will not update when the current date changes. If it fits your business rules, you should make the calculation field use Date ( 12 ; 31 ; 4000 ) as the default date (so that open records will be counted until the end of the viewed month).