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