Field is too large - 01

Problem Description

Error Encountered: “Field is too large (32K) or View’s column & selection formulas are too large”

Problem Scenario:

You have recently deployed some of your changes and suddenly every document gets locked. You can’t edit any existing documents or save a new document. When you close or open any document you get the following error:

You can’t do anything with the database. You check in your test copy and everything works perfectly fine there. You check the production copy’s data size and it is not too much to trigger the error. This error doesn’t let anyone to make any changes to the database.

Problem Solution

Solution:

Step 1: Find out the Form name from the document properties which when opened/edited/saved/closed throws the error.

Step 2: Find out how many hidden Text fields are present in the Form/Subform that you found out in the Step 1. These hidden fields usually have some look up code which is used in the other Listbox field of the form. Some time these Text fields if are doing look up of more than it’s maximum limit i.e. 32K then it would throw the exception that field too large.

Step 3: Now, you have to find out which are the field(s) that have the look up code in their Default Value option. E.g. say field name is schMainCompList and the lookup formula used is:

LookupList:= @Unique(@Trim(@DbColumn("":"nocache"; ""; "LUCSCEntityName"; 1)));
@If(@IsError(LookupList) | LookupList = "“; "“; LookupList : schMainCompExtra)

Every time the document is opened/edited/saved/closed the field does a lookup as the form is getting refreshed; thus triggering the error.

Step 4: Change the code to the following:


@If( @IsNewDoc | @IsDocBeingEdited;
               @Do(LookupList:= @Unique(@Trim(@DbColumn;"":"nocache"; ""; "LUCSCEntityName"; 1)));
               @If(@IsError(LookupList) | LookupList = ""; ""; LookupList : schMainCompExtra));
"")

Now this would let the field to do the look up only when the edited and not when they are opened/saved/closed.

Step 5: Now, change the field type to Listbox and check the option Allow multiple values as shown below.

Step 6: Sometime, making the code change till Step 5 resolves the issue. But the issue may still re-occur after some other time. May be this time with some other form name but with the same subform, may be. Thus, to avoid this issue further, add the @DbLookup(…) code to the Formula Window of the field properties:

This completely resolves the issue of 32K exception triggered by the hidden text fields.

In short: Check if Text field > Change to Listbox > Place the @DbLookup code in Formula Window > Use proper validation.