1 2 Previous Next 16 Replies Latest reply on May 12, 2014 3:31 PM by keywords

    Output portal records to one merge file for MS Word

    ynot.nodrof

      Hello,

       

      this is my first post so be gentle!

       

      I have been using Filemaker since it was Claris Works and thats a Looooooong time!

       

      I have been scratching my head trying to export to a mail merge using fields from Table 1, only one record at a time which works OK no problem. But I am only able to get the first record from the related Table 2 portal in the Table1 layout, the portal is one field only and a maximum of eight records, to show in the merge.

       

      I have tried a repeating field using the same relationship but that does not work.. If I enter the data from Table 2 manually using a related pop-up into a repeating field on the Table 1 that will show in the merge file but as a string not line by line as required and is too messy to do it that way.

       

      Any suggestions would be much appreciated.

       

      TonyG

        • 1. Re: Output portal records to one merge file for MS Word
          beverly

          Tony, test an "export" from two perspectives:

          Parent

          Child

          using the same fields. Export as csv (or mer to get the field names as first row). Open in Excel and notice what values you get.

           

          You may need to do this from the child perspective. 

           

          -- sent from my iPhone4 --

          Beverly Voth

          --

          • 2. Re: Output portal records to one merge file for MS Word
            ynot.nodrof

            Hello Beverley,

             

            thanks for the rapid response!

             

            Both ways  the data is showing as separate records which is not what I want ..

             

            Name,Manufacturer,auditable site,Inspector2014,Certificate

            "Z Pty Ltd","E S.p.A."," Bovezzo (BS) 25073","","W220"

            "","","","","W222"(portal)

            "","","","","W223" (portal)

            "","","","","WM221" (portal)

            "","","","","W110" (portal)

             

            so only the first record in the portal shows in the merge.

             

            any way to work around this that not too fiddly.

            tanks

            TonyG

            • 3. Re: Output portal records to one merge file for MS Word
              erolst

              Do this in a context where you have as many records as you want to export, and where there is only one related record you need data from (assuming your two tables are in a one-to-many / parent-child structure).

               

              This means: go to the related records from the child table table2 (do your tables have proper names, btw?) and combine fields from table2 and related fields from (the only related record in the parent) table1 in your export.

               

              This is the same principle by which you base an invoice layout on the line items table, not the invoices table.

              • 4. Re: Output portal records to one merge file for MS Word
                ynot.nodrof

                I only want export one record at a time from the Parent Table 1 which has between one and eight related records in the portal (one field). Irrespective of which way i do this The exported data shows multiple rows in XL. and l can only get the first child record to merge to the single word document!

                 

                I have always gone straight to print from filemaker layouts in the past and never tried merging exported data previously but it is unavoidable in this case. 

                 

                Not so easy then!

                 

                thanks

                 

                PS the tables do have "proper" names!

                • 5. Re: Output portal records to one merge file for MS Word
                  erolst

                  ynot.nodrof wrote:

                  Not so easy then!

                  Don't say that!

                   

                  But I'm a bit lost at what you want:

                   

                  For each record in the parent table, create

                  • one export file with the data from all child records? – or

                  • create one export file for each child record?

                   

                  And that is where it would be helpful to know what your tables represent – i.e. their real names – because that makes it easier to understand what it is you're after.

                   

                  So, please provide some sample data of a parent record, the child records and the desired output, and I'm sure we'll find a solution

                  • 6. Re: Output portal records to one merge file for MS Word
                    beverly

                    Have you an example of what the result needs to be?

                     

                    Merging needs distinct placeholders, correct? So what placeholders and placement do you expect?

                     

                    Yes there are variable # of portal rows. Do these need to be in a table format in someway? Does word have the ability to deal with this kind of data (repeating rows)?

                     

                    -- sent from my iPhone4 --

                    Beverly Voth

                    --

                    • 7. Re: Output portal records to one merge file for MS Word
                      keywords

                      This sounds to me like a looping script would do the trick. I imagine the script would do something like:

                       

                      1.     Set Variable – collect all the data you want to include from the parent record

                      2.     Go to the first portal row – append the data you want from this row to the above variable

                      3.     Loop thrugh the rest of the portal rows repeating the above on each row

                      4.     Export the contents of the variable

                       

                      I've attached a demo file which does the above.

                      • 8. Re: Output portal records to one merge file for MS Word
                        ynot.nodrof

                        You are correct .. the word doc does need distinct place holders but it does appear that word cannot handle repeating rows and just prints the first record because thats the way it exported . it does however print the other records on separate documents.

                         

                        Place holders:                                        Output in word

                        «Name»                                                 E & Co

                        «Manufacturer»                                       B & Co

                        «auditable_site»                                     Glasgow

                        «Inspector2014»                                     A.Non

                        «Licence_1» (portal field)                         W234 (first record only)

                         

                         

                        Using a repeating field instead of portal I can get the output as a string e.g.  W234W235W236W237 etc.

                        but this is not acceptable as the user would need to use return to get them in the correct place on the document.

                         

                        I thought there might be some way of identifying each portal record field individual e.g.  Licence_1_1, Licence_1_2  etc so that a corresponding placeholder can be added in word?

                         

                        Same thing happens with the script  kindly shared by Keywords.

                         

                        thanks all for your input.

                        Tony

                        • 9. Re: Output portal records to one merge file for MS Word
                          beverly

                          You are still not specific enough in the output of the repeating (portal) values.

                           

                          Do you have more than one column/field? If not, the List() function can be used to push the values of a single column in the portal  to one field in the parent. This would append the return character to each value. Your list would be variable based on the # of portal rows per parent record.

                           

                          If you need something else, please post an example!!

                           

                          -- sent from my iPhone4 --

                          Beverly Voth

                          --

                          • 10. Re: Output portal records to one merge file for MS Word
                            erolst

                            Well, as Beverly says, please be as specific and detailed as you can; listing all the methods that don't work and the undesired output it generates is distracting, not productive.

                             

                            A description like

                             

                              • parent table <realName>> with fields <<parentTableFieldNames>>

                              • child table <<realName>> with field(s) <<childTableFieldName(s)>>

                             

                              The goal is to output a mail merge file for a parent record that, given sample data like

                              <<sampleData>>, 

                              has the following contents:

                              <<desiredOutput>>

                             

                            would probably let us find a quick solution.

                            • 11. Re: Output portal records to one merge file for MS Word
                              ynot.nodrof

                              Well, as Beverly says, please be as specific and detailed as you can;

                               

                               

                              So sorry .. I thought I was being specific and detailed and by listing what doesnt't work should indicate to you that I have tried that route and you as the expert need not consider it  any further ..

                              I have given the desired output as below and stated that it is one parent record with up to eight child records in a portal

                               

                              Place holders (corresponding to Fieldnames):                Output obtained in word

                              «Name»                                                                         E & Co

                              «Manufacturer»                                                               B & Co

                              «auditable_site»                                                              Glasgow

                              «Inspector2014»                                                              A.Non

                              «Licence_1» (portal field)                                                 W234 (first record only)

                               

                              I am afraid I don't know how much clearer I could be unless you want to come around and have a look your self.

                               

                              Thankyou Beverley I will try the List() function !

                              • 12. Re: Output portal records to one merge file for MS Word
                                erolst

                                Well, what I really wanted to see is not the output in the Word document, but the contents of the .text file that holds the merge data – I think …

                                 

                                See if the attached helps you, or at least may serve as a foundation for something like "That's almost it; if we could just <here> a bit, and maybe over <there>…” …

                                • 13. Re: Output portal records to one merge file for MS Word
                                  beverly

                                  Create a field in parent "license" and make it calculated:

                                   

                                  List ( )

                                   

                                  That's your last placeholder:

                                   

                                  «license»

                                  Hopefully Word will get the list idea.

                                  -- sent from my iPhone4 --

                                  Beverly Voth

                                  --

                                  • 14. Re: Output portal records to one merge file for MS Word
                                    keywords

                                    I remain somewhat mystified as to why you wish to export placeholders to Word instead of the text itself; that seems like doubling up. I would have thought that the data lives in your FM database and the script performs the "placeholder" function internally so that the data exported is correct for the specific parent record—and its children—you have active.

                                     

                                    The script I have already supplied (and the one erolst supplied, which is smarter than mine) does just this. It seems needless to my mind to export placeholders to Word so that it can extract the data, when you could just supply Word with the data. Or am I missing something?

                                    1 2 Previous Next