We have custom fields that get a 50000 range number and appear in the middle of the Object Designer. However, in the SQL tables for all companies that were created before the custom field, the field is appended at the bottom, thus making Unions between before and after companies difficult. Has anyone encountered this and have a recommended solution?
Not quite sure why it makes union queries more difficult? Just copy the query that's already in your union query then change the table name, it doesn't matter what order the fields are in then. It's arguably quicker anyway?
I presume it's just sorted by the SQL field id in Sys.fields. when NAV adds the field in a new company it works through the fields in NAV field ID order and adds them so they appear in the "correct" order. Adding it to an existing table it has no choice but to stick it at the end. Just a guess though, would be interesting to know for sure!
Not recommended but if your don't want to change it the "you are not permitted to save changes" error you're getting is just an SSMS option, just go "tools > options > query designer > allow schema changes that require drop and create" (or something along those lines). If its a large table though it may take a while to apply.