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?
ScottSchuler, could you imagine how this “solution” should look like? what do you want to change, the base hardcoded sorting rule for SQL or NAV?
I suppose either or. The solution going forward would be that all new companies created would match the older companies in SQL. Whether that’s changing the existing companies in SQL or changing the sort in Object Designer. I suspect changing the SQL would be the fastest but I’m not sure about rights.
Do you have a possibility do change somehow sorting in SQL (I think it is in SSMS)?
I tried in a test copy to do it by going to design and reordering the column name. The error I get is:
“Saving changes is not permitted. The changes you have made require the following tables to be dropped and re-created. You have either made changes to a table that can’t be re-created or enabled the option Prevent saving changes that require the table to be re-created.”
Admittedly I’m not a SQL guru and typically would steer clear of any hard changes to the tables.
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.