Wanted: help on a script create records from a field that contains a list of values.

[Angela10]

I am preparing to move some data from an old legal calendaring system to a new one. I'm using FMP to assist in the reorganization of some data to make exporting from the legacy system easier. In the old system, attorneys related to an event was tracked using a text field on the event record. The field lists the attorney's initials separated by a comma (Event 1 ABC,DED,MMS, Event 2 DEF,BBD, Event 3, FKD,....). The new system is structured differently, related attorneys are actually a related table to the event table. so, I need to be able to get those attorney initials in separate records and include the eventID field. ABC,1 DED,1 MMS,1 DEF,2 BBD,2 FKD,3 DPW,3 NZG,3 Any pointers? Thanks - A

[eos]

Write a looping script to 1. create two lists holding event IDs and associated initials, respetively, and 2. go to the join table and process these lists, along the lines of …

Loop
  If [ not IsEmpty ( Events::initials ) ]
    Set Variable [ $eventIDs ; List ( $eventIDs ; Event::ID ) ]
    Set Variable [ $initials ; List ( $initials ; Events::initials ) ]
  End If
  Go to Record/Request [ next ; exit after last ]
End Loop
#
If [ IsEmpty ( $eventIDs ) ]
  Exit Script
End If
#
Go to Layout ( EventsAttorneys … ]
Set Variable [ $eventCount ; ValueCount ( eventIDs ) ]
Loop
  Exit Loop if [ Let ( $eventCounter = $eventCounter + 1 ; $eventCounter > $eventCount ) ]
  Set Variable ( $currentInitials ; Substitute ( GetValue ( $initials ; $eventCounter ) ; "," ; ¶ ) ]
  Set Variable ( $initialCounter ; 0 ]
  Loop
    Exit Loop if [ Let ( $initialCounter = $initialCounter + 1;  $initialCounter > ValueCount ( $currentInitials ) ]  
    New Record/Request
    Set Field [ EventsAttorneys::eventID ; GetValue ( $eventIDs ; $eventCounter ) ]
    Set Field [ EventsAttorneys::attorneyInitials ; GetValue ( $currentInitials ; $initialCounter ) ]
  End Loop
End Loop
# etc

.