one for the geniuses....

[MParker]

is it even possible...i'm not sure...but i figured that this would be the place to ask.

i have a report that prints out monthly totals for a residual payment. it collects all the data from the past month, then puts it into a report. it seperates each page by payee. is there any way to have it email the page that belongs to that person to the correct email address for that person?

[Fitch]

By "email the page" I presume you mean save the page as a PDF.

Unless there is only one record per payee, I think you will have to report on one person at a time.

That doesn't mean you have to do it manually - you can make a script with a loop that creates the report for each person.

[mr_vodka]

You can script it to automatically email by using a plugin with email capabilities or upgrading to FM10.

[Dave Graham]

You can script it to automatically email by using a plugin with email capabilities or upgrading to FM10.

Unless I'm missing something, we've been able to script PDF creation and e-mail with a single attachment since FM 8.

[MParker]

yeah, i know that you can email a report. i've been doing that for a while. but the report is 182 pages, each page is one payee. i need each page to be seperated and emailed to the correct person. is that possible? Fitch, any recommendations on this loop script? i've never dealt with a loop function before.

[mr_vodka]

Unless I'm missing something, we've been able to script PDF creation and e-mail with a single attachment since FM 8.

Yes but it opens up the email client. So he would have to send each email with attachment seperately to each person from the default email client.

New new SMTP feature (or using a plugin with earlier versions) will send it seemlessly without the client.

[Dave Graham]

...the report is 182 pages, each page is one payee. i need each page to be seperated and emailed to the correct person.

There are a few ways to do it, but this should do the trick. Add a loop in your payees table which:

1. performs a find in the payees table for all payees for that month.

1. set a variable to the current payee id

2. switch layouts (or open new window) to the payment data table and do a find as normal but add the payee id.

3. your report should now be constrained to the payee and you can output and mail PDF.

4. switch back to the payee table and go to the next record (exit after last). repeat 1-4 until done.

[Dave Graham]

Yes but it opens up the email client. So he would have to send each email with attachment seperately to each person from the default email client.

I agree; there is an advantage in FM 10 or with plug-ins, but he never mentioned trying to avoid the mail client. I just didn't want him to think that he would have to upgrade or obtain additional software to do this. Good point.

[MParker]

thank you all for your help.....sadly...i'm having a bit of trouble with it....this is what i've got so far....

Click for full size

any ideas where i'm going wrong? it's freezing on me, and i'm assuming that's because i have it looping wrong.

Edited January 15, 2009 by Guest

[MParker]

ok, found why it was freezing...it was because i didn't have the "else" function in the "if" statement....duh! but sadly...it still isn't working.

[bcooney]

1. you need a go to next record (exit after last) just before the End Loop.

2. $$pdf does not need to be a global variable, $pdf will do fine.

3. you do not need to Adjust Window.

4. you need to test the found count before you enter the loop.

so,

if foundcount>0

loop

else

halt

endif

Edited January 15, 2009 by Guest

[MParker]

umm.....ok.....now it does nothing at all. what did i do wrong now?

click for full view

[bcooney]

loop comes right after perform find

[bcooney]

Listing the key elements:

Perform Find

// Found Records

If Get FoundCount >0

Go to Record (first)

Loop

If not isempty( email)

gtrr new window

save as pdf

close window

endif

Go to Record Next (exit after last)

Else

//no found records

Show Dialog

Show All

EndIf

PS: Don't show dialogs within a loop

Edited January 15, 2009 by Guest

[MParker]

ok...that got me a little further, but now it's going spastic. hahaha. it will export a pdf, but then it disappears as it gets to the next record.

:?

click for full view

[MParker]

ok, trying your way, but it's telling me that i can't use a ">" in the 'get' function.

[MParker]

where does the 'end loop' go in that one?

[bcooney]

Sorry, this is not real script code.

Corrected

Perform Find 



// Found Records

If (Get FoundCount) >0



   Go to Record (first)

   Loop

        If not isempty( email)

           gtrr new window

          save as pdf

          close window

       EndIf

     Go to Record Next (exit after last)

   End Loop



Else

//no found records

   Show Dialog

   Show All

EndIf

Edited January 15, 2009 by Guest

[MParker]

ok, that works! thank you for all of your help! now...on to problem number 2....i need to be able to group by payee....is there any way to group the payees in a script?

[bcooney]

I'm not really familiar with your structure. Aren't you finding in the payee table? Could you find payments, then gtrr payees (match found set) and loop from there? What is your structure?

Edited January 15, 2009 by Guest

[MParker]

the problem is that i have multiple items that i need to sum up per payee. so, i have to pull all records that belong to that payee and then total them, then email that statement to the person.

[bcooney]

Can you attach a version of your file with enough sample data in it?

[MParker]

sure thing. let me get one

[MParker]

ok, i stripped out all of the private info and company info. here it is....

Database File

[bcooney]

It needs to be zipped and attached using the forums file mgmt routine (switch to full reply view).

[MParker]

opps. sorry. ok, here it is.

EOM.zip

[MParker]

by the way...i just updated to fmp10a. i figured i've been putting it off long enough. so, if there's a function that will help in the new version that i didn't have before, please feel free to let me know that too. i'm not sure what all is new yet.

[bcooney]

Found quite a few things. Rewrote you Email Statement script, and established two new relationships. Have a look and feel free to ask questions.

EOM.fp7.zip

Edited January 16, 2009 by Guest

[MParker]

it seemed to work to an extent, but there's problems. maybe it's something i did wrong. i'll have to look into it on monday, i'm leaving for the day. thank you again for all you help...i'll let you know what's going on on monday.

thanks!

[bcooney]

OK, have a good weekend. Let me talk you thru the script.

Rather than using Find, I built a relationship that isolates the records within the requested Month filters AND that have an email address.

First, I "look" thru this relationship to see if there are any records found. If not, dialog and halt.

Otherwise, I use this relationship to isolate the records for the requested date range, and switch to the report layout. Set the print setup and sort by payee.

Now we process the found records. I only want to produce a pdf once per payee. So, at each turn of the loop, I check to see if the payee has changed. If so, I go to all the records for that payee for the date range and create a pdf. When I'm done creating the pdf, I close the window, and go to the next record. If the payee hasn't changed, I reset my $payee.

[MParker]

ok...i have NO idea why it just will not work in my database when i do everything that you did in the sample one...but for some reason it won't. but i just used yours and it works like a charm, so i just moved everything back into the one you did and it works like a charm. thank you again, so very much. you're good!