Crosstab Absence Sheet

[Dimitrios Fkiaras]

Hello to all,

     I am building a solution, where the user needs to see a crosstab absence sheet of their personell. The people there can be absent for a variety of reasons, (absence types) which are recorded. The users want to be able to select a date and see the absent personell for a certain date range.

I tried very hard to find the a way to do this, so that the app will be responsive. The solution is accessed via WebDirect  (so I tried to avoid the Window Refresh script step)

So far I have come with two ways to do this. The first way is based on a repeating calc and the second one on a summary field (which summarizes a repeating calc) 

See the attached files (the records in the real solution, will not be as much. Maybe 1/10th.)

How would you approach this problem? In the past I tried to solve this problem with filtered portals in each date, or with virtual list. Both solutions didn't seem to be fast enough (although I admit I haven't spent the time to optimize them, like the current solutions). I also thought about creating a summary table somehow but cannot finish my though on this.

I suspect that there may be  a better way to do this (maybe without calculations, only relationships). Any help is greatly appreciated.

Thank you all!

 

 

Absence_sheet_demo_1.fmp12

Absence_sheet_demo_2.fmp12

Edited January 25, 2019 by Dimitrios Fkiaras

[Ocean West]

Few options:

https://www.soliantconsulting.com/blog/pivot-table-filemaker

 https://dbservices.com/articles/filemaker-crosstab-reports/

 

 

[Dimitrios Fkiaras]

Thanks a lot! Though I feel more comfortable with native FileMaker solutions!

[comment]

Perhaps I am missing something, but it seems to me that my file does the same thing as your 2nd file - but with considerably less resources. Try it on for speed (and it would be best to perform these tests before adding niceties such as conditional formatting - so that you can identify the cause when it slows down).

CrossTabR.fmp12.zip

[Dimitrios Fkiaras]

Thank you very much! The calculation is very elegant! 

The problem is that when I change the gReportDate, the summary field refreshes only for the selected record!!

I tried moving the fields gReportDate and cDateR to the Personel table, only to confirm the same behaviour.

Then I filtered the relationship by adding gReportDateStart and gReportDateEnd fields, and the summary fields auto refresh. 

Why don't the repeating calculations evaluate with the change of gReportDate??

CrossTabR_Mod.fmp12

Edited January 27, 2019 by Dimitrios Fkiaras

[comment]

This is merely a screen refresh problem, and it would be best to address it as such: attach a script trigger to gReportDate, and have it do Refresh Window [Flush cached join result]. Otherwise Filemaker will be trying to minimize the CPU cycles (and network traffic, if the file is served).

BTW, while this is an interesting problem, I doubt there's a human that can learn anything useful from looking at so much data at one - even with 1/10th of the records .

[Dimitrios Fkiaras]

Thanks a lot for the explanation!

As for the second part, you are  partly correct.

The people responsible for approving leaves etc. want to have the big picture of absences for... lets say -3 / +15 days in order to be able to decide. There's no reason to navigate throughout the past... or the empty future. Just a little back and forth.

Unfortunately the Refresh Window script step takes soooo long to complete on Webdirect (FileMaker Pro handles it just fine, even on legacy PC's). 

All in all we just need to summarize around 150-200 records (100 people with approximately 1-2 leaves in 15 days). That is why I thought of filtering the relationship.

 

 

[comment]

Filtering the relationship by date range might help, as there will be less records to summarize. Unfortunately, I am unable to test this.

Ultimately, performance issues are solved by denormalizing the database, until the hardware can catch up.

Edited January 27, 2019 by comment

[Dimitrios Fkiaras]

Thanks!

 I don't know if I am being very dumb,  but this version seems to work pretty fast.

CrossTabR_Test.fmp12

PS. When I created the test data I didn't take care to avoid overlapping absences, so summarized data could be off sometimes

Edited January 27, 2019 by Dimitrios Fkiaras

[comment]

Well, that's very "creative", and if it works ... Though I wonder what would happen if you scrolled beyond the limit of the 2000.

--
P.S.  Why are you using a $variable in a Let() function? 

[Dimitrios Fkiaras]

Well, if I select a date that lies before or after the 2000 reps I get a "?" in the calculation.

I defined 2000 reps for the sake of the test. I am thinking to define maybe 90 reps. 

If the user selects a date before the initial date or after the final rep date, I am thinking to show a warning like:

"You have selected a date range that is not expected. It will take some time to calculate  --- OK, Cancel" and then set a different initial date so the 90 reps will cover the desired range

As for the $variable... no particular reason... my ignorance... I removed it.

I will test the solution on WebDirect and let you know of its performance.

Edited January 28, 2019 by Dimitrios Fkiaras

[Dimitrios Fkiaras]

Well, the solution seems to work... 

What amazes me is how fast FileMaker makes all these calculations. I'd expect that 2000 calc reps x 75,000 records will take a long time. Even in Webdirect there is a delay of just 1 sec (LAN).

Maybe my knowledge is a bit limited on this matter also.

*New: Even with 20000 reps X 75,000 records there is no delay. Why??

            After maybe the 2000th rep all other calc repetitions result to "empty". So we can assume that FileMaker engine can summarize empty repetitions instantly?

Edited January 29, 2019 by Dimitrios Fkiaras
New info

[LaRetta]

Comment, your CrossTabR.fmp12.zip file is extremely lightweight and easy to implement!  Thank you for providing this - it absolutely ROCKS! 😀