Need help with a script, pretty please

[Paula OConnor]

I have just upgraded to Filemaker Pro 9, mainly because of the "append to .pdf" feature. However, I can't seem to be able to figure out how to append the reports in the order I'd like. I have several tables, all with the PAYEE in common. One is a Cover Letter, one is a report on Product Sales, one is a report on Digital Sales, etc (some of the reports are multiple pages for each payee). How would I write a script that would sort them by PAYEE... Cover Letter, then Product report, then Digital report, then the next PAYEE, and so on, instead of just all of the cover letters, then all of the reports, one by one? I sure hope I'm making sense here! : I'd appreciate any help!

[Fitch]

You're going to have to do them one by one, but this is not hard to script: use a Loop/Go to next, and for each payee, GTRR (go to related) report of each type in a new window and append it. Will that work for you?

[Paula OConnor]

I'm not really familiar with scripts, so I'm having a hard time understanding exactly what you mean (especially about the loops), but I'm going to keep playing with it this morning. Thank you so much!

[bcooney]

This article by Geoff Coffey might help: Link

[Paula OConnor]

This is what I have now (which just lists them one report after the other)...

Go to Layout ["Cover Letter"]

Show All Records

Sort Records [Restore] (sorting by Payee)

Save Records as PDF

Go to Layout ["Sales Royalty Report by Payee"]

Show All Records

Sort Records [Restore] (sorting by Payee)

Save Records as PDF [Append]

and so on for each report...

How/where would I add a loop to that to make it group them all by Payee?

[David Jondreau]

Can you put the cover letter and all the reports into one layout?

Otherwise, you'll need to put the list of payees into a variable and then loop through that list finding each payee in each layout and appending.

[aldipalo]

Go to Layout ["Cover Letter"]

Show All Records

Sort Records [Restore] (sorting by Payee)

Go to record/request(first)

Loop

Save Records as PDF(append)

Go to record/request(Next);Exit after last

End loop

This is very down and dirty, but, should put you in the right direction.

[Fitch]

Assuming that all your report layouts use the same base Table Occurrence, your found set and sort order will be retained as you go from one layout to the next; no need to find and sort when you change layouts.*

So, starting on the first record, you go from layout to layout and append -- be sure you're only saving/appending PDF for the *current record* -- then move to the next record and do it again.

Go to Layout ["Cover Letter"]

Show All Records

Sort Records [Restore] (sorting by Payee)

Go to record/request(first)

Loop

  Save Records as PDF

  Go to Layout ["Sales Royalty Report by Payee"]

  Save Records as PDF [Append]

  Go to Layout ["Cover Letter"]

  Go to record/request(Next);Exit after last

End loop

*If your layouts don't use the same TO, then you could go to related in a new window as I mentioned above.

[Paula OConnor]

Thanks for taking the time to do that. I think my poor little script-deprived mind understands this one. However, I came across a major issue that I didn't think of before...

Each payee will not have all of the reports. They all have the same cover letter, but the rest varies. Therefore, without some kind of script telling it to match the reports with the cover letters by PAYEE, I don't think it'll work.

I really do thank you all for your help with this. I guess I just have to be satisfied with one report after the other! :

[Fitch]

I'm sure with a little thought it would be possible to accomplish your goal. It's up to you.

First I'd need to know a little more about what layouts and tables these reports are based on and how they are related.

[Paula OConnor]

I hate to keep bugging you about this, but since you offered... LOL

I work for a smallish music publisher & we are getting ready to start our annual royalties. Normally I print out all of the cover letters & reports separately & then manually match them up. This is exactly what I have:

1- A Cover Letter table with about 500 payees

2- A report (actually, it's a detailed statement) on CD Sales

3- A report on Digital Sales

4- A report on Additional Licensing, etc.

5- A report on Deductions

They will all receive a cover letter, but they do NOT all receive all of the reports. Some only have CD sales, only a few of them have deductions, etc.

The one field that they all have in common is the PAYEE NAME.

The cover letter and all of the reports are together in one database.

I think that about covers it. :

[Fitch]

How are your tables set up? For example, is CD Sales a related table? I ask because basically you just need a little bit of If/Then logic in your script, but we have to know what to test for, e.g.:

Instead of just saying...

Go to Layout ["Sales Royalty Report by Payee"]

 Save Records as PDF [Append]

...we might do this:

If( Sum(related::CD Sales) > 0 )

    Go to Layout ["Sales Royalty Report by Payee"]

    Save Records as PDF [Append]

 End If

Does that make sense? It's easy enough to perform different actions based on certain conditions, but first we have to figure out how to define those conditions.

PS: You're not bugging me. Yet. :

[Paula OConnor]

The main report is the cover letter, which includes the names and addresses of all of our royalty recipients. The cover letter is related to each of the other reports by the PAYEE field.

So I guess the "if" would have to deal with that PAYEE field? Each payee will have a cover letter, but the script would have to do something like this...

Say the 1st cover letter is for JOE BLOW... Then it would have to go to the CD Sales report, and if there's a JOE BLOW in that one, it would be placed after the cover letter... Then it would have to go the the next report (Digi sales) and look for JOE BLOW there. If there is no report for JOE BLOW in that one, it would have to move on the next report... And so on.

Like I said, some recipients will have all of the reports and some will just have a couple of them, but they will all have that cover letter.

Once all of the reports are checked, then it would go back to the cover letter again & start with the next PAYEE.

I hope that makes sense! :

[David Jondreau]

Are the reports (Sales, Digi, etc) based on the same table or different tables?

It's possible you can combine all your reports into one layout.

Edited October 26, 2008 by Guest

[Paula OConnor]

The reports are in the same database, but different tables and layouts.

[Fitch]

For each record/payee in the cover letter table, test to see if it has related records, and then go to the related records for that payee in a new window, append PDF, then close the window. LOL, that's exactly what my first response was -- but maybe it makes more sense to you now?

I showed you one way to test for related records in my last post. Another way is:

If( not IsEmpty( related::payee ID )

You say you have some kind of relationship between these tables already in place. Change the examples to reflect the actual names of your related fields.

[Paula OConnor]

Thanks, guys. I'm getting closer : Now I'm getting them in the correct order, but for some reason my sales report is not showing up correctly. The only thing I'm getting is the 1st line entry & then a grand total at the bottom. I'm sure it's something about the way my report is set up, but it looks correct in Filemaker & when I print it out.

Anyhow, I'm going to keep trying. Thanks so much for all the help!

[Fitch]

Make sure you've set the Save as PDF option to Record being Browsed.