Prevent Custom Field Lost Data

2022-03-24 07:45
Written by
kbeireis - member of the CiviCRM community - view blog guidelines

It’s a great idea to create a Custom Field with the field type Checkbox, particularly when you have many, many options. Did you know there is a risk of losing data if you don’t have the checkbox set up properly?

CiviCRM will attempt to save the information to the database without regard to the length of the items chosen.

If the length of all the options chosen are longer than the database field length, an error will be written to the error log, the record will not be saved properly, and a confusing and generic error (like the one shown below) will appear for the front-end user, which might prompt them to reach out to you with questions.

Error for Custom Field

The 2 options for avoiding the problem are as follows:

  1. Use shorter codes to store in the backend database: Option Value (under Multiple Choice Options) - enter numbers in order or use short abbreviations that describe your labels.
  2. Adjust the Database Field Length: when creating your custom fields, make sure the Database Field Length is set at a length that accounts for the number of options they can choose and the length of each option.
New Custom Field Screen

If you increase the database field length to a large number, this may have an effect on the indexing feature if you choose to make the field searchable.

If you are getting an error message, then follow the next steps:

  1. In the settings for the field, enter a large enough number in your Database Field Length field (the maximum number is 2000) to accommodate all the options.
  2. Save.

Why is this Important?
Information can be lost as it will not be written to your contacts' profiles.

Case Study:
We have a nonprofit client with a very sophisticated exhibitor registration form. Exhibitors have the option of selling or displaying the following items (they can check as many as apply - see below).

  • As exhibitors check on the different options, CiviCRM starts adding all the characters under Value for all the options checked.
  • If the sum of all characters exceeds the Database Field Length, the process will be broken.
    As explained above, CiviCRM returns a very generic error and the information is not written to the exhibitors' profile.
  • Initially when setting up the field, the Database Field Length defaults 255. The Value was set to the same as what was entered under Label which meant that the data being stored was rather long for each item selected.

As you can imagine, it was quite easy to exceed the database field length of 255 once exhibitors started checking a few options. This prevented the information from being written to the exhibitor's profile.

Once we changed the field length, the exhibitor profiles were updated with their items to sell.

At BackOffice Thinking, we focus on technology so nonprofits can focus on their mission.

Filed under