1 2 3 Previous Next 30 Replies Latest reply on Jan 3, 2017 6:06 AM by thehalpeen

    How to create entries via a looping script for selected days

    thehalpeen

      I have created a simple looping script to create shifts for an employee. The script starts by pulling data from 4 global fields – Week start, Employee ID, Shift ID and Number of days this shift is to repeat itself - see screenshot below:

       

      I use the following tables:

      • People
      • Shifts
      • People_Shifts (Join Table)
      • Menu (contains the global fields)

       

      Simple loop.PNG

       

      What I'd like to do

      I've looked at other non-Filemaker or commercial solutions and I see that these program create records based on selected days - see below:

      Selected days.PNG

      I'm trying to figure out how to do this in Filemaker.

       

      I can do or know the following:

      • Create a value list of days - Mon - Sun
      • In know the 'start date' or first date of the weekly shift which is always a Monday -  say in this instance, Monday 19th December, 2016
      • I can create a field called 'Shift Days'  - this is a checkbox field based on the Value List above. This field will contain a comma separated list/array of the days selected - in the above case Mon, Tue and Thu.
      • I can count the number of days in the field using ValueCount calculation.
      • I can use GetValue to pull out a value in the field

       

      So in summary I have:

      The number of records to create via ValueCount

      The shift DaysI need to create records (Mon, Tue, Thu) via the checked boxes

      The first day of the weekly shift

       

      My Question (I've got to it at last)

      How do I create the three records with the correct dates for Mon, Tue and Thur.

        • 1. Re: How to create entries via a looping script for selected days
          David Moyer

          Hi,

          given all you've stated, it seems that you already know this.  But just in case ...

           

          Set Variable $index = 1
          Set Variable $limit = ValueCount($list)

          Loop

            $value = GetValue($list; $index)

            ... do stuff here

            Exit Loop If $index = $limit

            Set Variable $index = $index + 1

          End Loop

          1 of 1 people found this helpful
          • 2. Re: How to create entries via a looping script for selected days
            erolst

            thehalpeen wrote:

             

            My Question (I've got to it at last)

            How do I create the three records with the correct dates for Mon, Tue and Thur.

             

            Here's the basic idea:

             

            Set Variable [ $selectedWeekDays ; yourWeekDaySelectionField ]

            Set Variable [ $selectionCount ; ValueCount ( $selectedWeekDays ) ]

            Set Variable [ $weekStart ; Menu::gWeekStart ]

            # [ collect the other data ]

            # [ switch layouts ]

            Loop

              Exit Loop if [ Let ( $i = $i +1 ; $i > $selectionCount ]

              Set Variable [ $currentWeekDay ; GetValue ( $selectedWeekDays ; $i ) ]

              Set Variable [ $offset ; Let ( weekDays = "Mon¶Tue¶Wed¶Thu¶Fri¶Sat¶Sun" ; ValueCount ( Left ( weekDays ; Position ( weekDays ; $currentWeekDay ; 1 ; 1 ) ) ) ) ]

              New Record

              Set Field [ Table::shiftDate ; $weekStart + $offset - 1 )

              # [ write the other data ]

            End Loop

            1 of 1 people found this helpful
            • 3. Re: How to create entries via a looping script for selected days
              David Moyer

              ah, I neglected to address the issue of the date as Erolst did.  If this is something you have to do often, here's a custom function that will return the row number of a single value in a value list.

              Custom Function:  ValueListIndex

              ValueListIndex(

                ValueListItems ( Get ( FileName ) ; "DaysOfWeek" );

                "Wed"

              ) would yield 3

              1 of 1 people found this helpful
              • 4. Re: How to create entries via a looping script for selected days
                thehalpeen

                David,

                 

                Do you mind writing out your script again, with the date - I just want to see how it looks?

                • 5. Re: How to create entries via a looping script for selected days
                  David Moyer

                  If you have the ValueListIndex custom function, then can be used like so ...

                  Set Variable $limit = ValueCount ( $selectionList )
                  Set Variable $dayList = ValueListItems ( Get(Filename); "MyValueListOfDays" )

                  Set Variable $index = 1


                  Loop
                    $value = GetValue($selectionList; $index)
                    $offset = ValueListIndex ( $dayList; $value ) - 1
                    $dateOfSelectionRow = $theMondayBaseDate + $offset

                   

                    ... do stuff here

                   

                    Exit Loop If $index = $limit
                    Set Variable $index = $index + 1
                  End Loop

                   

                  (you supply $selectionList, $theMondayBaseDate and the value list name for $dayList)

                  • 6. Re: How to create entries via a looping script for selected days
                    thehalpeen

                    erolst,

                     

                    I've tried to test your script, but I'm stumbling at the first hurdle. Below is the script I've written - copied to be precise. I've insert two screenshot - see below

                     

                    Screenshot 1 - the full line 8 is in screenshot 2 below

                    erolst code 2.PNG

                    Screenshot 2 - showing line 8 of script in full

                    line 8.PNG

                    When I run the script it exits when it gets to line 6 'Exit Loop if' - so it doesn't get a chance to run.

                    I disables line 6 to see what would happen if it ran. It populated the records, but all the dates were Sunday 18th December  - even thought my start date was Monday 19th December.

                     

                    Can you spot where I have gone wrong from my code?

                    • 7. Re: How to create entries via a looping script for selected days
                      erolst

                      Seems like your selection was empty.

                       

                      (You need to build in a check on that. And you also must find a mechanism to prevent the script from creating duplicates ...)

                       

                      In that case, already in the first iteration the loop counter is > 0 (the selection count).

                       

                      When you dusabled line 6, there was no offset to calculate, and all records were set to mondayDate - 1.

                      • 8. Re: How to create entries via a looping script for selected days
                        philmodjunk

                        What you describe is consistent with the $SelectionWeekDays variable being empty.

                         

                        Looks like you either have a problem with your data or your context rather than your code.

                         

                        data: confirm what values are actually in the daysSelected field.

                        context: Are you running this script on a layout based on the People_Shifts table occurrence? (this is a box on your relationships graph and should be specified in "show records from" in Layout Setup... for your layout in most cases--though there may be other contexts that also work if the specified layout table occurrence and current record is correctly related to the correct record in People_Shifts.

                        • 9. Re: How to create entries via a looping script for selected days
                          David Moyer

                          my script didn't provide error-checking either ...

                           

                          ...

                          If $limit > 0

                            Loop

                              ...

                            End Loop

                          Else

                            // throw an error

                          End If

                          • 10. Re: How to create entries via a looping script for selected days
                            erolst

                            David Moyer wrote:

                             

                            my script didn't provide error-checking either ...

                             

                            End Loop

                            Else

                            // throw an error

                            End If

                            I'd do this at the very beginning ...

                             

                            Set Variable [ $selection ; someField ]

                            Set Variable [ $selectionCount ; ValueCount ( $selection ) ]

                            If [ not $selectionCount ]

                              Error Dialog

                              Exit

                            End if

                            Proceed

                             

                            If you get past that check, you have at least 1 value, and the loop control will take care of itself.

                            • 11. Re: How to create entries via a looping script for selected days
                              thehalpeen

                              Ok, some really great advice and examples here.

                               

                              Firstly, erolst and philmodJunk, you were correct, my selection was empty, because I had selected the wrong field in my script - apologies.

                               

                              I ran the script -  without error checking at the moment -  and it worked beautifully - thanks for help.

                               

                              I have to insert the error checking and then run it again, just to make it complete.

                               

                              After that, I'm going to attempt David's script with the custom function.

                              • 12. Re: How to create entries via a looping script for selected days
                                beverly

                                Check this article:

                                 

                                beverly

                                 

                                Sent from miPhone

                                1 of 1 people found this helpful
                                • 13. Re: How to create entries via a looping script for selected days
                                  thehalpeen

                                  erolst,

                                   

                                  You advised the following and I agree - "(You need to build in a check on that. And you also must find a mechanism to prevent the script from creating duplicates ...)"

                                   

                                   

                                  I've attempted this. I created a field called 'check _shift' in the Join table (People-Shifts) which concatenates the StaffID and the ShiftID and the Shift_Date to give a unique calculation that looks like this -  020112-12-2016.

                                   

                                   

                                  I have the menu table, with the globals fields that captures the shift details before creating it in the join table. I have tried to create a similar calculation in this table that will compare with the above 'check_shift' data in the join table.

                                   

                                   

                                  I have the gStaffID, the gShiftID, but I don't have the exact date of the shift at this stage. I have the date the shift starts and the selected days, but nothing that is giving me an exact date format for the selected days.  I think I need to create a field called 'gCheck-Shift' that can create a list that will convert the selected days into dates, but I'm not certain how to pull the days from the selected days list and convert to actual dates in this global table.

                                   

                                   

                                  Any idea how to do this or have I taken the wrong approach completely  in trying to identify if the shift exists in the join table?

                                  • 14. Re: How to create entries via a looping script for selected days
                                    erolst

                                    You could do this by adding another loop to your script where you check the dates to be created against the already existing ones, like:

                                     

                                    Set Variable [ $selectedWeekDays; Value:CreateSelectedWeekdays_eos::gWeekDaySelection ]

                                    If [ IsEmpty ( $selectedWeekDays ) ]

                                      Show Custom Dialog [ Title: "no selection"]

                                      Exit Script [ ]

                                    End If

                                     

                                    Set Variable [ $existingDates; Value:CreateSelectedWeekdays_eos::sListOfDates ]

                                    Set Variable [ $selectionCount; Value:ValueCount ( $selectedWeekDays ) ]

                                    Set Variable [ $thisMondayDate; Value:Let ( cd = Get ( CurrentDate ) ; cd - Mod ( cd ; 7 ) + 1 ) ]

                                    Set Variable [ $weekDayList; Value:ValueListItems ( "" ; "WeekDays_short" ) ]

                                     

                                    # [ calculate dates, check against existing and add to a list if not yet ]

                                    Loop

                                      Exit Loop If [ Let ( $i = $i + 1 ; $i > $selectionCount ) ]

                                      Set Variable [ $currentDay; Value:GetValue ( $selectedWeekDays ; $i ) ]

                                      Set Variable [ $offset; Value:IndexPositionInList ( $currentDay ; $weekDayList ) ]

                                      Set Variable [ $theDate; Value:$thisMondayDate + $offset - 1 ]

                                      Set Variable [ $listOfDatesToCreate; Value:

                                        List (

                                          $listOfDatesToCreate ;

                                          Case ( IsEmpty ( FilterValues ( $theDate ; $existingDates ) ) ; $theDate )

                                          )

                                       ]

                                    End Loop

                                     

                                    If [ IsEmpty ( $listOfDatesToCreate ) ]

                                      Show Custom Dialog [ Title: "all in" ]

                                      Exit Script [ ]

                                    End If

                                     

                                    Set Variable [ $creationCount; Value:ValueCount ( $listOfDatesToCreate ) ]

                                    Set Variable [ $i; Value:0 ]

                                    Loop

                                      Exit Loop If [ Let ( $i = $i + 1 ; $i > $creationCount ) ]

                                      New Record/Request

                                      Set Field [ GetValue ( $listOfDatesToCreate ; $i ) ]

                                    End Loop

                                     

                                    Note that this is from a simple single-table setup, so I used a summary field to get a list of existing dates; in your file, you'd use List( ) via the/a relationship by staffID.

                                     

                                    Instead of the list check, you could use a relationship by staffID and a global date field, set each calculated date into that field and check if there is a record; if so, the record exists; if not, add it to your to-create-list.

                                    1 2 3 Previous Next