Hi All,
I am creating an app showing sites, the dates the sites were checked and the result of the check. I have 2 tables, one for the sites, checked date and check result and a table showing sites and the dates they were cleaned. below is an example of the tables. I need to create a counter in Table 1 showing the number of times the site has been cleaned, ie the count of CleanedDates for each site that is less than the CheckedDate. The tables obviously link via Site and I can do this counter in a straight table in the front end but am struggling to do this in the script. If I can add the field in the script it makes it much easier to design tables, charts etc in the front end. Please help and thanks for your time!!
Table1:
Site, CheckedDate, CheckResult
A, 01/01/21, Clean
B, 03/01/21, Clean
A, 08/01/21, Clean
B, 10/01/21, Dirty
A, 15/01/21, Dirty
B, 17/01/21, Dirty
A, 22/01/21, Clean
B, 24/01/21, Clean
A, 29/01/21, Clean
B, 31/01/21, Clean
A, 05/02/21, Clean
B, 07/02/21, Clean
A, 12/02/21, Clean
B, 14/02/21, Clean
A, 19/02/21, Dirty
B, 21/02/21, Clean
A, 26/02/21, Clean
B, 28/02/21, Clean
Table2:
Site, CleanedDate
A, 16/01/21
B, 18/01/21
A, 20/02/21
Intended Output Table:
Site, CheckedDate, CheckResult, CleanCounter
A, 01/01/21, Clean, 0
B, 03/01/21, Clean, 0
A, 08/01/21, Clean, 0
B, 10/01/21, Dirty, 0
A, 15/01/21, Dirty, 0
B, 17/01/21, Dirty, 0
A, 22/01/21, Clean, 1
B, 24/01/21, Clean, 1
A, 29/01/21, Clean, 1
B, 31/01/21, Clean, 1
A, 05/02/21, Clean, 1
B, 07/02/21, Clean, 1
A, 12/02/21, Clean, 1
B, 14/02/21, Clean, 1
A, 19/02/21, Dirty, 1
B, 21/02/21, Clean, 1
A, 26/02/21, Clean, 2
B, 28/02/21, Clean, 1
