Parsing data
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?
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.
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
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
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.
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.
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.
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.
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.
Thanks again Phil. As usual I was making it harder on myself than it needed to be.
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.