Hi DUG,
I have added a BOOLEAN field to the customer table in NAV 2013 R2, via the Development Environment. I have then added this field to the customer card page. Functionally, it is working fine. However, if I try to use the Dev Env to create a backup of the company, I get the following error:
“Customer table contains a boolean field that does not use a sql server data type. a value other than 0 or 1 has been found in this field”
When I look at the data in SQL I see that the SQL datatype is TINYINT and the values are all 0 or 255. I have inspected other NAV BOOLEAN fields and they are TINYINT also, but the values are stored as 0 or 1 (as you would expect).
Can anyone help me here? Have I done something wrong when adding the field? I can’t see any different to the standard fields in the properties.
Thanks in advance,
PG9
hi,
best you remove that field, save/compile the table and re-create the field.
the sql column size should be 1.
Thanks Jonathan. I will try this and let you know how I go. Appreciate you taking the time to respond.
Best wishes,
PG9
Hi Jonathan (plus anyone else who reads this),
Further to your suggestion I did find that recreating the fields and recompiling the table, then re-importing the data did work. So, having tried this in a demo environment, I looked at my live environment. What I found was that if you un-tick, save and re-tick the tick box on the page, the subsequent value is saved as 1.
Looking further, it turns out that these flags were originally imported from an Access database. The datatype in Access was yes/no, which is a 16 bit field storing ‘no’ as 0 and ‘yes’ as -1 (dec, equal to 111111111111111 in bin). Translating this into the tinyint fields directly through SQL, rather than NAV, truncated this to 8 bits, which then equals 255 dec.
In short, the issue was not with NAV but rather with a datatype translation between Access and SQL.
Jonathan, thanks for your assistance in sorting this.
Best regards
PG9