8 Replies Latest reply on Mar 9, 2015 10:55 PM by philmodjunk

    Help with calculation with 2 variables

    JonathanYoung

      Title

      Help with calculation with 2 variables

      Post

      I am looking for a calculation or formula to help me send out information at different intervals. I have clients that need to receive emails weekly or semi weekly depending on how many days are left before travel. For example a client needs 13 different emails over a 92 day span (date of contact - date of arrival), emails should be sent out on Thursdays on a regular basis. Or a client needs 18 different emails but only has a 36 day span. Emails then could be sent out on Mondays as well as Thursdays. Coding and logic are not my forte so I may have some follow up information if needed. Thanks.

        • 1. Re: Help with calculation with 2 variables
          philmodjunk

          And how is this data recorded in your database? What tables and relationships are involved? The lack of such detail may be why no one has yet responded to your question.

          • 2. Re: Help with calculation with 2 variables
            JonathanYoung

             

             Understood. The database is simple (or simply a mess because i am learning). The main table is a client info table where clients travel plans are recorded. There a a couple related tables for attractions and different travel agencies. In the client table I have a simple date of contact <Date Entered> field which is the first time a client contacts us. The other field of importance is <travel arrive> . I need to create a script or calculation that will email (links to stored documents (in dropbox ) or the documents themselves. The  problem I am having is the logic. Clients travel on different dates and have different number of documents they receive, as well as how much time is between the date of contact and date of arrival (lets call that  <Days till Travel> . The last part of my problem (at this point) is, documents should be sent out evenly across the initial <Days till Travel> but only on Thursdays.

            For example a client has a <Date Entered> of 3/14/15 and a <travel arrive> of 9/22/15 therefore an initial <Days till Travel> calculation of 192 days. There are variable number (n) documents that need to be sent out as evenly as possible (on Thursdays and Mondays) over the course of the 192 days. The number and type of document differs based on different criteria. A simple check box would select the individual documents that needed to be included in the calculation. (i.e. if document A and document B are to be included but not document C, then documents A and B would be checked by the travel planner and document C would not. 

            So I think some sort of count the checked boxes function that divides into the <Days till Travel> but only counts Thursdays and then will email the resultant number of documents on each Thursday. Should a field that checks whether a document has been sent or not be created as well? I am using FMP 13 Advanced.  I am a neophyte and still experimenting/trying to understand it all. So much is over my head but I am willing to try. Thanks for any help and appreciate the patience.

             

            • 3. Re: Help with calculation with 2 variables
              philmodjunk

              Do you know how to send out an email with a file Attachment from FileMaker?

              Are you aware that Send Mail can only send out emails with a single attached file? (To attach more requires getting a plug in.)

              Do you know how to use container fields such that each document (or reference) is inserted into a container field?

              It would seem that one of the first things that you need is a table linked to clients where each document associated with that client (or better yet with that client's trip)  is a separate record.

              • 4. Re: Help with calculation with 2 variables
                JonathanYoung

                I currently do send out an email based on a return date. (Find all records that have a return date of x and send an email with a linked file.)Yes it is one attachment at a time and that is not ideal but ok at this point.

                Looping and sending multiple emails is a viable option .

                I have used containers for some other projects. 

                There are only about 20 or so documents. I could set up a separate table with each document it's own record. Still need to be able to script out or calculate the email issue.

                • 5. Re: Help with calculation with 2 variables
                  philmodjunk

                  But are you able to use a script to do this using a $Path variable to refer to the attached file?

                  What I am trying to do is narrow the scope of your question to the specifics where you need help. There are a lot of steps to this process and would hate to spend a lot of time telling you how to do something that you already know how to do.

                  • 6. Re: Help with calculation with 2 variables
                    JonathanYoung

                    But are you able to use a script to do this using a $Path variable to refer to the attached file?

                    I don't know what that is. Hope the screen shot captured the simple script used to send the mail and attachment.

                    Thanks for the continued assistance!

                    • 7. Re: Help with calculation with 2 variables
                      JonathanYoung

                      So the short answer is No. I don't  know how to use the $Path Variable to email the document.

                       

                      • 8. Re: Help with calculation with 2 variables
                        philmodjunk

                        A $path variable allows you to refer to both the name and location of the file without explicitly stating either and this will likely be necessary here. 

                        You can use it with export field contents to export a copy of the file and the use the same variable to attach it to the Email.

                        See this link on $path variables:  Exploring the use of a $Path Variable in Scripts