1 2 Previous Next 25 Replies Latest reply on May 2, 2012 1:06 PM by Tucker

    Date Addition Based On Weeks

    Tucker

      Title

      Date Addition Based On Weeks

      Post

       I have a layout where you can choose a date to set an appointment with a contact.

      Contacts do have a field called Frequency which contains the frequency in weeks for repeat calls.

      Instead of just picking the next appointment date from the drop down calendar, I would prefer if a calculation was performed to establish the next call date based on the frequency value that already exists in the contact Frequency field.

      How would I create a new appointment date in the appointment date field by automatically adding the call frequency weeks to todays date? I'm guessing there would be a script trigger upon ObjectEnter of the drop down calendar that would run a script that performs the calculation. I just don't know what the calculation would look like.

        • 1. Re: Date Addition Based On Weeks
          philmodjunk

          Get ( CurrentDate ) + Frequency * 7

          will compute a date in the future that is 7, 14, 21, etc days in the future if Frequency is a field storing 1, 2, 3 for the number of weeks untill next appointment.

          • 2. Re: Date Addition Based On Weeks
            Tucker

             Thank you for the insight Phil. Now I have an added twist. The call frequency in the contacts table is not the actual number of weeks but instead is a foreign key that points to a table called CallFrequency. In the CallFrequency table there is the primary key field and a number field called Weeks. The weeks field holds the value of weeks for frequency of calls (1, 4, 12...etc).

            I set up an expression on my Appointment layout (in the appointment field) that states: Get (CurrentDate)+(CallFrequency::Weeks*7).

            Now, when on the layout in the appointment field - On ObjectEnter, the date is automatically set to todays date but the added weeks are not reflected. What am I doing wrong?

            Thanks

            • 3. Re: Date Addition Based On Weeks
              philmodjunk

              Is the relationship to CallFrequency a valid relationship at the time the triggered script executes? If you are using FileMaker Advanced, you might want to use the debuger and data viewer to check this detail by using the debugger to step through the script and the debugger to see if CallFrequency::Weeks has a value at the time the script step executes.)

              Referring to a related table will refer to data in the first related record. Is there more than one related record? Does the first related record contain the desired info?

              I'd be inclined to set this up as an auto-entered calculation instead of using a script trigger.

              • 4. Re: Date Addition Based On Weeks
                Tucker

                 Yes, the relationship is valid before the triggered script executes. The CallFrequeny table has a one to many relationship with the Contacts table. The Contacts table only contains the CallFrequencyID foreign key. 

                As you mentioned, I should set this up as an automatic calculation in the appointment date field. I will try adding the same script there.

                 

                • 5. Re: Date Addition Based On Weeks
                  philmodjunk

                  The script should work though. If there is some reason that it isn't, that issue could affect the auto-enter calculation as well.

                  • 6. Re: Date Addition Based On Weeks
                    Tucker

                     Hi Phil. I have reorganized the database and have the following tables set up.

                    CallFrequency -->Company-->Contacts-->Appointments

                    The CallFrequency table has 3-fields: __pkCallFreqID, CallFreqType, CallFreqDuration

                    Records in the CallFrequency Table are as follows:

                    ID      CallFreqType     CallFreqDuration

                    1            Weekly               1

                    2            BiWeekly             2

                    3            Monthly               4

                    Company has the foreign key CallFreqID and a drop down box with a value list that uses CallFreqType (text) so you can pick one of various frequency types (Weekly, BiWeekly, Monthly...).

                    The appointments table has a date field called AppointDate. This field uses a drop down calendar and allows picking of a future appointment date.

                    My goal is to add the CallFreqDuration (expressed in weeks) to today's date to establish the next AppointDate value. I have tried:

                    Get (CurrentDate)+(CallFrequency::CallFreqDuration*7) as a script trigger and as a calculation right in the AppointDate field but no luck. It appears that the CallFreqDuration cannot be found. I'm really stuck on this one!

                    Thanks

                    • 7. Re: Date Addition Based On Weeks
                      philmodjunk

                      Company has the foreign key CallFreqID and a drop down box with a value list that uses CallFreqType (text) so you can pick one of various frequency types (Weekly, BiWeekly, Monthly...).

                      So your relationship looks like this?

                      Company::CallFreqType = CallFrequency::CallFreqType

                      I'm not sure I understand how/why you are using fields in Company for both the type and the ID here. It would seem that just identifying the Type would be sufficient.

                      If you put a portal to CallFrequency on your Company layout, what do you see in it?

                      How does the appointments table link to the Company table?

                      • 8. Re: Date Addition Based On Weeks
                        Tucker

                         Let me explain in more detail. On the Company Edit layout I have placed the field CallFreqID and it uses a drop down box. The drop down box uses a value list that shows CallFreqType from the table Call Frequency. This works fine.

                        The appointments table does not have a direct connection to the Company table or the Call Frequency table. There is a TO of the Contacts table to Company though.

                        • 9. Re: Date Addition Based On Weeks
                          philmodjunk

                          Is this field where you need to see the appointment date defined in contacts or appointments?

                          • 10. Re: Date Addition Based On Weeks
                            Tucker

                            The appointment date field is defined in the appointments layout. Do I need another TO? 

                            • 11. Re: Date Addition Based On Weeks
                              philmodjunk

                              IF there is no relationship between the appointment table and the company table, how can a calculation access the needed Frequency value from the Company field?

                              • 12. Re: Date Addition Based On Weeks
                                Tucker

                                 There is no direct relationship but an indirect relationship exits (travelling backwards from Appointments table) via Contacts and then Company.

                                Company carries a CallFreq ID, Contacts carry a Company ID and Appointments carry a Contact ID. Perhaps the relationship is too many tables away or as a newbie I have lost grasp of how the relationships actually work? The latter would not come as a surprise. :)

                                • 13. Re: Date Addition Based On Weeks
                                  philmodjunk

                                  This is what I think you have:

                                  CallFrequency----<Company----<Contacts------<apppointments     ---< means "one to many"

                                  CallFrequency::__pkCallFrequencyID = Company::_fkCallFrequencyID
                                  Company::__pkCompanyID = Contacts::_fkCompanyID
                                  Contacts::__pkContactID = Appoitnments::_fkContactID

                                  If that doesn't look correct, please upload a screen shot of Manage | Database | Relationships. (zoom and crop it to remove unused space.)

                                  As I understand it. The frequency is stored in callFrequency and it must be used to compute a date in a related record in appointments.

                                  That should work, provided that there exist correctly related records in contacts and company at the time the new record is created in Appointments.

                                  Exactly what steps are you taking to create a new appointments record for a given contact?

                                  • 14. Re: Date Addition Based On Weeks
                                    Tucker

                                     

                                    It's working with a couple of changes...

                                    Was: Company has the foreign key _fkCallFreqID and a drop down box with a value list that uses CallFreqType (text) so you can pick one of various frequency types (Weekly, BiWeekly, Monthly...).

                                    Is Now: Company has the foreign key _fkCallFreqID and a drop down box with a value list that uses CallDuration (number) so you can pick one of various frequency durations in weeks (1, 2, 4, 26, 52).

                                    The trigger script now states  Set Field [Appointment::ApointDate; Get (CurrentDate)+(Company::_fkCallFreqID*7)]

                                    This script adds the preset Company call frequency to the next appointment date. I only wish I could select the FrequencyType instead (Weekly, Biweekly, Monthly....)

                                     

                                     

                                     

                                    1 2 Previous Next