I would leave your raw data fields alone and simply pull each piece needed and set new fields. To test that the data will properly be translated, create the following three new text calculations, repointing where I say Data1 and Data2 to your original two data fields:
cItemCode: Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 )
cItemName: RightWords ( data2 ; 1 )
cStatus: LeftWords ( data1 ; 1 )
Once you are sure, by checking through your records, that these calculations produce the correct results (be sure the result type of all three fields is TEXT), then you can create your three standard text fields ItemCode, ItemName and Status and script setting the fields with something like this:
Show All Records
Replace Field Contents [ ItemCode ; by calculation ; Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 ) ]
Replace Field Contents [ ItemName ; by calculation ; RightWords ( data2 ; 1 ) ]
Replace Field Contents [ Status ; by calculation ; LeftWords ( data1 ; 1 ) ]
If this is multi-user file then run it when no Users are in the system to protect from record locking.
Well it does work however thier is a problem that i really didnt define clearly. Not every entry has the "LOV" tag this is the exception not the rule. So a correct entry which I will import will be Itemcode:LSN1001009, ItemName: White Wine , Status: (I created to know once the code was parsed out which items would be"LOV"). So now with the calc fields defined the other entries become incorrect. How can I define to only the entries that have "LOV"?
Thanks for the edification! This forum is quite amazing.
Hmmm, I do not know if this is a one-time thing or an ongoing process but you now have a Status field will LOV or LSN etc. So you can perform a find for LOV in field Status and run your script through those.
Otherwise, you can perform a find on Text1 for: ==LOV*
This will isolate the records you wish to split.
Optionally, for viewing the calculations only on those entries beginning with LOV to ease the stress on your eyes, wrap blue portion around your first two calculations (example on your first calc):
Case ( LeftWords ( data1 ; 1 ) = "LOV" ;
Right ( data1 ; 2 ) & LeftWords ( data2 ; 1 )