13 Replies Latest reply on Jun 29, 2010 9:54 AM by m10muhammad

    Email an offer letter

    m10muhammad

      Title

      Email an offer letter

      Post

      Background: My database is used for tracking printing machinery specifically relating to purchases, sales, inquiries, etc. There are three main tables: Contacts, Machinery, and Requests. The Contact table includes all of the people (customer, dealer) associated with the database. The Machinery table includes information on each machine either in stock at the company or available for purchase. The Request table has a form where customer's inquiries can be stored (i.e. they ask for specific make, model, and provide a budget). There is also a relationship that allows for a portal to be made on the Request layout which will list all of the items in Machinery table that have identical characteristics to those in the Request record (so if the request is for a 1999 Komori AB-200, the page for that request has a portal which lists any machines in the Machinery table that match those criteria). 

       

      What I want to do is set up a script, which, when run, will send an email to a customer that his request has a match, and will provide him the specifics of the match. So a button that runs the script will be placed in the portal next to the details of the related records from the Machinery table, and when it is clicked, an email will be sent to the customer that informs him that his request has a match, and will include the details of his request (i.e. Make, Model, Year, Budget) and the details of the match (Make, Model, Year, Price, etc.). 

       

      Please advise how this can be done. Thanks. 

        • 1. Re: Email an offer letter
          philmodjunk

          Are you familiar with the Send Mail command? Can you use it to send an email from filemaker? (That will confirm that you have an email client or SMTP email server that you can use.)

           

          You can construct a calculation in the Send Mail dialog to combines text from your fields with other text you type in to create a message to that person in the email body. You can script this process.

           

          Email from Send Mail is limited to only plain text. If you need more elaborate formatting, there are plug ins you can use.

          • 2. Re: Email an offer letter
            m10muhammad

            No I am not familiar with the Send Mail function. It would be appreciated if you could explain this command to me a bit. Thanks 

            • 3. Re: Email an offer letter
              philmodjunk

              You'll need to look it up in help and confirm whether you have an email client that is compatible with filemaker.

               

              Select Send Mail from the File Menu, fill in the boxes and send the email. That's a good test to see if you have everything working on the email program. Each box has a button next to it whith a black arrow or triangle on it. You can click these buttons to bring up a specify calculation dialog where you can construct a calculation that produces the info you want to see in the various parts of your email. This is how you can set up an email to go to a person whose email is stored in a field in your database and how you can construct a message to them that includes information in various tables and fields.

               

              Once you can send such an email succussfully from the File Menu command, you can add the Send Mail Script step to a script where you can open up the same dialog I just described for sending emails via a script.

              • 4. Re: Email an offer letter
                m10muhammad

                When I set up the Send Mail box, in the Message section I choose Specify Calculation and here I enter the body of the text with the various fields interspersed with normal text. However I receive the error message that the specified table cannot be found. Why is this? Do I need to put the specific fields within quotations or something? And what exactly does the "Create: One email using data from the current record / Multiple emails (one for each record in found set" option signify? 

                 

                Just to clarify the situation, I have in the layout based on Requests table a portal which shows any matches between the specific Request record and item(s) in the Machinery table. So I would need a button in the portal next to each matching Machine that will run the script to send the email notifying the customer who made the request that we have a match for his inquiry.  

                • 5. Re: Email an offer letter
                  philmodjunk

                  Consider your email body a calculation field that returns text. You'll need an expression formatted like this:

                   

                  "Dear " & Table::firstnameField & ¶ & ¶ & "Thank you for expressing interest..."

                   

                  Put your literal text in quotes and use the list of fields in the specify calculation dialog to add fields to it. The ¶ I used is to add a carriage return.

                   

                  "what exactly does the "Create: One email using data from the current record / Multiple emails (one for each record in found set" option signify? "

                  If you are just sending one email to one person, use the first option. If you want to send one email to a list of people, use the second.

                   

                   

                  • 6. Re: Email an offer letter
                    m10muhammad

                    Once I have created the Calculation for the body of the email, how do I create the script/button which I can place in the portal, and when I click it, the email will be sent?

                    • 7. Re: Email an offer letter
                      philmodjunk

                      Create a script an add the send mail script step to it. Click Specify and you'll get the same dialog we've been discussing so that you can enter your calculation there where it will be saved as part of the script.

                      • 8. Re: Email an offer letter
                        m10muhammad

                        Just to clarify the situation, I do not want a one-off email to be send to a specific customer regarding any matches between his request and our machines. What I want is an option (in the form of a button) on the portal, which displays information from Machinery table, which can be found on the Request table. The Request table has fields such as Make, Model, Year, Budget, and Requested By (which is related to the Contact table). On the Request layout, there is a portal which displays all records from the Machinery table which have the same Make, Model, Year, and Price as the values specified in the Request record. This button would be alongside each related record found in the portal (so if two Machines match a Request, the two rows on the portal which give the information about the matching Machines would have the button in the row). The email message would basically reiterate the Request characteristics of the Contact (using his email in the related Contact table) and also let him know of the availability of the matching Machines. So depending on the specific Request, matching Machine, and Contact, each email would have a different message. 

                         

                        The purpose of this button is to provide a simple way for users of the database to let their customers know that their request has a matching item in our inventory. Please advise. Thanks. 

                        • 9. Re: Email an offer letter
                          m10muhammad

                          This is the calculation I entered in the message:

                           

                          "Dear" & Contact::Name  & ¶ & ¶ & "On" & Request::Date & "you requested a machine with the following characteristics:"  & ¶ & "Manufacturer:" & Request::Manufacturer & "Model:" & Request::Model & "Year:" & Request::Year & "Price:" & Request::Budget & "." & ¶ & "We are happy to inform you that we have a product which matches your request, which can be bought for" & Price &  "." & ¶ & "We look forward to your response."

                           

                          Is this a correctly formatted calculation?Also, the last field specified in the message, i.e. Price, is not in the same format as the other fields, such as Date, Manufacturer, Model, etc. Is this due to the fact that the email is based on the Machinery table which includes the field Price? 

                           

                          Also, the email client I use, Apple Mail, is supported by FileMaker per their help topic on the issue, but I cannot find instructions on how/if to configure FileMaker so that it can access my Mail account.

                           

                          Please advise. Thanks. 

                          • 10. Re: Email an offer letter
                            philmodjunk

                            Have you tried just using a send mail command to send an email to yourself? If you can do that you should be in good shape for your scrip to do that.

                             

                            for" & Price &  "."

                             

                            That looks like incorrect syntax as you should have a tableoccurrence name and :: preceding the field name. I'd expect you to get an error message if you tried it with that expression. (Did you click the button with the black arrow so that you are entering this calculation there? That option enables the Specify Calculation Dialog to catch syntax errors for you.)

                            • 11. Re: Email an offer letter
                              m10muhammad

                              OK I re-formatted the calculation so that the desired message shows up and I successfully sent it to myself so that part works just fine. The only issue I am having is that the fields in the message are not relating to the specific record but always to the FIRST record, i.e. for the Contact::Name field, it doesn't use the name of the Contact who made the specific request, but always for the first contact in the system. For the Request fields (Request::Manufacturer, Request::Model, etc.) it doesn't use the information of the specific request but of the first request in the system. And, for the matching machine information, such as Machine::Manufacturer, Machine::Price, etc., it doesn't use the information of the matching machine in the row of the portal in which I pressed the button, but just uses the information of the first machine. Basically, regardless of which contact made the request, what he requested, and which matching item I want to tell him about, each email is exactly the same. 

                               

                              Please advise how this can be fixed. Also note that the calculation is based on the Machinery table, even though the Email Offer button will be on the Request layout as well as the Machinery layout (the button will be on the portal on each Request record showing matching Machines, as well as on the portal on each Machine record showing matching Requests. Please also advise if the calculation should be based on another table or not. Thanks.  

                              • 12. Re: Email an offer letter
                                philmodjunk

                                You'll need to take several things into account. Your Send Mail calculations will evaluate from the context of the current record on the layout that is current at the time it is executed. Your script can switch layouts and find records as needed to establish the correct context. You can also use set variable to capture key items while one one record and/or layout and then refer to the variable to get the right value into your expression.

                                 

                                In your case, I believe you'll need your script to switch to a layout based on your portal records using either a Find or Go To Related record to find that specific portal record and make it the current record. Executing your Send Mail step from that record should then work for you. After sending the email, your script can return the user to their original layout.

                                • 13. Re: Email an offer letter
                                  m10muhammad

                                  Thanks, I added the step to Go to Related Record (from Machine table) before the Send Mail step and it worked like a charm. Thanks!