11 Replies Latest reply on Oct 6, 2016 3:48 PM by dtcgnet

    Search/Find logic question


      I need to be able to test for an interesting condition.

      We are collecting data from a phone app.

      People are required to upload a minimum of one record for two consecutive days out of the last 7 days.

      A nightly script will do the checking and send out test messages to the deficient people.

      Here is the minimum acceptable uploads in a 7 day period.


      x = uload
      day 0day -1day -2day -3day -4day -5day -6


      Anything more than this is good.

      Anything less and they should receive a message.

      I have a person id and an upload timestamp in the table

      How can I script a find to detect the people that need to have messages?

      Adding fields to the table is acceptable as are any new TO's or relationships.

        • 1. Re: Search/Find logic question

          Can a person have 2 or more upload in a day?

          • 2. Re: Search/Find logic question

            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?

            • 3. Re: Search/Find logic question

              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 ?

              • 4. Re: Search/Find logic question

                Table definition is written as

                I have a person id and an upload timestamp in the table

                So I think he mean


                personid     timestamp

                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)

                • 5. Re: Search/Find logic question

                  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:



                  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.

                  • 6. Re: Search/Find logic question



                    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

                    • 7. Re: Search/Find logic question

                      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!


                      - RG>

                      1 of 1 people found this helpful
                      • 8. Re: Search/Find logic question

                        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:

                        Let (


                          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" )

                          ] ;



                        Case (

                          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" ]

                        Perform Find

                        If ( Get ( LastError ) = 0 ) // Some Dings were found

                        Do your notifications

                        End If

                        Exit Script


                        Long explanation, I know. File attached. Admin, no password to access it.

                        • 9. Re: Search/Find logic question

                          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.

                          • 10. Re: Search/Find logic question

                            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.


                            HTH, Thomas.

                            • 11. Re: Search/Find logic question

                              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.

                              1 of 1 people found this helpful