1 2 3 Previous Next 31 Replies Latest reply on Jul 14, 2009 9:02 AM by acurtis_1

    Working with dates in FMP Pro 9.x

    acurtis_1

      Title

      Working with dates in FMP Pro 9.x

      Post

      MS - Access Programmer/Developer. FMP confusing in using dates.  Trying to create reports that allows the user to enter a date range then use the date range to query against multiple date fields to determine which records are within the range. For example: Site visit by client happens at 2, 4, 6, 8, 10 and 12 month intervals. In order to have recruiter followup on rolling reviews for a client we want to enter a date range in to variables Start & End Dates then use those values to filter only those records that fall within the range for each of the month interval time for each client then sort by location and subdivison. Have tried working with layouts and scripts by creating a layout to enter the dates and a button to call the script to run the report but to no avail the criteria keeps coming up as no records fall within the range when there are at least 2 or more that fall within the range for each visit interval. I need an expert on FMP to help me answer this with a code example. No time to learn this product just need to create these reports and move on to next project. Please anyone have a heart and help me.

       

      Thanks,

      Annette

        • 1. Re: Working with dates in FMP Pro 9.x
          mrvodka
            

          You can use two global date fields to enter in your date range and then script it to search all the different date fields.

          http://fm.lithium.com/fm/board/message?board.id=FM-en-4&message.id=13138

           

          However, from what you have stated, it seems as though the multuple date fields should be seperate records in a related table.

          • 2. Re: Working with dates in FMP Pro 9.x
            acurtis_1
               Already have the global fields identified. Several examples defined in the knowledge base do not function as shown. For example setting a field back to a null value/empty value. Or performing the find and getting no records meet criteria. I will continue to wait until someone who has expert development knowledge of the product is able to provide me a solution. Thank you anyway.
            • 3. Re: Working with dates in FMP Pro 9.x
              mrvodka
                

              Okay...

               

              Have fun.

              • 4. Re: Working with dates in FMP Pro 9.x
                acurtis_1
                  

                Did not mean to offend but if you are an expert with 9 years of experience the take a stab at the following:

                 

                Script to set fields to null/empty does not process according to knowledge base sample. 

                 Go to layout (Search Date Range)

                 Set Field (Start Date[""])

                 Set Field (End Date[""])

                 Enter Browse Mode()

                 

                Then the following script from the knowledge base should have worked but it does not retrieve any records and there are records in the range provided.

                 

                If (IsEmpty(Start Date)

                   Show Custom Dialog["Start Date"; "Start Date must have a value. Please re-enter"]

                Exit Script()

                End If

                If (IsEmpty(End Date)

                   Show Custom Dialog["End Date"; "End Date must have a value. Please re-enter"]

                Exit Script()

                End If

                If (End Date < Start Date)

                   Show Custom Dialog["End Date"; "End Date must be after Start Date. Please re-enter"]

                Exit Script()

                End If

                Go to Layout ("Layout Name")

                Enter Find Mode)_

                Insert Calculated Result[Select; MyDateField; Start Date & "..." & End Date]

                Perform Find[]

                 

                This Script brings back no results when I type dates in the first scripted layout that I know there are records that meet the criteria.

                 

                So tell me why FMP does not behave as expected.

                 

                • 5. Re: Working with dates in FMP Pro 9.x
                  comment_1
                    

                  acurtis wrote:

                   

                   Go to layout (Search Date Range)

                   Set Field (Start Date[""])

                   Set Field (End Date[""])

                   Enter Browse Mode()



                   

                   It's hard to tell what's wrong with this, because that's not a Filemaker script. An actual script printout would (or at least should) look like this:

                   

                   Set Field [ TO::Start Date ; "" ]

                   Set Field [ TO::End Date ; "" ]

                   

                  (if these are global fields, you don't need to go to a layout in order to set them)


                  • 6. Re: Working with dates in FMP Pro 9.x
                    LaRetta_1
                       And you REALLY should listen to Mr._Vodka, who IS a top Developer.  Having multiple date fields within one table shows lack of relational experience, no matter WHAT your background indicates.
                    • 7. Re: Working with dates in FMP Pro 9.x
                      etripoli
                        

                      I see the main problem as the proper calculation of the intervals.  If you have a record, with an original visit date, and you want your users to be able to figure out if that record is due for another visit, whether it happens to be the 2nd month, 4th month, etc, is tough.  I have setup something like this, but it's difficult.  And since your background is Access, and you need a solution quickly, I'm not sure you'll be able to accomplish what you want without frustration.  With that said, you'll need to start by setting a calculation to determine the next scheduled visit date:

                       

                      cd_next_visit = date ( ceiling ( ( get ( currentdate ) - original_visit_date ) / 60 ) *2 + month ( original_visit_date ), day ( original_visit_date ), year ( original_visit_date ) )

                       

                      I'm taking a shortcut with the year calculation, but it should work.

                      • 8. Re: Working with dates in FMP Pro 9.x
                        comment_1
                          

                        What exactly does this part do:

                         


                        etripoli wrote:

                        ceiling ( ( get ( currentdate ) - original_visit_date ) / 60 ) *2 + month ( original_visit_date )


                         

                         


                        • 9. Re: Working with dates in FMP Pro 9.x
                          etripoli
                            

                          Figures out what the next interval is, and adds that to the original start month.  According to FM regarding the date function:

                           

                          "If you type a month greater than 12 or a day greater than the number of days in a month, FileMaker Pro adds the extra days or months to the result. The date function also allows zero and negative numbers as parameters."

                           

                          So it should properly calculate dates into the following year.

                           

                          Edit: and it does, if you use the update version in my previous post.

                          • 10. Re: Working with dates in FMP Pro 9.x
                            comment_1
                               Oh, I see now - you are trying to calculate the next bi-monthly interval, given a startDate.

                            Well, I don't see how it pertains to the original post, but in any case dividing by 60 is not a good idea, since some bi-monthly intervals are larger than 61 days.
                            • 11. Re: Working with dates in FMP Pro 9.x
                              etripoli
                                 I agree, most of my calculations of this sort go by weeks. As for pertinence, it seemed the OP was wanting the dates based on intervals calculated, instead of just searching for a range of entered dates.
                              • 12. Re: Working with dates in FMP Pro 9.x
                                acurtis_1
                                  

                                As for proper print out I cut and pasted from the knowledge base so that is how the information was displayed in knowledge base. As for telling me that I would get frustrated I do not believe that I have stated the question clearly. Simply put the database already calculates the 2, 4, 6, 8, 10 and 12 months target dates upon the initial visit. What is needed based on another value are those pending records that have not been assigned to a case regardless of 2, 4, 6, 8, 10, and 12 month dates that fall within a 14day defined date range by the user. What is being established is whether or not the client has been seen whether it was at 2, 4, 6, 8, 10 or 12. It will also determine workload and whether or not to hire more case workers.

                                 

                                So I repeat, what I said originally this is very easily created in MS-Access by creating a query that pulls all values through an SQL statement that uses input start and end dates created on a pop-up form then displays the results in a grouped report by location, sub-division and target dates. (i.e. all 2 month, then 4 months, etc.).

                                 

                                So as I stated if FMP has a way to do that great; (keep in mind I did not develop the database everything is in one table) also the FMP is shared across a network and between more than 2 locations making changes to the data model would be next to impossible. My goal is to help create a report that will meet there needs. If FMP can do this please provide an answer that allows me to review it either through free online resources or a reference book I may have access to. Already a member of books 24/7 and safari books online.

                                 

                                Thank you.

                                • 13. Re: Working with dates in FMP Pro 9.x
                                  comment_1
                                    

                                  acurtis wrote:

                                  As for proper print out I cut and pasted from the knowledge base so that is how the information was displayed in knowledge base.


                                  I don't see how that's relevant. You asked why your script doesn't work. I can't tell you that without seeing your script.


                                  • 14. Re: Working with dates in FMP Pro 9.x
                                    acurtis_1
                                      

                                    It's relevant because I used the exact same code. As for showing you the script unless FMP has an export feature or this forum board has a way to attach documents or image files not as url's or pasted views then I can't show you the exact script from FMP that I was able to create jpeg's from. The only way to show you exactly what is in the script is to type specifically each line from the script including the :: [] or the () where applicable. Suffice it to say I can point you to the knowledge base link that I used to create the scripts. Try the link below the you will see what I was attempting to accomplish

                                     

                                    http://filemaker.custhelp.com/cgi-bin/filemaker.cfg/php/enduser/std_adp.php?p_faqid=5912&p_created=1142370591&p_sid=qCbo1PCj&p_accessibility=0&p_redirect=&p_lva=&p_sp=cF9zcmNoPTEmcF9zb3J0X2J5PSZwX2dyaWRzb3J0PSZwX3Jvd19jbnQ9NzEsNzEmcF9wcm9kcz04NjQsODY1JnBfY2F0cz0mcF9wdj0yLjg2NSZwX2N2PSZwX3BzX2Fuc191cGRhdGVkPSZwX3BhZ2U9MSZwX3NlYXJjaF90ZXh0PWRhdGVz&p_li=&p_topview=1<!-- [if !mso]> <style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif] --><!-- [if gte mso 9]><xml>   Normal   0       false   false   false                      MicrosoftInternetExplorer4 </xml><![endif] --><!-- [if gte mso 9]><xml> </xml><![endif] --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] --><!-- [if !mso]> <style> v\:* {behavior:url(#default#VML);} o\:* {behavior:url(#default#VML);} w\:* {behavior:url(#default#VML);} .shape {behavior:url(#default#VML);} </style> <![endif] --><!-- [if gte mso 9]><xml>   Normal   0       false   false   false                      MicrosoftInternetExplorer4 </xml><![endif] --><!-- [if gte mso 9]><xml> </xml><![endif] --><!-- [if gte mso 10]> <style> /* Style Definitions */ table.MsoNormalTable      {mso-style-name:"Table Normal";      mso-tstyle-rowband-size:0;      mso-tstyle-colband-size:0;      mso-style-noshow:yes;      mso-style-parent:"";      mso-padding-alt:0in 5.4pt 0in 5.4pt;      mso-para-margin:0in;      mso-para-margin-bottom:.0001pt;      mso-pagination:widow-orphan;      font-size:10.0pt;      font-family:"Times New Roman";      mso-ansi-language:#0400;      mso-fareast-language:#0400;      mso-bidi-language:#0400;} </style> <![endif] --><!-- [if gte vml 1]>                         <![endif] -->

                                    1 2 3 Previous Next