I have a problem where the all the completed ToDo's are showing up on the day view, even though they have been completed earlier.
I have used my own ToDo table and it has Status of 'Open' and 'Closed' as opposed to your example where it uses 'done'.
I have changed the Database Calculation field code for field zscToDoSummaryCalc'
from

Code: Select all

....Case ( st ≠ "done" ; s & ....

to

Code: Select all

....Case (st ≠ "Closed" ; s & ....

and
from

Code: Select all

...."(done) " & s ....

to

Code: Select all

...."(closed) " & s ....

so the display correctly shows.

The SQL query builds with a '?' in the sql statement which I believe is the issue. I guess there is somewhere else I need to change reference to my new status name.
The Status of 'open' and closed' is not the same as the status I use for the calendar events entries, in case this is relevant.

Portion of SQL

Code: Select all

SELECT c."zscTimestampStartCalcNum", ''||c."id_ToDo", c."Date_Created", c."Date_Completed", c."TimeStart", c."Time_Due", ''|| c."Active_Staff_Name" , 3, c."zscTimestampEndCalcNum"
FROM "ToDoListNew" c
where c."zscTimestampStartCalcNum" < 63530092800 and ''||c."Status" Not LIKE ?
Union
.....

If you have any ideas that would be appreciated.

Hi Tony. The only change you should need to make is to change the value given to the $sc_ToDoDone variable in the "Capture Events by Date Range - Run SQL Query" script from "Done" to "Closed" (see screen shot). Let me know if that does the trick. -Jeff

Thank You Jeff for that solution and the quick reply. That was it, working fine now.