7 Replies Latest reply on Feb 6, 2014 8:56 AM by philmodjunk

    Exporting report for every occurance as pdf, then sending a custom e-mail with file attached

    DagKnepr

      Title

      Exporting report for every occurance as pdf, then sending a custom e-mail with file attached

      Post

           Jeeeez guys, when I started meddling with FM, I had no Idea of the vast possibilities it has... Now, I want more and more every day.

           Now this is one problem I can't seem to solve so thought you might have some ideas how to do this.

           This is the situation:

           FM 11 on OSX 10.6.8.

           Created a database of invoices for my non profit.

           As it happens, a lot of people are overdue on their issued invoices.

           So I got the idea to create an automated script that will when I activate it create a PDF report containing invoices overdue for every person. And then compile a personalized email that attaches the PDF for that person and then sends it to them.

           Simple, ha? Well, I know how to create a report which has one persons overdue invoices on every page when in Preview. But I can't figure out how to make FM export each page by itself and name each file according to the data that person has. Had I managed this I think I could manage to automate the email part of the script, but I got stuck here...

           Now, this seems horribly complicated to me, but I guess to someone reading this it could be piece of cake.

           So please, if you have the time to suggest a solution, I will be eternally grateful.

            

        • 1. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
          philmodjunk

               Sounds like you need to constrain your found set to just the records for one person at a time, then your PDF using the 'Records being browsed' option will only contain the overdue invoices for that one person.

          • 2. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
            DagKnepr

                 Thanks for the fast reaction.

                 Sure, that would work but how do I then automate that the script goes from one person to the other and makes a constrain on that persons invoices, then the process of creating report, PDF, email... And then move to the next person for the same process?

                 I know I could go by hand from one person to the other and do this but after doing this manual for months now, I'd like it to be a one click operation...

                 My goals are high! :)

            • 3. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
              philmodjunk

                   It's difficult to describe in detail as I do not know the underlying table/relationship structure of your database, but here's a broad outline:

                   On a layout based on your people table, perform a find for all people with at least one outstanding invoice

                   Loop through this found set to:

                        Set Variable [$PersonID ; value: Persons::PersonID ]
                        Go to Invoices Layout
                        Enter Find Mode []
                        Use set field steps to specify find criteria for your unpaid invoices. Include $PersonID to limit found set to records for that one person
                        Compute $Path variable
                        Save As PDF  (using $path variable to specify file name and save location such as the Temporary Folder)
                        Send Mail (using $path variable to attach PDF)
                        Go to Layout [persons
                        Go to record/request/page [next ; exit after last]
                   End Loop

              • 4. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
                DagKnepr

                     Hey Phil, I know I'm pushing my luck but could I give you some more details to help you understand the structure? I tried implementing your suggestion but just can't seem to see the logic of how to do it...

                     The structure is simple. One table containing invoices as records. Each record has these fields, relevant to this operation:

                       
                •           Person (Contains person full name)
                •      
                •           Persons email (Obvious)
                •      
                •           Invoice number (Contains numbering)
                •      
                •           Invoice overdue? (Contains Yes if late)
                •      
                •           Invoice amount

                     So, what I want to do is:

                       
                •           Find Invoice overdue = Yes
                •      
                •           Sort by Person
                •      
                •           Create report that forms a list of all invoices overdue by each person containing their total summary
                •      
                •           Export report for each person to temporary folder as PDF
                •      
                •           Email each person an evil sounding email with PDF attached
                •      
                •           Loop this through all persons with invoices overdue
                •      
                •           Create custom message saying "That's it! Have a nice day!" :)

                     As said before, each individual segment of this I understand and know how to do. What I don't understand is how to tell FM to loop this for every unique Person name once without naming each person by hand within the script... Especially since I don't want to export each record but rather all overdue records of a unique person.

                • 5. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
                  philmodjunk

                       Note the steps that change layouts in my example. I am assuming that you have two related tables here and a layout for each. On your Persons layout you pull up a list of Persons that have overdue invoices. Your script then loops through this found set as shown in my example. To save a PDF of the invoices, it changes layouts to the INvoices layout and finds all over due invoices for that person. It then returns to the Persons layout so that it can do a go to next record and exit the loop after processing the invoices for the last listed person.

                  • 6. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
                    DagKnepr

                         Hey Phil, you helped me a lot, I started thinking in the right way and solved it. Today the first series of e-mails went out. They are almost exactly as I wanted them. However, I still have a few issues:

                           
                    1.           I thought this will be trivial but I can't seem to get a FM to keep track of today's date all the time. I use this so that every FM knows if an invoice is overdue or not. This is how I tried to set it up: I have a field which has the due date. I created a field that should calculate today's date via Get(CurrentDate) and I have a field that compares the two and spits out a Yes or No on whether the invoice is due. Now, this worked fine for the first day, but on the second day the today's value got stuck on yesterday. It doesn't update every day. I just can't figure out how to have an always updated today's date in a field.
                    2.      
                    3.           I created a script for the three steps the full script takes. 1 find all people that have invoices overdue, 2 make PDF of the record, 3 send e-mail to person with attached PDF. Each of these 3 works great. Then I went and created a script that combines the last two and loops until it comes to the last person on the found list. However, the script seems to run to fast for the operations. It went through all the records and sent them e-mails, some of them did get the attachment but a lot of them did not, as if the create PDF part couldn't keep up with the send e-mail part... Do you think I should incorporate a 3 second pause within a loop or what?! Or do you think I have another problem? In the end I browsed through all the people by hand and clicked Send warning for every person. This worked fine.
                    4.      
                    5.           One thing I didn't see coming, I use a Mac and the Mail application. I have believe it or not 25 accounts set up in Mail. So FM chooses a random one and sends the e-mails from it. I'll have to set up the SMTP option to keep this in check... :)

                         In every case, thank you for your support!

                    • 7. Re: Exporting report for every occurance as pdf, then sending a custom e-mail with file attached
                      philmodjunk

                           1) Your calculation field with Get ( CurrentDate ) must be setup as an Unstored calculation field or it will not update with each new day.

                           2) I'd need to see the script to be certain, but a pause may indeed be necessary so that the script can be sure that the PDF has been generated before it tries to attach the PDF to the email.