1 2 Previous Next 19 Replies Latest reply on Oct 17, 2011 12:10 PM by philmodjunk

    email script



      email script


      Need help from one who will speak simple to me please...Sealed

      I used the inventory database from filemaker, I have expanded it somewhat to suit what I need.  I have been using the late email reminder, but, find it rather time consuming one at a time emails.  (lots)

      What I have done:  made a table layout - I do a manual find; (Not availible) + (== in the check in date, (I want it empty)) + (History table; name)

      It will bring up whomever, and the layout works. It saves as a pdf to the desktop, then the email client goes to work, attaches the PDF and sends to whomever.

      What I would LIKE to do:  ?Write? a script that will do 50+ name finds, create the table, save as a pdf, mail, go to the next person, create the table, save as a pdf, mail.....

      My issue, I don't know how to do it!  LOL Tongue out

      Can someone give me a simple, simple way to do this.  I understand basic script, the else if, I do not have a clue, so some of the stuff, huh?  

      Thanks in advance


        • 1. Re: email script

          How much of this do you know how to set up?

          I see three separate parts to your script:

          Scripting a find or find(s) to pull up the records the script needs to process

          Looping through a found set of records to send out a separate email each time the script "loops".

          Scripting the save as PDF, Send email with attachments--done inside that loop--to send out the emails you want to send.

          If you can tell us what parts of this you can do, we can focus on the parts you don't know how to set up. (And it's OK if we need to start from the very beginning and set it all up one step at a time.)

          • 2. Re: email script

            The script I am TRYING to put together is not working.  I don't know a loop or how to use it.

            What I am doing at this point;  I MANUALLY go to find; go to 'status' and click, 'not available'; next to the history tab and type in the persons last name; staying in the history tab, I then go to 'check in date' and enter == as I want that field empty.

            Once I have the found set, I Click on my script to save and send out email.  

            Here is the script:  

            sort records [restore; no dialog]

            Go to layout ["Inventory Late" (Assets)]

            Enter Preview Mode[]

            Save Records as PDF [Restore; No dialog; 'INVENTORY OVERDUE.pdf"; Records being browsed]

            Send Mail [Send via E-mail Client; to: History Asset ID Flag Checked Out::Contact Email Address with Name; Subject "Late Inventory"; Message: "Attached you will find lalalalalalal"

            Enter Browse Mode[]

            Go to Layout ["Asset Form View' (Assets)]

            It is working, but I would like to be able to set it up where it will 'find' the 'not available' status, then the Check in date blank, then the person's last name, then run the above script, then start all over again, where all I have to do is click the send when the send from client comes up.  (Thinkin there should be a 'pause' of sending email before script starts over with new person.)

            I tried the find, but then I still have to manually go into the script, change the name in the find 50+ times.

            Am I making ANY sense at all?

            • 3. Re: email script

              Need to know each of the tables involved and how they are related to provide full details here.

              First task is to script the find you need. (I'm making a lot of assumptions about the design of your database here. One thing you haven't told me is how the "names" you want to use in your finds are stored in your database. Thus this script, as written may not work at all for you, but maybe it can help you get started.

              #start on layout based on table storing "names"
              Go To Record/Request/Page [first]
                Set variable [$Name ; Value: YourNamesTable::NameField]
                Go To Layout[Inventory]
                Enter Find mode [] //clear the pause check box
                Set Field [Inventory::YourAvailabilityField ; "Not Available"]
                Set Field [Inventory::checkin Date ; "=" ] //no need for == here
                Set Field [Inventory::NameField ; $Name ]
                Set Error Capture [on]
                Perform Find []
                Pause/Resume [indefinitely]
                Go To Layout [Names layout]
                Go To Record/Request/Page [next ; Exit after last ]
              End Loop

              This is not the final version, just a demonstration of how to loop through a set of records in one table (names table) while performing a find for each such name in another table with additional criteria specified (the inventory table). This demo script pauses to display the results of the find for each name before you click continue or press the enter key to continue the script for the next round. In an actual script the Pause step would be replaced with script steps intended to save the PDF then email it as an attachment. (And the script would not need to pause for each email unless you wanted to review each individual email before sending it out.)

              • 4. Re: email script

                I am using the Lending Library Starter Solution from fm 11, there are 3 tables in the starter, I added a forth, zip codes.  

                The 'History' tab is where the check out check in takes place; I did away with the time, but kept the date

                The layout for the names, methinks would be one of the contact views, is that where you want me to start in so far as writing the script?

                Status is still status;  check in date/time is just check in now.  name is just name. I would insert a pic, but I don't have a url, so I don't know how.  

                I will play with it more Monday, as I am off in half an hour.  

                You have always been a great help to me.



                • 5. Re: email script

                  A layout based on contacts would seem a possibility, but I don't think you want to run down the list of every contact in that table do you?

                  I'm thinking you can search History for all records without a check in date and status "not available. T then sort those records by Contact ID to produce a list of all History records. The script can then loop through this found set of unreturned items to produce your emails. Does that sound like what you need here? (This will take a bit different approach from what I originally outlined.)

                  • 6. Re: email script

                    Good Morning,

                    Back to another day of work.  Yes, what you are suggesting is right on with what I would like to do.  Can you help/do that script?  I have not changed much, as I said, in the starter solution, other than possibly the email script, and most likely can't reuse that one as I have probably basterized it.  LOL!

                    Thanks in Advance


                    • 7. Re: email script

                      Go To Layout ["Process Check Out" (History)] //have to use a layout based on history here
                      Enter Find Mode [] //Clear the pause check box
                      Set Field [history::Check In date ; "="] //find all history records where check in date field is empty
                      Set Error capture [on] // keep dialog box from FileMaker from interrupting script if no records are found
                      Perform Find []
                      If [ Get ( FoundCount ) > 0 ]
                        #Pull up all contact records for this set of History records
                        Go To Related Record [Show only related records; ; match found set; From table: Contacts Contact ID; Using layout: "Contact List Report" (Contacts)]
                         Go To Record/Request/Page [first]
                            #Pull up list of History items for current contact record
                            Go To Related Record [Show only related records;  From table: History Contact ID ; Using layout: "Process Check Out" (History) ]
                            #Filter out items returned by this contact
                            Enter Find Mode []
                            Set Field [ History::check in date ; "="]
                            Constrain Found Set []
                            #Pull up matching list of assets for PDF
                            Go To Related Record [Show only related records;  match found set; From table: Assets Asset ID ; Using layout: "Asset List View" (Assets) ]
                            Set Variable [$Path ; "File: " & Get ( TemporaryPath ) & "AssetList.PDF" ]
                            Save Records as PDF [Restore; no dialog ; "$Path" ; Records Being Browsed]
                            Go To Layout ["contact List Report" (Contacts) ]
                            Send Mail [Send via email client; No dialog; To: Contacts::EmailAddresse ; Subject: "Asset List" ; Message: "This is..." ; "$Path" ]
                            #Repeat with next contact in the list. exit after all are processed
                            Go To Record/Request/Page [next ; exit after last]
                         End Loop
                      End IF

                      • 8. Re: email script

                        Thanks!  Shall give it a go and see what will happen

                        • 9. Re: email script

                          It isn't working all the way.

                          I changed the set variable - [$Path ; "File:" & Get (DeskTopPath) & "Inventory overdue.pdf"]

                          and - Go to related record 8th line - Using layout:  "Inventory Late" (Assets) as that is the layout that I made in order to show the asset, the item #, and the picture of said item.

                          It didn't work all the way when I did it without changing anything.  

                          I will keep plugging along, any ideas Phil?

                          It is not letting me save the pdf and it just kinda stops

                          • 10. Re: email script

                            Do you get a PDF file saved to your desk top?

                            The 8th line has to refer to the Contacts Contact ID table occurrence and  a layout based on Contacts--not assets.

                            You can change the 18th line to refer to this layout, but keep the table reference set to Table: Assets Asset ID.

                            • 11. Re: email script

                              Hi Phil,

                              I am finally finding time to get back to this script.


                              It seems to work OTHER THAN it is not saving the PDF.


                              I get this dialog box before each one is emailed.


                              ""AssetList.PDF" could not be created on this disk.  Use different name, make more room on the disk, unlock it or use a different disk"


                              I tried:

                              line 14, I have a layout called late inventory that I would like to send, so I changed 'Using layout: "Asset List View" (Assets)'  to 'Using Layout: "Inventory Late" (Assets)'


                              I have not changed anything else on the script other than above.


                              After the dialog box appears, I still get a email with the persons email address, but nothing attached.


                              I have warned them that they may be getting multiple emails today.  LOL


                              Thanks in advance

                              • 12. Re: email script

                                That error message can be caused by several different causes. The most likely is an invalid file path or file name.

                                I am seeing an extra pair of quotes in your last post: ""AssetList.PDF" could not be c...

                                Is that a typo or is this exactly what is appearing on your screen? If you see the extra pair of quotes, then you have something wrong with your Set Variable set that is trying to produce a file name of : "AssetList.PDF and that extra pair of quotes is the culprit.

                                If that's not the case, please copy and paste the actual expression you have inside the Set Variable step so I can see how it is put together.

                                You can also paste your entire script here.

                                To post a script to the forum:

                                1. You can upload a screen shot of your script
                                2. You can print a script to a PDF, open the PDF and then select and copy the script as text from the opened PDF.
                                3. If You have FileMaker advanced, you can generate a database design report and copy the script as text from there.
                                4. If you paste a text form of the script, you can use the Script Pretty box in the Known Bugs List database to paste a version that is single spaced and indented for a more professional look to your script. (Use the HTML option and paste the text into the HTML editor.)
                                • 13. Re: email script

                                  I have it working now.  I changed line 15 from //set variable [$Path ; "File: " & Get ( TemporaryPath ) & .....  to set variable [$Path; Value:Get ( DesktopPath ) & ......

                                  It is now saving as pdf and on MOST of the email's it is 'attaching' with some of them it is doing an insert in the mail message instead of attaching the PDF, I don't know why it would do that, but, at this point, I am happy that it is working.

                                  Of course, my dear mentor, if you have a suggestion on how to fix that, I would be happy to receive it.


                                  I have attached the script above with my mods

                                  • 14. Re: email script

                                    The difference in the appearance of the attachment is probably due to the different email programs and their settings of the person receiving the email. I doubt that there's much you can do to control this on the sending end.

                                    1 2 Previous Next