10 Replies Latest reply on Jul 3, 2012 9:20 AM by philmodjunk

    how to concatenate with a calculation

    SarahStacilauskas

      Title

      how to concatenate with a calculation

      Post

      I am not a pro at filemaker but i need to take a text file and import it into filemaker. it has fields called StyleNumber and Page number. i need a formulat to find duplicate stylenumbers and merge them together and then concatenate the page numbers that coorespond to them. for example if i have a style number abc123 and its on page 133 and 144 it needs to ouput like this:

      abc123133, 144

       

      I am trying this calculation field. but i must be missing something becuase when i export to text file nothing works.

      Substitute ( ValueListItems ( Style Number ; Page Number ); "¶"; ",")

      Screen_Shot_2012-07-03_at_9.13.48_AM.png

        • 1. Re: how to concatenate with a calculation
          philmodjunk

          Substitute ( List ( Style Number ; Page Number ); "¶"; ", ") //note added space after comma

          Should work, but so would"

          StyleNumber & ", " & PageNumber

          The only advantage to the first calculation is if you sometimes do not have a value in one of the two fields. In those cases, the first expression leaves out the unneeded comma.

          • 2. Re: how to concatenate with a calculation
            SarahStacilauskas

            do i then just Export as text file? then when i export how do i get the calculation to run on my records? not sure what to choose for Field Export order?

             

            again..i am not a pro at programming so sorry if this is a dumb question.

             

            thanks for your reply

            • 3. Re: how to concatenate with a calculation
              philmodjunk

              You'll need to explain in much more detail what you are trying to do with this calculation and your data export. FileMaker does not export calculations, only the value returned by that calculation.

              • 4. Re: how to concatenate with a calculation
                SarahStacilauskas

                I have 1 file with 2 columns of text. First column has Numbers (that i call style numbers) and the second column lists what page number those numbers are found on.

                I need filemaker to take that text file and search for duplicates style numbers and then remove the duplicates but then list each of the page numbers that style number is found on.

                for example:

                Style NumberPage Number

                ALF18302F737

                ALF18302F600

                ALF18302F300

                 

                that should output like this

                Style NumberPage Number

                ALF18302F737, 600, 300

                 

                the style numer and then tab separated over to where the page numbers are combined together with commas.

                • 5. Re: how to concatenate with a calculation
                  SarahStacilauskas

                  crap it removed the tabs i put it.

                  this is what it should look like

                  for example:

                  Style Number         Page Number

                  ALF18302F             737

                  ALF18302F             600

                  ALF18302F             300

                   

                  that should output like this

                  Style Number          Page Number

                  ALF18302F              737, 600, 300

                  • 6. Re: how to concatenate with a calculation
                    philmodjunk

                    But what does this have to do with exporting?

                    You can import this data into a second table with the just the Style Number field Specify a unique values validation rule (validate always) on the style number. When you import the data a second time into this file, the duplicates will be eliminated and you get one record for each style number.

                    Then, link your two tables like this in a Relationship:

                    OriginalTable::StyleNumber = JustStyleNumbers::StyleNumber

                    Define this calculation field in JustStyleNumbers:

                    Substitute ( List ( OriginalTable::PageNumber ) ; "¶"; ", " )

                    and it will produce the list of page numbers for each style number, separated by comma and space characters.

                    • 7. Re: how to concatenate with a calculation
                      SarahStacilauskas

                      sorry.

                      my final step i need to Export a Tab Deliminated file with the stylenumber and page numbers listed out in the cleaned up view.

                      Style Number          Page Number

                      ALF18302F              737, 600, 300

                      • 8. Re: how to concatenate with a calculation
                        philmodjunk

                        Which you would export from the JustStyleNumbers table after importing the same data into both tables. Specifying the calculation field as one of the fields to be exported will export the value of that field--which in this case is your comma delimitted list of page numbers.

                        • 9. Re: how to concatenate with a calculation
                          SarahStacilauskas

                          Laughing I'm closer now...

                           

                          but still not working.
                          Under Manage..Databases..Tables.. i have 2 Table Names (OriginalTable and JustStyleNumbers)

                          For JustStyleNumbers Table i have the field StyleNumber its set to 'always validate' 
                          I added a calculation under this table  Substitute ( List ( OriginalTable::PageNumber ) ; "¶"; ", " ) 

                          For OriginalTable table i have the StyleNumber and PageNumber as before, both are set to Text and both are set to Indexed

                          Under Relationships i drew the connector between both tables StyleNumber

                          The result is stylenumbers are still showing duplicates and no page numbers have commas added.

                          Thanks a bunch for your help getting this far:)

                          • 10. Re: how to concatenate with a calculation
                            philmodjunk

                            How did you import data into JustStyleNumbers?

                            You need to import the same data twice, once into each table. When you import into just StyleNumbers, you should see a message after the import that tells you that not all records were imported. This is expected as the records not imported where the excluded duplicate stylenumbers.

                            If you view the data on a layout based on JustStyleNumbers, you should not see any duplicate values and you should see the comma separated list of page numbers.

                            This layout should only display the two fields defined in JustStyleNumbers, the imported style numbers and the calculation field that produces the list of page numbers.