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.
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! :)
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]
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.
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.
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:
- 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.
- 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.
- 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!
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.