Can a person have 2 or more upload in a day?
Is each column a field? Each row a record in your table?
If on week 2 (row 2), the user uploaded on days 4 and 5, would this still meet the standard?
If you count records, you must have : for each day (min 2), for a couple of day (min 4) and for a seven days period ( min12).
You must :
select a 7 days period (daybegin..dayend where dayend = daybegin + 7 ) and count (get(numberselectedrecords)) 12 records min.
select a 2 days period (daybegin..dayend where dayend = daibegin + 1) and count (get(numberselectedrecords)) 3 records min
select a 1 day period (daybegin) and count (get(numberselectedrecords)) 2 records min
Is that correct ?
Table definition is written as
I have a person id and an upload timestamp in the table
So I think he mean
1 2016-10-06 01:23:45
1 2016-10-05 01:23:45
should be ommitted. (person1 uploaded on day-0 and day-1)
2 2016-10-06 01:23:45
2 2016-10-04 01:23:45
should be found. (day-0 and day-2)
Philmodjunk et al
the table was just an attempt to show the absolute minimum requirements for a person to NOT get dinged.
The table does NOT reflect the table where the data is stored
The table is simple:
pkID, fkPersonID, uploadTimestamp, textField
with this test data:
pk fkPersonID ts 1 1 1/1/2016 2 1 1/3/2016 3 1 1/5/2016 4 1 1/6/2016
If i run the report on 1-5-2016 or after (but not on) 1-11 person 1 would get dinged.
If i run the report on 1-6-16 thru 1-11-2016 person 1 would NOT get dinged.
That is why the table/matrix says minimum.
Dont care if they upload 50 times on day -3 if they didn-t upload on day -4 or day -5 then they get dinged
1 of 1 people found this helpful
This may not be the most efficient method and I might not have it 100% right, but I'd use a script along these lines because it's fairly straightforward:
- Perform Find [ all records from the last seven days ]
- Sort Records [ by personID, then by date, ascending ]
- go to first record
- Set a variable for $CurrentPersonID
- Set a variable for $CurrentPersonDates
- Go to next record
- Begin a loop
- If the personID being browsed is the same person as the $CurrentPersonID and either PatternCount ( $CurrentPersonDates ; current record's date - 1 ) or $GotoNextRecord is true
- Set Variable $GoToNextRecord to True [since this person does not need to be added to the deficient people list]
- Else if personID = $CurrentPersonID
- Set variable $CurrentPersonDates ( $CurrentPersonDates & If PatternCount ( $CurrentPersonDates ; "" ; "," & current record's date ) [i.e. append the current date to the list if it is not already there]
- Else [i.e. if personID != $CurrentPersonID ]
- Set variable $DeficientPeopleList ( $DeficientPeopleList & If ( $GoToNextRecord ; "" ; "," & $CurrentPersonID ) [i.e. add person to the deficient people list if $GoToNextRecord was never set to true for the current person]
- Reset variable for $CurrentPersonID ( CurrentPersonID )
- Reset variable for $CurrentPersonDates ( current record's date )
- Reset variable for $GoToNextRecord ( false )
- End If
- Go to next record ( Exit after last )
- End Loop
...and then do a second loop to unpack the $DeficientPeopleList in a series of find requests, or show them in a dialog or whatever.
I use this method a lot in my scripts, so if there is a more efficient way of doing this, I think I'd like to know too!
Create a new field of type date in the UploadedData table, which is the upload date extracted from the time stamp field. Lots of ways to do that, but you could use tsUploadDay = LeftWords ( ts ; 1 ). You could do it as an auto-enter of Get ( CurrentDate ) if you preferred. Depends on if you've already got all of the timestamp data or not.
Create a new field of type summary in the UploadedData table. Make the summary a List of tsDay. This will provide a carriage return delimited list of the days on which SOMETHING was uploaded. If something was uploaded more than once on a given day, it wouldn't matter...the day would show up in the list only once.
Since your script will run nightly, you'll know which days you're looking for. You're looking for Get ( CurrentDate ), and the six days before that. In your Personnel table create a new field of type Date with global storage: CheckDateDay1. Create a new field of type Date with global storage: CheckDateDay7. Your nightly script will populate those two dates. Once those dates are populated, each person will have a list showing which of the seven dates they've uploaded data (once you set up the relationship below).
Create a new Table Occurrence based on the UploadedData table, call it UploadedLast7Days. Relate it to the Personnel table with:
Personnel::PK_PersonnelID = UploadedLast7Days::fk_PersonnelID
Personnel::CheckPeriodStartDate <= UploadedLast7Days::tsUploadDay
Personnel::CheckPeriodEndDate >= UploadedLast7Days::tsUploadDay
In the Personnel table, set up an unstored calculation field called ConsecutiveDayCheck. Formula as follows:
UploadDay1 = Personnel::CheckDateDay1 ;
UploadDay2 = Personnel::CheckDateDay1 + 1;
UploadDay3 = Personnel::CheckDateDay1 + 2 ;
UploadDay4 = Personnel::CheckDateDay1 + 3 ;
UploadDay5 = Personnel::CheckDateDay1 + 4 ;
UploadDay6 = Personnel::CheckDateDay1 + 5 ;
UploadDay7 = Personnel::CheckDateDay1 + 6 ;
UploadedOnDay1 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay1 ) ; "Y" ; "" ) ;
UploadedOnDay2 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay2 ) ; "Y" ; "" ) ;
UploadedOnDay3 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay3 ) ; "Y" ; "" ) ;
UploadedOnDay4 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay4 ) ; "Y" ; "" ) ;
UploadedOnDay5 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay5 ) ; "Y" ; "" ) ;
UploadedOnDay6 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay6 ) ; "Y" ; "" ) ;
UploadedOnDay7 = If ( PatternCount ( UploadData_Past7Days::ListOfDays_smry ; UploadDay7 ) ; "Y" ; "" ) ;
BuiltListOfUploadDays = UploadedOnDay1 & ¶ & UploadedOnDay2 & ¶ & UploadedOnDay3 & ¶ & UploadedOnDay4 & ¶ & UploadedOnDay5 & ¶ & UploadedOnDay6 & ¶ & UploadedOnDay7 ;
ConsecutiveDays = PatternCount ( BuiltListOfUploadDays ; "Y" & ¶ & "Y" )
ConsecutiveDays ≥ 1 ; "No Ding" ;
"Ding This Person"
If a person has no instances in the 7-day period where they uploaded on consecutive days, then you want to ding them.
Your script would do something like:
Set Error Capture [ On ]
Go to Layout ( "Personnel" )
Set Field [ Personnel::CheckDateDay1 ; Value: Get ( CurrentDate ) - 6
Set Field [ Personnel::CheckDateDay7 ; Value: Get ( CurrentDate )
Enter Find Mode
Set Field [ ConsecutiveDayCheck = "Ding This Person" ]
If ( Get ( LastError ) = 0 ) // Some Dings were found
Do your notifications
Long explanation, I know. File attached. Admin, no password to access it.
Create a calculated field for your each of your dates:
- Calculated Date field zero is get(currentDate)
- Calculated Date field 6 is get(currentDate) + 6
Create 7 relationships each a multiple field join based on phone id and one of these calc date fields and the call date. Relationship 0 uses date field 0...relationship 6 uses date field 6 and all use the same phone id fields.
Create a calulated number field that totals the calls for that each dated relationship
- Total Date Field 0 = if ( count( relationship 0 record id) > 0 ; 0 ; 1)
- Total Date Field 6 = if ( count( relationship 6 record id) > 0 ; 0 ; 1)
The result is that if a date has no calls its flag is a 1 which shows up nicely in a checkbox.
Add a result calculated field that will tell you if one of the dates did not have a call:
Calc Result field = Count 1 + count 2 ...+ count 5 > 0
Now search for calc result field = 1 which means that a field has no value.
The nice part of this is that it is real time and no script, etc. has to be run.
Somehow I can't help but think that what you need to do is first find all records that meet the criteria.
After all it's sometimes easier to find existing records that non existing ones and deduct from there.
Find uploads on date range, sort by user id and loop over every user group comparing each consecutive record to previous one on the date.
If there's a match, grab the user id.
You could, at the end store the correct user id's to a global field when there's a match and use GTRR into user table, then use Show Ommited Only to get the users that need to be dinged.
If there's no related data into Users then Go To Layout, Show All Records, ding everyone.
I don't think you'll need extra fields, GetAsDate ( timestamp ) will suffice.
I tried some stuff with ExecuteSQL but due to it's limitations there's no way to compare consecutive rows of data.
If the script runs overnight, it should do alright performance wise.
1 of 1 people found this helpful
One thing to keep in mind is that the goal, expressed as a 7-item list familiar to FileMaker users, is to see if there are two "Uploaded" days in a row.
Day 1, did this guy upload? Y
Day 2, did this guy upload?
Day 3, did this guy upload? Y
Day 4, did this guy upload?
Day 5, did this guy upload? Y
Day 6, did this guy upload?
Day 7, did this guy upload? Y
In the list above, the person would be dinged because there isn't even one time where there are "Y"s on consecutive days. Each day, each of seven days needs a boolean answer of "Yes, he uploaded today" or "".
Shown as a return-delimited list of seven days in a row (the start or end day can change every day. No matter what, you're checking a seven-day period):
In that list, the person would not be dinged, because it's easy to see there were uploads on consecutive days at least once.
The example I sent looks at those days, and if it finds any times where there is at least one occcurrence of Y¶Y, a string of consecutive upload days has happened, and the person won't be flagged as needing to be dinged.
The use of globals in the relationship also allow for use in a multi-user environment, and the relationship automatically causes the necessary calculations to be calculated in a way that allows a simple find on "Who needs to be dinged?".
Doing this as a PSOS would be lightning fast, I think. I'm sure people can enhance the formulas involved, too.