10 Replies Latest reply on Jan 26, 2017 12:20 PM by philmodjunk

    Move Text with Merge Fields from one place to another?

    steveald

      XPOST - I have to create many layouts in our main database using static formatted text mixed with merge fields. Attached is an example of what three sections (or paragraphs) of this text might look like. The dollar amounts are merge fields which display data that is calculated based on selections made elsewhere within each record. I use merge fields to make the surrounding text move based on how many characters are in the merge field data.

       

      Normally, I create each layout knowing exactly which sections I want to appear on that page. If there needs to be a handful of combinations of sections, I just create a layout for each one. But I now have a situation where any number of sections (usually between 1 and 8) will need to appear on a page. And there are 32 sections to select from - each with merge field data unique to the record they are found in. Obviously, creating one layout for each possible combination of sections is not realistic.

       

      So, I thought it would be nice to be able to create a layout where I could drag each required section to it's own place on the layout. Drag and drop between container fields won't work because we are talking about text with merge fields containing data that needs to change based on selections made elsewhere. And I think manually moving text between fields could too easily open up the possibility of errors.

       

      If it helps, the merge fields (and possibly even the static text) could also be displayed as Edit boxes or something else.

       

      Thoughts?

       

      58754416d3af0_ScreenShot2017-01-10at3_26_08PM.jpg.7763bdb4046540ff1d3e342d929cc86e.jpg

        • 1. Re: Move Text with Merge Fields from one place to another?
          philmodjunk

          I have to create many layouts in our main database using static formatted text mixed with merge fields.

          This is your first mistake. While this works and can produce very nice layouts, every time a user wants to change the text, it takes a developer to correct. This is then what I call a "brittle" layout as user driven changes will break the layout and require a developer to "fix".

           

          I suggest that you replace that layout text with A text field and a calculation field. The contents of the text field would resemble your current block of merge text. The calculation field uses the substitute function to replace the merge field markers with data from your database. This merges the data just like your merge text, but now the basic message can be edited by a user that is not necessarily skilled as a FileMaker developer.

           

          For your particular problem, the above approach allows you to put such text into records and you can mix and match which records are referenced on a layout in order to allow you to mix and match blocks of text with merged data.

          • 2. Re: Move Text with Merge Fields from one place to another?
            steveald

            Thanks for the reply. Let's see if I understand your suggestions as they apply to my circumstances. I'll try to be as succinct as possible while still providing sufficient detail. I realize my current process isn't the most efficient, but I haven't found a better method that does all I need. Hopefully, I learn a new process from this.

            One important detail is how we use records. Each record in our database represents a single client. Every client can potentially need a document printed from any of the layouts I create. Which means the details seen on the same layouts in each record will likely be different from every other record based on the selections made for that client.

             

            Thanks to strict government filing regulations, I have to be very careful about the text that gets placed on these layouts to be printed. So, I leave as little control to my end-users as possible. They don't have access to Layout Mode and they don't need to be able to enter any text on their own.

             

            For most layouts, my end-users can make selections on other screens via radio buttons or drop-down menus which result in specific data appearing in merge fields on layouts which are otherwise static text. A script-driven button takes them to the appropriate layout. They can then print the layout confident that what it says is correct and compliant. That is because, usually, the subject of the layout is one product. Where variations apply, a few unique layouts are created and the script-driven button controls which layout the end-user sees.

             

            A more complicated scenario I dealt with involved a dozen products, broken down into two groups - any combination of which would need to appear on a layout. So, I created layouts for each possible combination - 1 from the first group and 1 from the second group through 6 from each group. And I put calculation fields like I think you're suggesting on each layout based on those combinations. One calculation field would look to see if the first product in one group should be shown. If so, the calculation was done and the text for the first product would appear in the field. If not, it checked the next product. Calculation fields were created for the product header, body and dollar values in each case since the text formatting for each differed.

             

            My current scenario involves 3 dozen products which can be shown in any combination and any quantity. Obviously, the above method could be done, but the amount of work involved would be huge. Hence this topic.

             

            Does that help you better understand specifically what I need?

            • 3. Re: Move Text with Merge Fields from one place to another?
              philmodjunk

              While you may not be able to allow users to edit this text. Using a table with a record for each block of merge text using this type of Text field plus calculation field using Substitute setup will still enable your to do the Mix and Match that you have requested.

              • 4. Re: Move Text with Merge Fields from one place to another?
                siplus

                I immagine:

                 

                - a table of sections having 32 records as per your post;

                - a table of lettertypes which lists the sections to be taken and in which order;

                - a table of letters having just one big text block.

                 

                Each section record contains the text, with placeholders at key positions.

                 

                When you create a new letter of type "B" for a patient, you see that you need blocks 12, 4, 7 and 1 in this order so you fetch them, replace placeholders of type <<patientFullName>> existing in the sections' text with patient/current data and nicely add them to the big text block together with a "¶¶" at the end.

                 

                On the print layout you have a calculation defined as BigTextBlockCalc, = big text block. You can also set tabs for it. Being a calc it can't be modified.

                 

                You only have 1 visible layout in the Letters database and a popover on the patient layout, listing all available letter types in a portal. When you pick one, a new letter for that specific patient will be generated in the letters table. Quite simple.

                • 5. Re: Move Text with Merge Fields from one place to another?
                  steveald

                  Wow. I'll look over both of your suggestions and see if I can figure out what I need to do. Thanks to both of you.

                  • 6. Re: Move Text with Merge Fields from one place to another?
                    philmodjunk

                    siplus is expanding on the same idea. Not only can you set tab stops on the big block of text field, you can set indents to format the text as well. These are all settings that you can investigate in the Inspector's Appearance tab in the "Paragraph" and "tabs" sections.

                    • 7. Re: Move Text with Merge Fields from one place to another?
                      steveald

                      I'm not finding this anywhere. Does the Substitute function allow the use of a wildcard so that whatever happens to be in the field gets replaced by the replaceString?

                      • 8. Re: Move Text with Merge Fields from one place to another?
                        siplus

                        sure.

                         

                        Substitute(.....

                         

                          Substitute(

                         

                             Substitute (sectiontext; <<FirstName>>; Patient::FirstName);   // <-- the first substitute

                         

                          <<LastName>>; Patient::LastName)

                         

                        ...)

                        • 9. Re: Move Text with Merge Fields from one place to another?
                          steveald

                          I was thinking something along the lines of:

                           

                          Case (

                          Product 1 > 0 ; Substitute ( Field 1 ; "*" ; "This is the description for Product 1" ) ;

                          Product 2 > 0 ; Substitute ( Field 1 ; "*" ; "This is the description for Product 2" ) ;

                          Product 3 > 0 ; Substitute ( Field 1 ; "*" ; "This is the description for Product 3" ) ;

                          Product 4 > 0 ; Substitute ( Field 1 ; "*" ; "This is the description for Product 4" ) ;

                          "" )

                           

                          If a User first selects Product 1, then Field 1 would populate with the first line. But, what if, later on, they deselect Product 1 and select Product 4. Somehow the calculation would need to know it should replace whatever is in the field at the time with the new text for Product 4. I assumed that might call for a wildcard.

                           

                          Would the Replace function be a better choice in this case?

                          • 10. Re: Move Text with Merge Fields from one place to another?
                            philmodjunk

                            Substitute ( textField ; [ "<<placeholder1>>" ; replace expression 1 here ] ; [ "<<placeholder2>>" ; replace expression 2 here ] )

                             

                            You can use as many bracketed pairs of quoted text and replace expressions as you need.

                             

                            A "replace expression" can be any expression that produces the text you want to see inserted into your text. It can be a field or variable to name just two possibilities.