10 Replies Latest reply on Aug 16, 2015 1:25 PM by philmodjunk

    Date calculations in filemaker

    simonbucknall

      Title

      Date calculations in filemaker

      Post


       

      Hi,

       

      Apologies for the question but i am new to file maker and not familiar with databases in general so my question is probably fail simple/obvious. 

       

      I have a database with a table containing customers bicycles details. This is linked to a table with bike service details using a unique bike code. The service details table has the date the service took place and and the bicycle details table has the service interval for the bike. I am trying to write a script that will generate an excel spreadsheet that i can use with MailChimp to email out service reminders for customers whose bikes are due a service.

      The service interval in the bicycle details table is just an number e.g. 5 so was going to add 5*30 to the service date and see if it was less than the current date if so reminder is due. or set another 'service due' field with a 0 or 1. I thought the date would be simpler as the script could just perform a find on the table.

      My initial thought was to somehow get the last service date in to a field in the Bicycle details table and then do the calculation from there but i don't know how to do that as a bike has several services and only need the date for the last one. 

      As you can see i am somewhat confused as to the best solution to this so all help/thoughts are very welcome.

      Many thanks

      Simon

        • 1. Re: Date calculations in filemaker
          philmodjunk

          The service interval in the bicycle details table is just an number e.g. 5

          Does that "5" represent 5 months?

          • 2. Re: Date calculations in filemaker
            simonbucknall

            Hi Phil,

            Yes it does and the variable can be adjusted by the user.

            Simon

            • 3. Re: Date calculations in filemaker
              philmodjunk

              "the variable can be adjusted" means that the number of months specified as your "interval" can be adjusted?

              And there are two ways to treat as a "month" when dealing with dates. You can consider 30 days to be one month or you can compute a date for the same day of the month, but add 5 to the month value to compute a new month. In other words. If your date is Janaury 1, 2015. 5 months later would be June 1, 2014. The two methods produce similar but not identical results.

              • 4. Re: Date calculations in filemaker
                simonbucknall

                I'm not overly worried about precision of the dates as we visit sites so as long as the due date is less than the next visit date the we are good for a reminder.

                • 5. Re: Date calculations in filemaker
                  philmodjunk

                  Your general description of how you want to do this seems sound, but there is not enough detail as to the excact design of your database to comment in more detail about how to go about doing this.

                  • 6. Re: Date calculations in filemaker
                    simonbucknall

                    Hi Phil,

                    the database relationship is here from my previous problem,

                    http://forums.filemaker.com/posts/af39e05e33?commentId=336587#336587

                    Simon

                    • 7. Re: Date calculations in filemaker
                      simonbucknall

                      I think my main issue is how to get the date of the last service in to the bicycle detail table or use the data in the associated table. I can do it from the script that sets the service to complete but it won't obviously update the historical services - this is a new system using legacy data from our old system. This is the last issue to sort out before we can finish testing and reimport the live data from our old system.

                       

                      • 8. Re: Date calculations in filemaker
                        philmodjunk

                        I thought that I recognized this project, but wasn't about to trust my memory to be accurate and even if I had accurately remembered your design, there was no way to know if the design in that other thread is still your current design anyway.

                        Does your current design match the screen shot near the end of that thread or have you made other design changes such as the changes that I suggested in the last post in that thread?

                        • 9. Re: Date calculations in filemaker
                          simonbucknall

                          Sorry for the delay in responding,

                          Yes relationship has changed a little although i still haven't implemented you solution - i got a bit confused as even you simple solution is at the limit of my ability. However having reread your support article I am feeling a little clearer on that problem. The date issue wasn't a critical issue just a necessary one but we have a temporary work around so it has been moved down the priority list!

                          This one however is so i can't go live without sorting it out.

                          I have attached an updated relationship diagram in ht hope it will make things a little clearer.

                          As always Phil, thanks again for your invaluable help it is immensely appreciated.

                          Simon

                          • 10. Re: Date calculations in filemaker
                            philmodjunk

                            Am I correct that you want to find all records in ServiceVisits with a date in VisitDate that are on or after today but no more than 30 * Interval days into the future from today?

                            Do you want to see these records as a list of records in a portal or a found set on a layout based on an occurrence of the ServiceVisits table?

                            Or perhaps as values in a value list?

                            This script would produce the needed found set:

                            Go to Layout ["ServiceVisits" (ServiceVisits) ]---> any layout with "ServiceVisits" in "Layout Setup|Show Records From? can be used
                            Set Variable [$Interval ; value: YourTable::Interval ] ---->  Don't know what table stores this value.
                            Set Variable [$Customer ; value: YourTable::CustomerCode ]
                            Enter Find Mode [] ----> Clear the pause check box
                            Set Field [ServiceVisits::VisitDate ; Get ( CurrentDate ) & "..." & Get ( CurrentDate ) + 30 * $Interval ]
                            Set Field [CustomerBikes::CustomerCode ; $Customer ]
                            Set Error Capture [on]
                            Perform Find []

                            Note that if you use a layout based on a different occurrence of ServiceVisits such as ServiceVisits 2, you'll need to change the Set Field step to refer to the different table occurrence.

                            You may also need to modify the Start of script depending on which table stores the interval.