1 2 Previous Next 17 Replies Latest reply on Sep 2, 2012 4:42 PM by beverly

    Comma separeted list in a field?

    kallehval

      Hi FileMaker ninjas

       

      Is it possible to make a comma seperated list in a field based on another field.

      This is for a songbook, where I need to list the writer, and a commasepareted list of all the songnumbers he has written.

      Also... between the 2 last entries in the list, there should be an "&"

      ehm... also... they should be sorted nummeric

       

      Example records

      Field 1 - Field 2

      Peter Jackson - 452

      Tom Sawyer - 987

      Peter Jackson - 123

      Peter Jackseon - 985

      Peter Jackson - 98

       

      Result:

      Field 1 - New calculation field

      Peter jackson - 98, 123, 452 & 985

      Tom Sawyer - 987

       

      Can this be done?

       

      Thanks in advance...

       

      Kasper

        • 1. Re: Comma separeted list in a field?
          comment

          1. Do you also have a table of Writers (or People) where each person has one unique record?

           

          2. How many songbooks are you tracking?

          • 2. Re: Comma separeted list in a field?
            kallehval

            Hi Michael

             

            All data are in the same table. One field for the writer and one for the songnumber.

            This means, that in the above example, Peter Jackson has 4 records in this table.

            There are only 1 songbook.

             

            Thanks...

            • 3. Re: Comma separeted list in a field?
              comment

              kallehval wrote:

               

              All data are in the same table. One field for the writer and one for the songnumber.

               

              That's not the best arrangement. Each writer should be entered only once. Among other things, it would ensure their name is spelled consistently (see your own example, where Jackson also appears as Jackseon).

               

              I am also not sure where exactly do you want to put the result, once you have it. It wouldn't be too hard to have a script sort the records by name and by song number, then loop among them to collect the numbers for each group. But your result example shows a table with one record for each writer - so again...

              • 4. Re: Comma separeted list in a field?
                Malcolm

                Then you are making it harder than it should be. To do what you want you need to have a relationship between the writer and the songs.

                 

                A normal table structure may look like this: Writer >- -< Writer_Song >- -< Song

                 

                What that is trying to show is that there would be three tables. Writer, Song and Writer_Song.

                 

                The table called writer_song is a join table. It is needed because writer's often collaborate on songs, so there may be more than one writer per song and writers' usually write more than one song, so there will be many songs per writer.

                 

                Each table would be linked. You link writer to writer_song using the writer ID and you link song to writer_song using the song ID.

                When you have all that hooked up the thing you want to do is easy.

                 

                substitute ( list(Song::Song Number) ; ¶ ; ", ")

                • 5. Re: Comma separeted list in a field?
                  kallehval

                  Hi Malcolm

                   

                  Thanks for the good and easy to undestand instructions!

                   

                  I Have rearranged my db to your suggestion and it works just fine. To spice it up a little, I still need the 2 last things I wrote in my first post.

                  1. Is it possible to put in an "&" between the last two numbers like this  23, 54, 65, 76 & 89 (this should only be the case if there is more than 1 number offcause)
                  2. Can I sort the order nummeric, with the smallest number first?

                   

                  Thanks in advance  - you have been a great help!

                   

                  Kasper

                  • 6. Re: Comma separeted list in a field?
                    comment

                    1. Try =

                     

                    Let ( [

                    listOfValues = List ( SongWriters::SongNumber ) ; 

                    lastCR = Position (  listOfValues ; ¶ ; Length ( listOfValues ) ; - 1 ) ;

                    temp = Case ( lastCR ; Replace ( listOfValues ; lastCR ; 1 ; " & " ) ; listOfValues )

                    ] ;

                    Substitute ( temp ; ¶ ; ", " )

                    )

                     

                     

                     

                    2. The List() function returns items in the sort order defined for the relationship.

                    • 7. Re: Comma separeted list in a field?
                      kallehval

                      Uhh, nice. This works like a charm (even though this is a little bit over my level).

                       

                      I also need a songnumber list where the same person is co-writing. This is no problem, I have made it from your above descibtion using another relationship.

                      But... the last list i need is a combined numberlist of the 2 lists and in the correct order of numbers starting with the lowest.

                       

                      I have removed your above calc from the field an just used the list(Song::Song Number) on both lists.

                      This leaves me with 2 fields with numbers in them. I have then made a 3 field that combines the two with a simple writer & cowriter calculation, which works fine, but now they are not sorted offcause.

                       

                      Can the list be sorted in the calculation somehow or does it have to be via the relationship?

                       

                      Does this make any sense???

                      Thanks...

                      • 8. Re: Comma separeted list in a field?
                        kallehval

                        Just to specify:

                         

                        In writer list I have:

                         

                        2

                        3

                        4

                        7

                         

                        In CoWriter list I have:

                         

                        1

                        5

                         

                        In the third field I need:

                         

                        1, 2, 3, 4, 5 & 7

                         

                        Thanks... Kasper

                        • 9. Re: Comma separeted list in a field?
                          comment

                          I am afraid I got lost somewhere along the way. In general, using Filemaker's native sort mechanism (such as sorting the relationship or the found set) is much more efficient than any custom routine you can come up with - and custom sorting routines are not at all easy to come up with...

                           

                          That said, if you have a sorted list of all song numbers - and this could be produced by defining a value list and then using the ValueListItems() function - you could then use the FilterValues() function to produce your desired result, e.g. =

                           

                          FilterValues ( ValueListItems ( Get ( FileName ) ; "SongNumbers" ) ; WriterList & ¶ & CoWriterList )

                          • 10. Re: Comma separeted list in a field?
                            beverly

                            I was thinking along the lines of Michael's calc example and supplying both writers and co-writers:

                            ===

                            Let ( [

                             

                            // listOfValues = List ( Songs::Song Number ) ;

                            listOfValues = List ( Writers::Song Number; CoWriters::Song Number ) ;

                             

                            lastCR = Position (  listOfValues ; ¶ ; Length ( listOfValues ) ; - 1 ) ;

                            temp = Case ( lastCR ; Replace ( listOfValues ; lastCR ; 1 ; " & " ) ; listOfValues )

                            ] ;

                            Substitute ( temp ; ¶ ; ", " )

                            )

                            ===

                             

                            Does that work?

                            Beverly

                            • 11. Re: Comma separeted list in a field?
                              kallehval

                              Hi Beverly

                              Thanks for the input

                               

                              It does not exactly work as I hoped for. It does put the 2 lists together, but they look lige this (if I use the example from above)

                               

                              2, 3, 4 & 7 1 & 5

                               

                              The formatting gets on both lists.

                               

                              The end product for this job is a book, so for now I have made a new database, where I imort from the other one. This way I have gotten something that is working. But the elegant solution would be to have it all in the same DB.

                               

                              Anyway - thanks for trying!

                               

                              Kasper

                              • 12. Re: Comma separeted list in a field?
                                comment

                                How about something simple?

                                • 13. Re: Comma separeted list in a field?
                                  beverly

                                  Please post your exact calculations, kallehval. You should NOT be applying any of the "," or "&" until AFTER you combine the pure lists from each relationship. This should be a new calculation and in no way a combination of the other two calculations you needed separately.

                                   

                                  Beverly

                                  • 14. Re: Comma separeted list in a field?
                                    comment

                                    Beverly,

                                     

                                    Kasper's observation is correct: the concatenated list needs to be resorted.

                                    1 2 Previous Next