10 Replies Latest reply on Dec 3, 2014 3:34 PM by philmodjunk

    Combining a text field

    PaulKatz

      Title

      Combining a text field

      Post

      Is there a and easy way to take a text field;   and  sum the records or combine the field into one larger text field?

      example: record 1 (TEXT_a) + record 2 (TEXT_a) + record 3 (TEXT_a) = TEXT_b

      if possible separating them by a line.

       

        • 1. Re: Combining a text field
          SteveMartino

          Can you be more descriptive?  Do you mean sum the records or combine the fields?  And how does "...if possible separating them by a line."  fit in? If you give an example of the data and the result you expect to see, someone will easily help you.

          • 2. Re: Combining a text field
            philmodjunk

            List, The "list of" summary field introduced in FileMaker 13 and ExecuteSQL (a function introduced in FileMaker 12) can all produce return separated lists of values. Each can be looked up in FileMaker Help and there's a resource PDF available from FileMaker on SQL queries used in ExecuteSQL.

            • 3. Re: Combining a text field
              PaulKatz

              record #1 (this is the time for all good men)/ record #2 (I want to become Spartacus.)

              SUM (this is the time for all good men I want to become Spartacus)

               

              • 4. Re: Combining a text field
                philmodjunk

                the SUM of 1, 2, 3 is 6, not "123", getting a result of "123" would be the Concatenation of the values 1, 2 and3. That's part of the confusion here.

                But from your first post, don't you really want to produce:

                this is the time for all good men
                I want to become Spartacus

                Isn't that what you meant by:

                if possible separating them by a line.

                ???

                • 5. Re: Combining a text field
                  PaulKatz

                  Yes...  or even better

                  this is the time for all good men

                  (blank line)
                  I want to become Spartacus

                  • 6. Re: Combining a text field
                    philmodjunk

                    Then I refer you to my posted comment on List, ExecuteSQL and the "list of" summary field. All can produce this result. For List and the summary field, you can enclose that result in a substitute function in order to double space it. ExecuteSQL can specify two returns and the record separator to get that result.

                    But your post lacks context and that context is need before we can respond in more detail. It might even result in a suggested solution that does not need any calculation at all.

                    How will the user determine which records to combine and in what order? Depending on your data and what you want to do, you might perform a find, click buttons or check boxes to select, use a relationship.... to do that.

                    and how will use use this combined text once you have it? (In some cases a list view layout or a portal might produce the results you want without any need to use a calculation to combine the data.)

                    • 7. Re: Combining a text field
                      PaulKatz

                      Here is some context...   fields(DATE OF SERVICE)     (SUMMARY OF WORK COMPLETED)/ I want to be able to view all the (SUMMARY OF WORK COMPLETED) combined, so it appears as one paragraph after another.

                      • 8. Re: Combining a text field
                        philmodjunk

                        Which leaves this key question unanswered:

                        How will the user determine which records to combine and in what order?

                        Is there a parent table where a single record is linked in a relationship to all of the Summary of Work records you want to concatenate in this fashion?

                        If not, is there some common value in a field or group of fields for all records that you want to combine like this?

                        And it leaves this question unanswered:

                        and how will use use this combined text once you have it?

                        In many cases, such as printing or producing a PDF, there's no need for such a calculation as the layout can be designed to list the data in this format.

                        • 9. Re: Combining a text field
                          PaulKatz

                          Yes there is a parent table/fields: (FIRST)( LAST) (UNIQ#)   child table fields:/ (DATE  OF SERVICE)   (SUMMARY OF WORK COMPLETED)   (UNIQ#)

                           

                          I want to be able to combine all the "notes" from the field SUMMARY OF WORK COMPLETED into ONE document that could be a printed summary of all the NOTES taken from the field (SUMMARY OF WORK COMPLETED)

                          • 10. Re: Combining a text field
                            philmodjunk

                            And that does not require any calculation at all to combine the data in this fashion. I'll provide that method just in case details you have not yet shared make it necessary at the end of this post.

                            You can set up a List View layout based on the child table and include fields from the parent table. You perform a find (manually or in a script) for all summary of work records for a given Unique ID or you perform a Go to related records from a layout based on the parent table to pull up the found set of records on your List View layout that you need to print such a report. And this type of layout can also be set up to list multiple parent records, each in it's own sub summary layout part with the related summary of work records grouped below it.

                            One simple calculation field that you can use to concatenate this info is to define the following in a field in the Parent table:

                            Substitute ( List ( ChildTable::Summary of Work Completed ) ; ¶ ; "¶¶" )

                            The substitute function takes the single return and replaces it with two to double space between blocks of text.