Assuming that the ID field is 100% consistent in format, Import the data first into your encounters table. Before doing so, add an extra text field to recieve the above ID field plus a second field defined with an auto-entered calculation like this:
Leftwords ( OriginalID ; 1 )
This field will strip off the a, b etc. so that you just have what should be a unique ID for each customer. Import the records into this table and enable the auto-enter option so that this calculation will produce the desired unique ID.
Now switch to a layout for the customer table. Open manage | database | Fields and specify unique values, validate always for the ID field in this table.
Use Import records to import this data from your encounters table and import the contents of the calculation field into your ID field. The validation rule will block repeated imports of the same ID and this will filter your values down to one entry per record in this table.
You can now define a relationship between the ID field in customers with the ID field where we added the auto-enter calculation in Encounters. After you are certain that this all worked, you can remove the auto-enter calculation and update the ID field in customer to be a serial number field with a next serial value setting larger than the largest imported ID number.
Thank you so much. I willl try it and let your know.
I used a variation of your solution and it worked. Thank you. Another question, is there a way to search for records that have empty container fields only?
Define a calculation field that references the contents of the container field.
It might just be the expression: ContainerFieldName with text selected as the result type. Then search for records where this calculation field is empty by specifying a lone = operator in the field.
After posting my container question, I searched in the forum and found this solution:
Case ( IsEmpty ( Graphic) ; 0 ; 1 )
Your's woks too. Thank you