6 Replies Latest reply on Feb 24, 2011 2:20 PM by philmodjunk

    Need Help Fixing Send Records as PDF and Send Email issue

    RussSchneck

      Title

      Need Help Fixing Send Records as PDF and Send Email issue

      Post

      I have a database set up with records created for multiple different clients and multiple service jobs done for each client every month. For the past several years, I have just printed out the invoice layout by running a script that found those jobs that were not yet paid for, sorting by client and date of job and then printing an invoice showing each job as a separate line item on the invoice. Print the envelope and mail it off.

      Now, I'm trying to change this process by sending the invoice by email instead. I created a script that finds those jobs not paid for, sorts them as before, goes to invoice layout and sends records as PDF and then Send Email. The problem is in the script I have tried two different things and get a different AND unacceptable result. If I choose "current record" (starting at first record and looping until last record) I get an invoice emailed to each customer for each job - for some clients that could be 10-15 jobs in a month! What I want is one invoice emailed to each client with all jobs listed on that invoice. Now, the other thing I have tried is choosing "records being browsed" which did appear to work as it sent out one invoice to each client but with one big PDF file of everyone's invoice attached!

      Any help you can give me would be greatly appreciated.

      Russ

        • 1. Re: Need Help Fixing Send Records as PDF and Send Email issue
          philmodjunk

          Use the Records Being Browsed Option, but just before you do, perform a find to pull up just the Jobs you want for that PDF.

          • 2. Re: Need Help Fixing Send Records as PDF and Send Email issue
            RussSchneck

            OK, I understand what you are asking but here is my script and I'm not sure how to try what you are suggesting.

            Email Billing Statements
            Go to Layout [ “Billing Statement” (Vet Rad DB) ]
            Perform Find [ Specified Find Requests: Omit Records; Criteria: Vet Rad DB::Invoice Paid: “X” ]
            [ Restore ]
            Sort Records [ Specified Sort Order: Vet Rad DB::Hospital Name; ascending
            Vet Rad DB::Date of Procedure; ascending
            Vet Rad DB::Patient Name; ascending ]
            [ Restore; No dialog ]
            Go to Record/Request/Page
            [ First ]
            Loop
            Set Variable [ $FilePath; Value:"filemac:" & Get (TemporaryPath) & "Statement.pdf" ]
            Save Records as PDF [ File Name: “$FilePath”; Records being browsed ]
            [ Document - Compatibility: Acrobat 7 and later ]
            [ Pages - Number Pages From: 1; Include: All pages ]
            [ Security - Printing: High Resolution; Editing: Any except extracting pages; Enable copying; Enable Screen Reader ]
            [ Initial View - Show: Pages Panel and Page; Page Layout: Single Page; Magnification: 100% ]
            [ Restore; No dialog ]
            Send Mail [ Send via SMTP Server; To: Hospital Info::Email; Subject: "Monthly Billing Statement"; Message: "Please find attached
            your Monthly Billing Statement from Dr. Bennett Fagin of Vet-Rad, Ltd. We thank you for your business and look forward to
            continuing to serve you."; Attachment: “$FilePath”; Name: "Dr. Bennett Fagin"; Email Address: "bennettfagin@gmail.com";
            SMTP Server: "mail.quadstar.com"; Port: 25; Authentication Type: Plain Password; User Name: "russ"; Password: "qsmail" ]
            [ No dialog ]
            Go to Record/Request/Page
            [ Next; Exit after last ]
            End Loop
            Enter Browse Mode
            Show/Hide Status Area
            [ Hide ]
            Go to Layout [ “Main Menu” (Vet Rad DB) ]

            • 3. Re: Need Help Fixing Send Records as PDF and Send Email issue
              philmodjunk

               Seems like you have more than one problem with that script.

              You go to one layout, perform a single find and then loop through it sending out emails. Seems like you need loop through these records one customer at a time rather than one record at a time as it sounds like each customer invoice represents a group of records in the Vet Rad DB table.

              Do you have a table where you have one record per customer? If you did, you could perform a find on that table to get your list of customers and their email addresses to step through one record at a time. Your script would then switch to the layout you use in the above script, performs a find to pull up their statement to save as PDF and email out. It then returns to your original layout before looping to the next record.

              • 4. Re: Need Help Fixing Send Records as PDF and Send Email issue
                RussSchneck

                Yes, this is an old database (probably Filemaker 2 or 3) that I have updated over the years. There are only two databases: Vet Rad DB - that keeps all the records of each job done for a Vet, and Hospital Info that has the address, Doctors Name and email address. Not real hi-tech or code intensive. I'm not really sure what to do next to get this fixed. Any help you can suggest would be much appreciated!

                • 5. Re: Need Help Fixing Send Records as PDF and Send Email issue
                  mgores

                  Russ,  if you already had a script that printed out the invoices correctly wouldn't it be easier to just change the print option to save as pdf, then add the send mail step.

                  BTW, I hope you put some made up data in there for your SMPT server authentication, otherwise you may want to change your password.

                  • 6. Re: Need Help Fixing Send Records as PDF and Send Email issue
                    philmodjunk

                    There are only two databases: Vet Rad DB - that keeps all the records of each job done for a Vet, and Hospital Info that has the address, Doctors Name and email address.

                    Then you are looping through Vet Rad DB when you should be looping through Hospital Info.

                    Mark makes a very good suggestion here, but if you want a script that does this all in a batch, you can do it with just a few changes to your existing script. Here's an outline on how to modify your script:

                    Go To a layout for Hospital Info
                    Perform a find for all Hospital Info records to which you need to send a PDF.
                    Loop
                       #Using data in current Hospital Info records, switch to a Vet Rad DB layout and perform a find for just that one Hospital Info Record
                       Set variable [$DoctorID ; Value: HospitalInfo::DoctorID]
                       Go To Layout [Vet Rad DB]
                       Enter Find Mode[]
                       Set Field [Vet Rad DB::DoctorID ; $DoctorID]
                       Set Error Capture [on]
                       If [Get ( FoundCount ) > 0 ]
                         //save as PDF here
                         Go To layout [//hospital info layout]
                         Send your email for this saved PDF
                       Else
                         Go To Layout [//hospital info layout]
                       End if
                       Go To Record [next ; exit after last]
                    End Loop

                    I haven't tried to fill in every detail here. (// means a place where need to specify details to fit your database.) But you should be able to use that script outline to get this to work. The key is to loop through the Hospital Info records to generate each email, one to each email address in that table, but use a Vet Rad DB layout for creating the PDF files for attachment to these emails.