I am trying to modify the calendar template, especially the circle that are there to show if there's any data on the calendar. The calendar works right of the box, but the have used CountRows and because I have a few entries on almost all dates, the speed of the app is reduced a lot.
I have been trying to solve it by an If-statement but I cannot get to work. Here is the code I am currently using, which works but the performance is horrible.
But to do this, you have to run a count on each row of the calendar. I have a few suggestions here--big ideas below:
Evaluate the date against the table so you do not need to calculate it in your filters.
Collect treatments for the dates shown in the calendar only. Then show counts against the collection instead of the underlying behandlingar table.
The calendar template uses a table of 42 integers, each representing a day on a calendar, 0-41. This results in 6 rows of 7 days each when shown in the gallery. A date is calculated in the items of the gallery:
In your CountIf, you are performing that calculation again and again for each row of the table. It's inefficient to do the calculation and filter at the same time. So let's complete that calculation ahead of time. We'll append it as a column to the underlying table itself, rather than figuring it out wherever it is needed:
This means, "Add a column called Date that is equal to each respective integer plus the first date in view." So the 0th date would be the first date in view. The table changes from one column (Value) to two columns: Value, Date. Now you can reference Date anywhere in the gallery.
Now that the Date can be referenced, there's a few things we can do. If you were to put your existing filter into the gallery, it would make a call to the connected datasource 42 times. That's a lot of calls. Let's do one call instead--get all of the treatments for the date range that is shown. You can place this action wherever you cause the calendar's date range to change.
This means, "Collect all of the treatments between the first and last date in the gallery named Gallery1. Put them into the colTreatments collection (name it whatever you want)."
Now inside the gallery, you can do the CountIf or CountRows against the collection instead of behandlingar.
One final suggestion: I notice that you are using DateValue() and Text() around Datum_behandling. That's causing the filter to be more complicated. Can you modify the table to make the column a date type? This way you do not need to do any conversions.