1 2 Previous Next 18 Replies Latest reply on Mar 2, 2012 9:19 AM by LaRetta

    Creation of date fields within two dates

    spinetta

      Hi, can anyone help me to find out how to automatically create one date field per day within two given dates (the first date is the beginning of a project, the last date is the last day of the project). I need to allocate one person and one place to each day.

      Thank you in advance

        • 1. Re: Creation of date fields within two dates
          Mike_Mitchell

          Hello, spinetta.

           

          Are you wanting to create a series of date fields, or a series of records with one date per record?

           

          Mike

          • 2. Re: Creation of date fields within two dates
            spinetta

            Hello Mike,

             

            Good point. I guess that what I need is to create automatically the records.

             

            Thanks!

            • 3. Re: Creation of date fields within two dates
              Mike_Mitchell

              That being the case, it's probably easiest to script it (especially if you have to do it on a repeating basis). There are a couple of ways to go about it; it depends on your table structure.

               

              Maybe the easiest thing to do would be something like this:

               

              Set Variable [ $start ; StartDate ]

              Set Variable [ $end ; EndDate ]

              Go to Layout [ { layout where you want to create the records } ]

              New Record / Request

              Set Field [ Date ; $start ]

              Loop

                Exit Loop If [ Date >= $end ]

                Duplicate Record / Request

                Set Field [ Date ; Date + 1 ]

              End Loop

               

              You'll probably need to do some additional stuff - like add the other fields you need and some cleanup - but that should get you started.

               

              HTH

               

              Mike

              1 of 1 people found this helpful
              • 4. Re: Creation of date fields within two dates

                You could shorten that script a bit as:

                 

                Go to Layout [ layout to begin loop ]

                Loop

                Exit Loop If [ gStart + $i  >  gEnd ]

                New Record/Request

                Set Field [ yourDate ; gStart + $i ]

                Set Variable [ $i ; $i + 1

                End Loop

                1 of 1 people found this helpful
                • 5. Re: Creation of date fields within two dates
                  Mike_Mitchell

                  Yep, that works too ... provided gStart and gEnd are global date fields. If you want to use variables, be sure to use GetAsDate to keep FileMaker from munging up the comparisons.

                   

                  Mike

                  • 6. Re: Creation of date fields within two dates

                    Mike_Mitchell wrote:

                     

                    If you want to use variables, be sure to use GetAsDate to keep FileMaker from munging up the comparisons.

                     

                    Mike

                     

                    Hi Mike,

                     

                    Yes, they were global fields.  It was not clear whether those dates were readily available to the table where the records are being created.  If the dates ARE available, whether already related and in fields in Projects or whether added by a User into globals to create the project records, then variables are unnecessary.

                     

                    But if variables are used in the comparitive portion then wrapping with GetAsDate() is important.  Thank you for bringing it up. 

                     

                    LaRetta

                    • 7. Re: Creation of date fields within two dates
                      jason.delooze

                      I wouldn't worry about shortening the script as much as I would worry about entering Browse Mode; checking that StartDate <= EndDate; checking that StartDate is not empty or invalid (or you get lots & lots of records); Freezing Window, etc.

                       

                      BTW - while GetAsDate will properly cast your $variables as date-types, it doesn't work so well when casting an invalid "date string".  It is always wise to test your date input for validity and emptiness via IsValid and IsEmpty.

                       

                      Jason

                      • 8. Re: Creation of date fields within two dates
                        spinetta

                        Hello LaRetta,

                         

                        The script suggested by you works just fine. Thank you!

                        There are two things I need to solve:

                         

                        -now every date created automatically appears in a different project. I need the script to automatically create all the dates for each project. How to do that?

                        -now the script only runs if I press the 'perform' buttom. How can I make the script run automatically?

                         

                        The Startdate and Enddate are allocated manually to each different project.

                         

                        Thank you in advance!

                         

                        María

                        • 9. Re: Creation of date fields within two dates

                          Hi Maria,

                           

                          -now every date created automatically appears in a different project. I need the script to automatically create all the dates for each project. How to do that?

                           

                          Then you are running the script in the Projects table?  If there are multiple dates for one project then the date records should be in a related table (one project with many dates).  You said, "I need to allocate one person and one place to each day" so it sounds like an Assignments table?  For ease of reference, I will refer to this dates table as Assignments - please provide the correct name for us.

                           

                          -now the script only runs if I press the 'perform' buttom. How can I make the script run automatically?

                           

                          Ideally, records are only created when needed - why create empty records?  If you still want to use this method, why not include creating the Assignments when you create the Project?   It would mean tightening your system and only allowing new records via your script.

                           

                          The Startdate and Enddate are allocated manually to each different project.

                           

                          Are these StartDate and EndDate standard date fields in the Project record or are they global fields?  If the Assignments dates always span the date range, and if you want to create all of them whether empty or not, then there is no need for these dates as standard fields in the Project itself (it would be redundant information).  So if the Min and Max of these assignment dates always equals your Project start and end then use global dates for User to specify range.  Then the relationship itself will provide the starting and ending dates for the Project and the Project's dates would change according to records added/deleted from Assignments.  If the dates table does not necessarily always match a Project’s start and end then you will need the date fields in Projects as well.

                           

                          Can you fill in the imformation gaps for us and explain the User process on creating a Project and these associated date records?  We can then provide a more-specific script to handle it.

                           

                          Message was edited by: LaRetta ... corrected quotes

                          • 10. Re: Creation of date fields within two dates
                            spinetta

                            Hi LaRetta,

                             

                            Yes, I'm running the script in the Projects table. There are multiple dates for one project (one project with many dates). We can call the dates table 'Assignment'. I even should be able to have the possibility of repeating a specific assignment date (not automatically). Regularly I will allocate one person to one date but it can be that I need to allocate more than one person to the same date.

                             

                            The idea would be to have each 'Assignment' date record automatically created when the Project record itself is created. I create a 'New record' and I manually indicate the DateStart and the DateEnd.

                             

                            It should not be possible (nor needed) to add a record dated before DateStart or after DateEnd.

                             

                            I have a Staff database (based on the Contact Management Started Solution) and I'm using FM Pro 11.

                             

                            Thank you, again, for the support.

                             

                            Maria

                            • 11. Re: Creation of date fields within two dates

                              Hi Maria,

                               

                              It really would be better to only create Assignments as you assign them but I'm going to proceed as you wish with the record creation concept.  You mentioned that you wanted it to happen without need of clicking a Perform Script button.  And we can give you that.  But really, you need to create the Project record and enter the StartDate and EndDates anyway, right?  What is problem with then clicking a 'Create Assignments' button?

                               

                              I created a sample approach which has the following:  A correct relational structure using proper table IDs, corresponding value lists so it functions, and a script which creates your Assignment records when clicked.  If need be, we can change that to a trigger or Custom Dialog[]-handled process after we try the file on for size.  I have included a Staff table as well and notice that you can assign Staff in the portal - just click on Staff Name.  It displays the staff name but inserts the StaffID (because it is a value list attached to a pop-up menu).

                               

                              I have turned on 'allow creation of related' to the Assignments portal (see the graph & relationship dialog, checked at the bottom) so if you wish to add an Assignment, just click into the last blank row.  The 'x' allows deletion of the Assignment.  It does not display in the 'add row' empty blank row because I have conditional formatting on it using custom font size.  Let us know how or if this file still needs tweaking to meet your needs. 

                               

                              LaRetta

                               

                              Message was edited by: LaRetta ... and my apology ... I wanted to mention that I always end table occurrence names with an _A to indicate that 'allow creation' is on.

                              • 12. Re: Creation of date fields within two dates

                                Hi Mike,

                                 

                                I've used process of duplicating a record before particularly if there are several fields to set which have same values and I know it can be faster when there are several steps involved.  But in this instance, I didn't expect much difference.  Regardless,  I decided to test it and I was surprised that your script was noticeably faster (enough that I must mention it).   I am attaching my test file (which is the Projects file for Maria but with test timestamps added and also includes your script for comparison).

                                 

                                In creating 4,000 records (based upon the date range in the Project record), your script is 1 second faster repeatedly.  In this instance, it would not really matter.  But I think it is important to point out different techniques when they are faster because every nanosecond counts and these issues add up (here and there) and if not careful, you have a system that runs like a dawg and sinks like a stone.

                                 

                                Thank you for making me take another look, Mike.    I'm now be running speed tests on Set Field[] and variables and everything else for the next few months, LOL.

                                • 13. Re: Creation of date fields within two dates
                                  spinetta

                                  Hi LaRetta,

                                   

                                  The sample file is great. This is exactly what I needed. It is just fine to click a 'Create Assignments' button. I just didn't realise that I could create the button in the layout.

                                   

                                  I will continue the database based on this sample. Thank you, LaRetta!

                                   

                                  Maria

                                  • 14. Re: Creation of date fields within two dates
                                    spinetta

                                    Hello LaRetta,

                                     

                                    Your sample file was very helpful. I included a TimeStart, TimeEnd and Nber hours (screenshot attached) based on the following calculation (seen on one of the Starter solutions):

                                     

                                    TimeEnd:

                                    If (  not IsEmpty ( TimeEnd ) and Hour ( TimeEnd ) ≤ Hour ( TimeStart ) ; TimeEnd + Time ( 12 ; 0 ; 0 ) ; TimeEnd )

                                     

                                    The calculation works fine except for the fact that every day of the project appears automatically with the TimeStart and TimeEnd when I write the TimeStart and Time End of the first day. How can I, instead, be able to include manually the TimeStart and TimeEnd for each day/each staff?

                                     

                                    Thank you!

                                     

                                    Screen Shot 2012-03-02 at 11.22.38 AM.png

                                    1 2 Previous Next