Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

So far I have found the following means to get around this issue. In the first case scenario: 1. I create a field joining parts of the first and lastname: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT (LEFT ("FIRSTNAME",2),LEFT ("LASTNAME",4)) 2. I merge these with a previously created integer ID Primary key field which updated automatically: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","ID") 3. I can then copy the database and make my mergefield primary... There may be a simpler way:

In the second case using a date, I used the following command. 1. I create a field joining parts of the first and lastname: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT (LEFT ("FIRSTNAME",2),LEFT ("LASTNAME",4)) 2. Then I converted the DATEOFBIRTH field to the format desired having already created a TEST field. UPDATE "MYDATABASE" SET "TESTDATE"= TO_CHAR ("DATEOFBIRTH", 'YYMMdd') NB. The dd was necessary instead of DD in this case otherwise the date was rendered e.g. 9808220Sat instead of 980808, I dont understand why. 3. I merge these two fields: UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","TESTDATE")

I can only make this field primary after the fact, hoping that no two fields are identical adding an automatically updated id field would assure that: 4. UPDATE "MYDATABASE" SET "MERGEDFIELD"= CONCAT ("MERGEDFIELD","ID")

I have learned that the CONCAT operator does not seem to be able to bring together more than 2 fields at a time. Hope this helps anyone interested.