Text Field Length limitations and possible work-around?

brother_dev · 2011-10-19

We currently use the Navision 3.6 objects with the 5.1 executible on a SQL 2005 SP4 (X64) database. We have a project that requires that two tables be created that accommodate all text fields with the maximum length of 250. However, upon compiling the table, we receive the error that "The active fields in a record may not take up more than 4000 bytes... You must reduce either the number or the length of the active fields."

The table that we were going to build a form for contains 40 of these text fields where the user would be entering attributes to the product being edited. Just wanted to check if there were some work-arounds that someone may know of or if reducing the size of the field length or incorporating subform(s) were my only options.

Thanks

Savatage · 2011-10-19

So are you saying you are creating a table that has 40 fields of length 250?

I'm not sure what you are trying to achieve - what will you put in all these fields?

Are all these fields related to 1 item (for example)
Is there something that the item comments can help you with - or extended text function?

brother_dev · 2011-10-19

Yes, 40 fields of 250 characters. The fields are to be used for an external web catalog. The field lengths they provided us for data capture of an item can hold up to 250 characters. I realize the event that more than, say, 100 characters being used would be rare but this is what we are currently faced with. The data entered into this table will later be exported to this company for upload into their catalog builder application.

GRIZZLY · 2011-10-20

I think it's not a limitation of NAV, but SQL.
The possible workaround is to rebuild table structure, for example:
1. Item No. (PK)
2. Option Type (PK)
3. Option Value.

So, lately you had 1 record with 40 fields, in my case you would have 40 records.

Savatage · 2011-10-20

We also did something similar to what Grizzly is saying.
If all the fields are 250 & text then you really just need 1 250 field and a smaller field to mark its use (function)

As you, we need to handle very large web descriptions, it could be Item ingredients to a large text on how to use the product, warnings, etc.

So we also use a few fields in a new table to hold this info.
Similar to the comments table.....
1.Item No. (code20)
2.Line No. (Integer)
3.Item Texts (250)
4.Date Modified (Date)

Then we use Waldo Pad to easily fill the table. ( http://www.mibuso.com/dlinfo.asp?FileID=335 )
IN Grizzly's post he mentions "Options" for you. Where you change your 40 fields into useable options, so you don't need so many giant fields, just 1.

Since you don't specify what's going in all these fields it's hard to be more precise but you should be able to see the forest thru the trees with these suggestions. Think about it. Good luck.

ara3n · 2011-10-20

It is a limitation of NAV c/side server and for compatibility purposes they kept it at 4KB.
SQL limit is 8kb.

Text Field Length limitations and possible work-around?

Comments

Categories