Sending datas in a pre-formatted Word document

[Charled]

Hello everyone,

I'm developing a little FMP database on Mac to help creating reports with MS Words. These reports will contain :

- non changing elements : logo and address, first level hierarchy titles…

- some specific datas from FMP : number and name of the affair, name and adress of the client…

- the text of the report directly typed in Word by the user.

So my need is :

- when all the fields are completed, user clics on "Create report" then :

- it creates a new folder with number and name of the affair,

- creates the report file by duplicating from the report template, naming it with number & name of the affair &.doc

- inserts datas from FMP (juste one record)

- add the file into a multimedia field to less user clic on it to open it.

All this by script of course for the user only clics on one button.

So what is the best way or what is the right plug-in for it ? I'm not familiar with Applescript nor VBA so I would prefer not have to use them.

Thanks all for your answers or suggestions.

[bcooney]

The only plugin I know that interacts with Word is 306work's Scribe. The OS ops, creating a folder, etc., can be accomplished with their ScriptMaster product.

[comment]

I'm not familiar with Applescript

Actually, Applescript may be your best option here - other than using a dedicated plugin (plus another plugin for the OS tasks).

What makes Word so attractive as an output format - compared to PDF, for example?

[Charity]

Or even RTF?

[comment]

RTF is not easily produced either - unlike PDF.

[bcooney]

It's definitely worth examining the requirement that the final product is a Word doc. Sometimes, that is a relic of an old workflow, similar to "we must have reports in Excel."

[Charled]

Hi everyone,

I'm back on this old topic. Thanks for your former help.

First, I answer the last question : why Word ? Because, after creating the document with FMP datas, users need to append it manually with Word.

Second. The situation now. As I couldn't manage to make Scribe work, I finally used EZXslt which did the job. But EZXslt is discontinued. So I would take advantage of making enhancements to the DB for replacing EZXslt by Scribe.

But, as before, my tests don't work. I tried to use ScribeDocSubstitute or ScribeDocWriteValue. But nothing is written in my Word document.

Can someone explain to me how I must prepare the document ? How I can add placeholders or what Scribe needs ?

I'm still using FMP 11 (.fmp7) ; Scribe 1_451 ; And Word 2011 Mac.

Thanks for help… again ;-)

[comment]

30 minutes ago, Charled said:

First, I answer the last question : why Word ? Because, after creating the document with FMP datas, users need to append it manually with Word.

Wow, I don't think I have ever had a question answered 6 years after asking it. Except it doesn't really explain why they can't add their input from within Filemaker and then produce the final document from there.

Anyway, I cannot answer your questions regarding Scribe, so good luck with that.

 

 

Edited August 3, 2017 by comment

[Charled]

Hi, sorry for being so long. I think I just didn't see your answer…

We are talking about expert reports which are all redacted the same way with same structure and 3 kind of datas :
- fixed informations (laws, blab bla…)
- datas about the case (who vs who, their adresses,  phone number, lawyers…) which come from the DB,
- text of the report itself (with photos etc) which must be written manually.

So I need to have some templates (.docx) with all the texte that don't change and the placeholders for case information. I duplicate the template, change the name, insert the datas in the placeholders. Then users can open it and append it manually.

If you know another way for sending datas into Word documents ? I tried with mail merge function but when I choose my database, Word says it can't connect to.

thx

[comment]

3 minutes ago, Charled said:

If you know another way for sending datas into Word documents ?

Actually, I do know a couple, though they may not be as easy to work with as EZXslt or Scribe claim to be (I haven't worked with either, so I can't say for sure). But you can use XML export, with a custom XSLT stylesheet,  to produce a document that Word can read. I presume you would want a fomatted document conforming to some template. The most convenient format for this would be the Microsoft Word 2003 XML format (a.k.a WordProcessingML or WordML), which is just an XML document that Word considers as one of its native formats.

The process can be something like this:

1. Create a template in Word, with some placeholders and save it as Microsoft Word 2003 XML;
2. Open it in a text editor and use it as a basis for your XSLT stylesheet;
3. Script your export to use the stylesheet.

Step 2 is manual and requires knowledge of the XSLT language. And you will need to repeat it any time you want to modify your template. But once it's done, it's just a matter of pressing a button and getting your document - just like exporting in one of the built-in formats.

It's also possible to use a similar process to produce a .docx document - but the format is more complicated, and you would also need a plugin to zip the result (I believe the free Base Element plugin could do this even in v.11).

And of course there 's AppleScript/VBA as you mentioned in the opening post.

 

[Charled]

Ok. Thanks.

How do I add placeholders in Word document (I'm on Mac with Word 2011) ?

Edited August 14, 2017 by Charled

[comment]

There are no placeholders as such - use any text you can easily recognize, for example $$$CLIENTNAME$$$.

[MikeKD]

Hi Charled,

Please take this answer with a HUGE pinch of salt!  I'm only a couple of baby steps beyond being a beginner....

I /think/ what I'd do here is create some kind of text string (one that's not likely to come up anywhere else) in word that I could then do a search / replace on, e.g:

////..Title..////

////..Date..////

////..Content..////

Oh boy - I've just seen comment's answer and I'm right!! Yay!

So then, you use substitute in FM to replace each of the text strings you've put in your template.

This is potentially /so/ useful!!!...

Thanks both :-)

 

 

[comment]

15 minutes ago, MikeKD said:

I've just seen comment's answer and I'm right!! Yay!

You are right up to this point:

15 minutes ago, MikeKD said:

So then, you use substitute in FM to replace each of the text strings you've put in your template.

That's very limited. What if you have a placeholder for an item, but you want the final document to list 20 items (or any number of items in the current found set)? 

 

[MikeKD]

Quote

That's very limited. What if you have a placeholder for an item, but you want the final document to list 20 items (or any number of items in the current found set)? 

mmm, *******, good point!

I'm showing my incompetence again..

My guess is that I should be looking at a calculation (or summary?) field listing either found or related records?

Once we've got the files text content correct, are there different methods for windows and Mac to set the filetype to .doc? Or is that part of the text content?

As always - huge thanks for the help you give to those of us learning the ropes comment :-)

[comment]

6 minutes ago, MikeKD said:

My guess is that I should be looking at a calculation (or summary?) field listing either found or related records?

No the idea is to export the records and let the XSLT stylesheet do the hard work. I guess this may be a bit difficult to understand if you've never used XML/XSLT export. It's one of the most powerful and at the same time under-utilized FM features. 

 

[MikeKD]

I've just had a quick google of it.

I think it needs rather more than a quick google! I think I probably need to learn how to use one tool at a time at the moment!!

[comment]

1 minute ago, MikeKD said:

I think it needs rather more than a quick google!

It's true that XSLT is a world of its own. However, you only need a very limited knowledge of it in order to manipulate a Filemaker XML export into something useful.  There used be some basic examples included with the application, but I see that's no longer true. Still, there are quite a few examples here on FM Forums you can use for learning.

[ryan360Works]

Charled,

I think your best option is to use ScribeDocSubstitute instead of ScribeDocWriteValue. With Word documents, ScribeDocWriteValue requires your template to have named content control fields which can be a bit of a hassle to set up and I have only ever found the option to make them in the Windows version of Word. Example for how to use ScribeDocSubstitute is as follows:

Example is a form letter that might be sent out to a customer:

Quote

 

Hello CUSTOMERNAME,

We have found that your account, ACCOUNTNUMNER , is past due. Please pay ACCOUNTBALANCE by DUEDATE or your account will be canceled.

 

Here the placeholders are CUSTOMERNAME, ACCOUNTNUMBER, ACCOUNTBALANCE, and DUEDATE. In your FileMaker script you will load this document into memory using ScribeDocLoad. You will then call ScribeDocSubstitute for each placeholder Ex. ScribeDocSubstitute("CUSTOMERNAME";"John Smith")  and then save the file either with ScribeDocSaveContainer or ScribeDocSaveFile. 
 

[Charled]

Thanks Ryan360Works.

I tried your solution with a little database with only 3 fields but it didn't work. I don't know why. Can I use fields name in ScribeDocSubstitue ? Ex : ScribeDocSubstitute("CUSTOMERNAME";Table::CustomerName)

[ryan360Works]

Yes you can use field references as the second parameter. It does not need to be hard-coded. 

[Charled]

ok.

What do you mean by "It does not need to be hard-coded" ?

[ryan360Works]

By hard-coded I mean the parameter is not dynamic. IE ScribeDocSubstitute("CUSTOMERNAME";"John Smith") will always substitute John Smith in the place of CUSTOMERNAME regardless of what record you are on. However, ScribeDocSubstitute("CUSTOMERNAME"; Customer::CustomerName) will write whatever value is in CustomerName field for the current record. Have you tried using our example file? Do you see the same results that you saw in your own files?

[Charled]

ok. thx.

[Charled]

OK. This plugin is driving me crazy !!!!!

My configuration is FMP Advanced 11 on Mac with Scribe 1.451.

For testing, I've created the most simple DB : one table, a field name (text), a field lastname (text) and a container for the Word file.
My Word file is called TestScribe.docx and contains a %%%Nom%%% string (like in the example).
I copy/pasted the example script. When I run it, ScribeDocLoad finds the file (no error) but ScribeDocSubstitute answers : "No occurence of '%%%Nom%%%' were found in the document. Try using a shorter placeholder string, as longer strinqs with spaces and other characters may not be recognizable due to extra metadata."

What's wrong ?? I'm just using the same placeholder string %%%xx%%% as in the example.

Could it be a text encoding pb ? I tried to modify it but in Word you need to convert file in plaintext, then choose the right encoding, then save in docx. I tried with mac (by default) Windows Dos and UTF8 but none works (if the encoding is saved when converting back to docx).

I cand send my test files to anyone wants to test them. Thanks.

[Charled]

Hello again,

I finally get the solution : the function to export to Word in Scribe didn't work because of a bug which was issued in v2. Now, I can use it.

Thanks.