12 Replies Latest reply on Jul 16, 2014 1:36 PM by philmodjunk

    Script for date range

    bruns

      Title

      Script for date range

      Post

           Hello, I currently have a date range script that works for finding info between two dates.  However I have added  a twist that I cannot figure out. Here is the sceniro, please let me know if I can provide more info.

            

           I have a table for vessel assignments and I have these fields:

           date_on_vessel

           date_off_vessel

           date of next crew change

           days on vessel (calc) (If (IsEmpty (date_off_vessel) ; Get (CurrentDate)  ; date_off_vessel) - date_on_vessel)

           g_datePickerStart

           g_datePickerEnd

           Currently if I use this I can find how long someone has been on the vessel realitive to date on and date off, however I have not been considering date of next crew change.

            

           I am trying to put together a Sea Service Report which includes all of the days on a vessel between to given dates, such as date on and date off but the new addition would also include date on until the current date ie the date the report ran.

            

           If I can provide more info please let me know.  I am including a script of what I currently have for showing crew changes between two dates.

            

      Scan_Jul_9%2C_2014%2C_2_56_PM-page1.jpg

        • 1. Re: Script for date range
          bruns

               Do I need to change my days_on_vessel to include date_next +crew_chg somehow?  If so how would I change it?

                

               Thanks

          • 2. Re: Script for date range
            bruns

                 Screen shot for the report I am looking at

            • 3. Re: Script for date range
              philmodjunk

                   I'm afraid that I don't see the problem that you see. Your days on vessel calculation already allows for the current date when the "date off" value is empty so where does a problem occur for you here?

                   Helpful Hint:

                   To post a script to the forum:

                     
              1.           You can upload a screen shot of your script by using the Upload an Image controls located just below Post a New Answer.
              2.      
              3.           You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF to your clipboard for pasting here. (with this approach, you can get multiple script steps on the same line, please edit the pasted text by inserting some returns to separate those steps.)
              4.      
              5.           If You have FileMaker Advanced, you can generate a database design report and copy the script as text from there.
              6.      
              7.           If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional and easier to read format.
              • 4. Re: Script for date range
                bruns

                     Sorry, you are correct, I did not see the forest for the trees.  Thanks

                • 5. Re: Script for date range
                  bruns

                       After looking at it again, I think my problem is in the script for Sea Service (attached).  I have also attached a screen shot of the date picker fields. 

                       As you can see in the script, the Set Field is going to the date off field.  So currently if I put in two dates, it places it in the date off field.  But the script does not take into accout the date of next crew change field to those days are not captured. 

                       So I am trying to find all vessels worked on between 2 dates and it works for date off but I am also trying to capture the vessel he is currently on if it is in Hopefully I explained it better ths time.

                  • 6. Re: Script for date range
                    bruns
                    /files/746c182762/Capture.PNG 1382x280
                    • 7. Re: Script for date range
                      philmodjunk

                           What you can do is add these lines just before Perform Find:

                             New Record/Request
                             Set Field [Vessel Assignments::Date Off Vessel ; "=" ]

                           This will find all records where Date Off Vessel is either inside the specified date range OR is empty.

                      • 8. Re: Script for date range
                        bruns

                             I am close Phil

                             I am posting the script of were I am currently at, the problem is now I am trying to just pull up the records of the person I am looking at, it keeps bringing up all records.  I have related record but maybe I have it in the wrong place or pulling from wrong table, I have employee table and vessel assignment table

                        • 9. Re: Script for date range
                          bruns
                          /files/241be2ec9b/Capture1.PNG 1559x398
                          • 10. Re: Script for date range
                            bruns

                                 Phil, I think I am looking at this wrong, If I want to find out what vessels someone has worked on between two dates, I would not use date off would I?  I am trying to find all the vessels that someone has worked on or is currently on between any 2 dates I choose in the date picker.  Is this possible?

                            • 11. Re: Script for date range
                              bruns

                                   I switched the date off to date on in the script and that part seems to give me that part but still cannot get the relate record to work, keep giving me all records.  Thanks

                              • 12. Re: Script for date range
                                philmodjunk

                                     Yes, there is nothing in your script that specifies that you find records for any one person as the only criteria that you are specifying is a date range. You'd need to add an additional criterion such as a crew person's ID.

                                     And I don't quite see what Go to Related records is intended to do here.

                                     And I've also been thinking more about that date range. The main problem here is that you actually have TWO date ranges. You have a date range in the record, the date on and date off dates (or the current date if still on the vessel) and you are specifying a date range in your search criteria. It would appear that you want all records where the two date ranges overlap by at least one day.

                                     Note the following date ranges:

                                     With a specified date range in your search of: 7/1/2014 to 7/25/2014

                                     7/11/2014 to 7/20/2014   On date is greater than date 1, off date is less than date 2
                                     6/25/2014 to 7/2/2014     Off date is greater than date 1 but also less than date 2
                                     7/20/2014 to 8/15/2014    On date is greater than date 1 but also less than date 2
                                     6/24/2014 to 8/1/2014     On date is less than date 1 and off date is greater than date 2

                                     all overlap the specified date range in your search.

                                     Have your script create multiple find requests each specifying the date range criteria differently:

                                     Enter Find Mode []
                                     Set Field [VesselAssignments::CrewMemberID ; VesselAssignments::gCrewmemberID ]
                                     Set Field [VesselAssignments::DateOn ; VesselAssignments::gDatePick1 & "..." & VesselAssignments::gDatePick2 ]
                                     New Record/Request
                                     Set Field [VesselAssignments::CrewMemberID ; VesselAssignments::gCrewmemberID ]
                                     Set Field [VesselAssignments::DateOff ; VesselAssignments::gDatePick1 & "..." & VesselAssignments::gDatePick2 ]
                                     New Record/Request
                                     Set Field [VesselAssignments::CrewMemberID ; VesselAssignments::gCrewmemberID ]
                                     Set Field [VesselAssignments::DateOn ; "<" & VesselAssignments::gDatePick1 ]
                                     Set Field [VesselAssignments::DateOff ; ">" & VesselAssignments::gDatePick2 ]
                                     New Record/Request
                                     Set Field [VesselAssignments::CrewMemberID ; VesselAssignments::gCrewmemberID ]
                                     Set Field [VesselAssignments::DateOn ; "<" & VesselAssignments::gDatePick2 ]
                                     Set Field [VesselAssignments::DateOff ; "=" ]
                                     Set Error Capture [on]
                                     Perform Find []

                                     Note: If you are starting from a Crew member record and then need this report for the current crew member record, your script can set a variable to your crew member ID before changing layouts and then the set field steps shown above can copy data from the variable instead of an additional global field.