7 Replies Latest reply on Aug 7, 2012 6:13 AM by philmodjunk

    Concatenating unique values



      Concatenating unique values



      I have a table with lists of categories in it, stored along with the ID of a book title that they belong to. If I do a search for a book ID, I might get, say, three records with the category list from each being:


      What I would like to do is take those three values and create a single string with 172,173,176,177,178,179 in it.

      It's not super critical that the values be in numerical order but it is important that there be no repeats. So I need to be able to ignore the 172,173 from the second record and the 172 from the third record.

      I have no idea how to do this and would appreciate any help that could be offered.

      I wondered about creating a custom function but, to be honest, I don't know how to do that either so maybe I can just do it in a script.

      So far, this section of script is:

      Go to Layout [ “CategoryTitleLink” (CategoryTitleLink) ]
      Enter Find Mode [ ]
      Set Field [ CategoryTitleLink::_fkTitle; $TitleID ]
      Perform Find [ ]
      If [ Get(FoundCount) > 0 ]
      Go to Record/Request/Page [ First ]
      Go to Record/Request/Page[ Next; Exit after last ]
      End Loop
      I need something to replace the ???s
      Thanks in advance,


        • 1. Re: Concatenating unique values

          How is the data entered into the category list for each record?

          If you can get it into a return separated list, you might be able to set up a conditional value list that can in turn be used to produce the new list of values that would be in ascending order and minus any duplications. This assumes using the book ID to define the relationship needed for the category list.

          Tell me more about your database design.

          Do you have something like this:

          TableA::BookID = TableB::BookID

          And in your example, you have three records in TableB with the same book ID. And you want the category list to be in a field in the one record in TableA with that same bookID?

          • 2. Re: Concatenating unique values

            I have a table of book information called Titles. There is a separate table of category information called Categories. There is a third, link table called CategoryTitleLink which has three fields:


            The user selects the categories they want the book to be in from a list which creates a record in the link table for each category selected.

            I am now trying to export the book data and need to amalgamate the chosen category lists into a single, comma-separated field in the export record. For this purpose, I have created another table in which I plan to build the records for export. The standard Filemaker export leaves loads of blank fields when there are several related records in a related table and the guy I'm sending the data to has ruled that out of order.

            My aim, then is to read the Title data (and also the authors and any reviews, which are in other tables, but which I've figured out already) and the categories from the various tables that I set up to store it and create a (temporary) table with complete records which I can then export directly using a standard FM export.

            Hope that helps,

            • 3. Re: Concatenating unique values

              Not completely. Your first post mentions a "bookId" and your second shows a "titleID". I'm guessing that these are not the same thing, but that you want to set up your "export table" to provide one record for each bookID, not TitleID.

              Is this correct or does BookID = TitleID?

              Either way, a simple combination of List and substitute can produce the list that you want and by referring to the Category table, instead of the link table will eliminate the duplicates.

              • 4. Re: Concatenating unique values

                The Book Id is the same as the TitleID - apologies for the imprecision.

                I don't see how the category table will help, sorry. It contains the text that goes with each category, a category ID and the string of comma-separated categories that translate the text into something usable by the export.

                So category ID 1 might be History/Mediterranean/Roman and category 2 might be History/Mediterranean/Italian (I'm making this up at this point, but perhaps you get the idea). Anyway, we've previously agreed that History is 100, Mediterranean is 101, Roman is 102 and Italian is 103.

                What the guy getting the export wants is 100,101,102,103. I can easily give him 100,101,102,100,102,103 but he needs to lose the duplicates.

                Any better?

                • 5. Re: Concatenating unique values

                  Each record in the Category table should list only one Category. The purpose of a link table should be to link a given record in title to a single category, not a group of categories. What you describe is an unecessary complication that should be avoided.

                  Restructuring your data is a long term solution. Here's a short term fix.

                  Define this calculation field, cCatList in the Categories table: Subsitute ( CategoryList ; ", " ; ¶ )

                  Define a value list to list all values of cCatList from the Categories table.

                  Specify "include only related values" and select Titles as the "starting from" table.

                  Then this calculation will list your categories, separated by commas, and with the duplicate values omitted:

                  Substitute ( valueListItems ( Get ( FileName ) ; "NewValueListNameInQuotesHere" ) ; ¶ ; ", " )

                  • 6. Re: Concatenating unique values

                    I take your point about the single category but the way I was looking at it was that the string of (up to) three numbers is a single category - it's almost like a text field, which just happens to contain a string of comma-separated numbers. We'd decided that (from my example above) History would be 100, Mediterranean would be 101, Roman would be 102 etc, Then when the exported file was processed, the long string would be used to display the book under various headings.

                    I guess I could change it so that when the user selected a category it created up to three entries in the link table instead. Probably it's possible to set up a composite key and define it as unique so that the second addition wouldn't add anything that already existed. Hadn't thought of that.

                    Thanks, as always,


                    • 7. Re: Concatenating unique values

                      it's possible to set up a composite key and define it as unique

                      Yep, use an auto-entered calculation to combine the values with a separator character and specify unique values as a validation rule.

                      There are also other options for preventing duplicates:  https://www.dropbox.com/s/oyir7cs0yxmbn6i/ManyToManywDemoWExtras.fp7