Send Mail via PSOS with PDF Attached

Discussion created by MarcDolley on Jan 17, 2019
Latest reply on Jan 21, 2019 by eric

Before I begin, let me apologise for the lengthy tome. I’m posting this in the hope that it may save others some time. This is a follow-up to my post earlier this week relating to my ongoing battle with one of my clients IT people who refuse to allow us to send emails directly from FileMaker Pro Advanced. Insisting that they can only be sent from the server using PSOS. Considering the IT person is an arrogant, egotistical *expletive* who refuses to accept any sort of compromise. I’ve resigned myself to the fact that I can’t win that battle, so I’ve set about working out how to modify all my email scripts to use PSOS.

I’d like to especially thank Taylor Sharpe for his suggestion to use JSON elements and to SeedCode for their example file. The majority of the emails are just text. That caused some issues because I was originally sending the data to the server via a script parameter with the data in a return separated list of values. That worked for the most part, except when users decided to add extra carriage returns in the text which resulted in data being dropped off. That was easily fixed using JSON. That was the easy bit.

It became more difficult when it came to generating PDF invoices and attaching them to emails. The current FMP-based process runs like this (somewhat abbreviated):

The invoice is generated from one section of the database
It finds the relevant data and creates an invoice record
It then finds the line items for that invoice, imports them and links them to the invoice record
It switches to one of three possible invoice layouts (depending on customer type), sorts the data, does the print setup and produces a PDF
It then adds a record in the communications (comms) table where (among other things), the email addressee is set along with the subject (including invoice number) and the body
It then inserts the PDF (using Insert PDF) into a container field in the comms record
After it’s done all that, it sends the email using the data and PDF from the comms record.

All pretty straightforward really and all of that, except for sending the email, still needs to happen in FMP. So switching to sending the email via FMS rather than FMP meant I had two options. Due to various limitations of FMS when it comes to PSOS, the obvious choice was to pass the unique ID for the invoice record as a parameter and re-create the context in a server session. So the process would work like this on the server:

First up it would need to switch to a layout showing the invoice and do a perform find based on the invoice ID passed in the parameter
It would then have to go to the related line items
Then switch to the appropriate invoice layout
Then do the print setup
Then create another PDF in the documents folder on the server
It would then have to find the comms record relating to that invoice get the addressee, subject and body and finally
Send the email from the server with the newly created PDF

Before you say it, yes there may be better ways of doing this, but the fundamental process is still the same and it requires a lot of repetition. Doing things once on the local machine and then again on the server, seemed like a waste to me.

So armed with my new found knowledge of JSON, I set about looking for a better way and found it (although I suspect many will disagree with that assertion). Here’s my new method:

I still go through the same process in FMP until it gets to the send mail part
I now use the JSON Set Element function to compile the values from the comms record including the addressee, subject and body and the name of the PDF file in the container field
In addition (and this is where it may get contentious), the PDF itself using the function ‘Base64EncodeRFC ( 4648 ; COMMS::PDF Doc) ; JSONString)’
This all gets passed to the server script as a parameter.

On the server, the script sets individual variables extracted from the parameter
It then switches layouts to a temporary table with only a few fields and creates a new record
It fills in the file name and the Base64 text and then uses the Base64Decode function to convert the text back into a PDF in the container field
Knowing that the Export Field Contents step isn’t server compatible, I installed the BaseElements plugin on the server and used its export field contents function with the FMS documents folder path hard coded into the calculation
Then the email can be sent with all of the data in the original parameter and the newly created PDF
After the send mail step, the temporary record gets deleted (effectively a self-cleaning table)
The process then hands back to FMP so the user can continue. They generally send invoices out in a batch, so this will operate within a loop eventually.

After a few stumbles long the way thanks to incorrect step orders and spelling mistakes in variable names, it all seems to work perfectly. I emailed myself one of the invoices and it looks absolutely identical to the original. Considering I couldn’t even spell JSON three days ago and knowing I’ve barely scratched its surface, I’m pleased with the result. It reaffirms my old adage “Where there’s a need, there’s a workaround”.

The advantages of this method (in my opinion) are that I don’t have to re-create the context on the server and I don’t need to repeat so many of the steps already being done on the desktop. It’s also fully compatible with FM Go and WebDirect. It does leave a copy of the PDF on the server computer, but so do other processes and we’re going to set up a batch file on the server which periodically empties out that folder. I hope that this is useful for anyone who finds they need to send emails via PSOS with PDF attachments.