10 Replies Latest reply on Oct 31, 2014 12:51 PM by MarkGores

    Parsing data

    MarkGores

      Title

      Parsing data

      Post

      I need to parse out a text field that may have one or many sets of numbers.  If there area multiple sets, they are separated by commas, but can be 5 or 6 digits long.  I would like to duplicate the other fields in the record for each of the sets of numbers in this field.

      Is there an easy way to script this?

        • 1. Re: Parsing data
          philmodjunk

          What exactly do you mean by:

          I would like to duplicate the other fields in the record for each of the sets of numbers in this field

          An easy way to parse data where all the data is separated by the same delimiter is to use:

          Substitute ( Text ; DelimittingcharacterHere ; ¶ )

          This gives you a return separated list of values and you can use the GetValue function to extract any single value from the list.

          • 2. Re: Parsing data
            MarkGores
            The records are from UPS and FedEx shipping programs.  I am trying to get the tracking numbers correlated to jobs in another DB.  The shipping people enter the job numbers in one of the fields in each of those programs and at the end of the day they export the shipped jobs. I'm importing the two files and combining them to one table of all shipped jobs.  The issue is that sometimes there are multiple job numbers for a shipment.  They are separated by commas if there are more than one I've been playing with the "word" function and seem to be getting close. Just trying to get through the looping and if statements so that it takes a record with multiple job #s and makes a separate copy for each job #. The checks the next, and so on...
            • 3. Re: Parsing data
              philmodjunk

              I can set up the loop but it's not clear what you are doing inside that loop:

              Set Variable [$JobList ; value: Substitute ( YourTable::JobListField ; "," ; ¶ )
              Loop
                 Set Variable [$K ; value: $K + 1 ]
                 Exit Loop If [ $K > ValueCount ( $JobList ) ]

                 #what goes on here is not clear, but GetValue ( $JobList ; $K ) will extract one job number at a time from the list if used here.

              End Loop

              • 4. Re: Parsing data
                MarkGores

                The table is 4 fields,

                Combined::ShipID

                Combined::JobNum

                Combined::ShipDate

                Combined::TrackingNumb

                As the data is initially imported there is one record per tracking number.  Those records can have 0, 1 or multiple numbers in the job number field.  My goal is to have one record per job number so that I can have the other database that tracks all jobs show the tracking number for each job.

                The script I've been working on looks something like this:

                Go To first record

                Loop

                If Word Count (Combined::Jobnum >1)

                Set variable $jn ; Value Combined::JobNum

                Set variable $wc ; Value Word Count (Combined::Jobnum)

                Set variable $rn ; Value Get(recordNumber)

                Loop

                   Duplicate Record

                   Set field [Combined::JobNum ; RightWords ($jn ; 1)]

                   Set Variable [$wc; value $wc-1]

                   Set Variable [$jn; Value; LeftWords ($jn ; #wc)]

                Exit Loop If [$wc = 0]

                Go to Record $rn

                Delete record

                Go to Record Previous

                End If

                Go to Record [Next ; Exit after Last]

                End Loop

                • 5. Re: Parsing data
                  philmodjunk

                  I see problems with that approach. In particular, the position of your original record in your found set (that's what Get (RecordNumber) returns) might change as duplicate records creates more and more records so you need to be careful that your records aren't sorted with the "keep records in sorted order" option specified.

                  And you might want to use IsEmpty instead of = 0 in your Exit Loop If step

                  Or you can adapt my example that parses the data with slightly simpler methods to do what your script does in terms of duplicating records.

                  • 6. Re: Parsing data
                    MarkGores

                    Are you saying that if the job numbers are separated by ¶ rather than commas that the other database will be able to relate the tracking number to each of the job numbers?

                    That would make things really simple.

                    Edit: That does seem to work.  I can get rid of that parsing script and just add a ReplaceFieldContents  [Combined::JobNum ; Substitute (Combined::JobNum ; "," ;  "¶") to my import script.

                    This table, Combined, is only being used as a place for the Master Job Tracking database to extract the  tracking number from.  The end result I'm thinking of is:

                    After shipping has run their end of day reports, import the csv files into the appropriate FedEd or UPS table.

                    Combine that data into the combined table

                    Have the "Master Job Tracking" database update jobs that have shipped with their tracking numbers and method shipped.

                    • 7. Re: Parsing data
                      philmodjunk

                      Are you saying that if the job numbers are separated by ¶ rather than commas that the other database will be able to relate the tracking number to each of the job numbers?

                      It is true that this is possible, but no, that is not what I have suggested. Using Substitute to replace the commas with returns just makes parsing the text into individual records a little bit simpler.

                      • 8. Re: Parsing data
                        MarkGores

                        I didn't realize that the relationship would work with the return separated job numbers allowing multiple jobs to get to the same tracking number.  That negates the whole reason for splitting it into individual records since all I need this for is a transitory step to move the tracking number over to the appropriate Master Tracking records.

                        • 9. Re: Parsing data
                          philmodjunk

                          This is one of those little "gems" unique to FileMaker (as far as I know) it results in using a field with a return separated list as a kind of "hidden join table" for a potentially many to many relationship. It works and has its uses, but also has significant limitations.  If this is only for a "transitory step" it is likely that this is a workable option.

                          • 10. Re: Parsing data
                            MarkGores

                            Thanks again Phil.  As usual I was making it harder on myself than it needed to be.smiley