Dear Navision Gurus, I hope to give you a little gymnastics for your minds: We have a field Position (data type - Code). It is populated with some values like “PT”, “Part time”, “Full time”, “Intern”, etc. A user right now can type whatever he/she wants. We would like to limit values to just few - make a drop-down menu. When I was changing the datatype in the Employee table for the “Position” field I have received an error message that I can not do that since the field already has some values in it. Question: what do I do? Should I create a new field and somehow (how??? [?][?]) transfer the values? Prior to that I should probably have to do some data cleansing - Find/Replace… If you could, please provide a basic code so I can implement that project. Thank you!
Hi, Whenever you change a datatype, the system expects that the value in all records be blank, hence you have to first delete all the values in the position field in all the companies and system would/should allow you to change the datatype. Also make sure that if this field is being used in other tables, change the datatype in also these tables because if it has a lookup it would reference that field and at some point of time you would get a error about mismatch of datatype. Thanks Best regards Suresh New York.
Suresh, thanks for your reply. The problem is that I DO want to keep the value/history. What is in there right now is important and I can not just delete it. It is not, however, linked/used in other tables.
- Make a new field “Position New” of the appropriate type (Either option or Code). 2. Update this new field using the old fieldvalues (run through existing records)- be sure to cover all values (example below using optionfield) CASE Position OF ‘PT’, ‘Part Time’ : “Position New” := “Position New”::“Part Time” ‘FT’, ‘Full Time’ : “Position New” := “Position New”::“Full Time” etc… END; Position := ‘’; MODIFY; 3. Delete the old field (now blank)
hi If it is not used in other table. it wil be easy for you to have the data in a text file for all the records where there is a value for the field u have mentioned. After having the data in a file, yo u can delete the values and then write a simple code which will populate the relevent value from the file in the records. With REgards Harikesh
quote:
Originally posted by Denis Petrov
Dear Navision Gurus, I hope to give you a little gymnastics for your minds: We have a field Position (data type - Code). It is populated with some values like “PT”, “Part time”, “Full time”, “Intern”, etc. A user right now can type whatever he/she wants. We would like to limit values to just few - make a drop-down menu. When I was changing the datatype in the Employee table for the “Position” field I have received an error message that I can not do that since the field already has some values in it. Question: what do I do? Should I create a new field and somehow (how??? [?][?]) transfer the values? Prior to that I should probably have to do some data cleansing - Find/Replace… If you could, please provide a basic code so I can implement that project. Thank you!
Hi! Just add new table “Work time type” and then add TableRealation to your fields. Fill new table with values that you want and dissable users to write/delete record is it. Then use Search&Reaplce to consolidate values of your fileds. I do not like Option fields. To mach can go wrong when you have to change values. regards Bostjan