The Old Joel on Software Forum: Part 3 (of 5) - Allowing users to create custom fields

Allowing users to create custom fields

Anyone have any good recommendations for how to allow users to create custom fields for a web based database application?

I've got a product that would probably see a decent increase in sales if it allowed customers to create their own fields. The application has 6 types of data entry, each with their own data entry page, edit/update pages, and several reports depending how they want to slice and dice the data.

A simplified overview of the database is as follows: there is a tblEntry master table for common data fields and 6 tblEntryType1 -- tblEntryType6 tables for entry-type-specific data fields. There is also a tblEntryType with metadata about the 6 different entry types.

I was thinking I could provide an admin tool that allowed the customer to add a field to any of the entry types. My first thought was that the admin tool would alter the SQL, making the new field intrinsic to the table. But that approach makes discovery of custom fields at the middle tier more difficult.

My second approach would be to have a hasCustomFields field in tblEntryType. In conjunction with that, I would have a tblCustomFieldDefinition and tblCustomFieldEntry. tblCustomFieldDefinition would have fields to define the parent entry type, the field name, the data type, and a rules expression. tblCustomFieldEntry would store the actual entries as VARCHARs. The middle tier component would check if an entry type has custom fields defined and if so handle all data conversion, parse the rules expression, then apply the rules if necessary.

The problem with the second approach is that there would be added overhead to every transaction. And it just seems kludgy.

Lastly, I'm not sure how to place the custom fields in the layout. The easiest way would be to have the middle tier component generate the HTML to place them at the end of the form. But this approach isn't the most user friendly from the customers viewpoint. And I haven't even looked at how to add the custom fields to the reports, which are generally more layout sensitive (people like pretty reports, you know).

I'm not prepared to write a custom layout designer. I also don't think that providing consulting for customization is feasible, since the product's market advantage is lower cost.

With all that said, anyone have experience writing an app that allows users to create custom fields? Any advice?

Thanks,

Nick
Monday, December 15, 2003

I wrote a sales forecasting web app that is primarily made up of custom fields. Admin users can assign various types and validations to a field like a numeric with two decimal places between 1.00 and 100.00. I also allow them to move a field around a grid so they can place fields anywhere they want within a form.

Behind the scenes these fields are all stored as varchar(100) fields. This limits fields to this amount of characters. This obviously leads to issues with querying and sorting dates and numeric fields. I also have a dynamic report generator I wrote which is basically an interface for creating SQL statements. For the dates and numerics, I just CAST or CONVERT using the appropriate command. The site runs on MySQL or SQL Server, so there are a difference set of rules for each server on conversions.

The biggest challenge is to find the balance of flexibility, performance and code complexity.

Wade Winningham
Monday, December 15, 2003

Basically, all this is in a data dictionary.

Wade Winningham
Monday, December 15, 2003

Just an aside, let the user enter whatever-the-hell-they-want-to (TM) in the existing fields.

My personal beef is telephone numbers. US masked fields "(xxx) xxx-xxxx" don't work in the rest of the world, and don't cover all posibilities even in the US.

Let them enter "123 1231234 ext:1234", or "3123123 (her father's work)" or even "dunno, ask bob" in there.

It's not as if you're going to try and dial that number automatically (eg modem), so why get so enthused in limiting entry?

AJS
Monday, December 15, 2003

My users dial them automatically which is why I store phone numbers +<country code> (<area code>) <local number>. There is a standard for everything.

John Ridout
Tuesday, December 16, 2003

And you want them to enter "ask Bob", give them a field for notes.

John Ridout
Tuesday, December 16, 2003

John Ridout: There is a standard for everything.

There's also a joke - "Which one would you like?"

Sorry John, but what you are saying is a cop-out. This is a forum for software design, and what you are doing is poor design. Just because everyone does it like that is no excuse, and doesn't make it a standard. (I know, you're talking about the format, I'm talking about numeric vs alphanumeric)

How do you handle telephone extensions? Shoving them in the note field along with 'ask bob' is a major flaw. Say you printed an address book or business cards. It's unlikley the note would get printed. Ok, 'ask bob' as the number is possibly embarrassing, but so are blank looks.

When you autodial, just strip the non-numerics. It's not hard. Use a bit of imagination, and give the poor users a bit of freedom & power. It also makes your app future proof.

AJS
Tuesday, December 16, 2003

In this case there is a single recognised de jure and de facto standard, which fits well with your comment, "Just because everybody does it like that..." So the joke does not apply.

My fomat was alpha numeric but very restricted.

Telephone extensions are deliberately not handled because the idea is to be able dial phone number from the PC and also to have the ability to match incomming numbers against the database.

The number of contacts with telephone extension numbers is miniscule and is best handled by a notes field.

I'm not printing address books or business cards.

Without knowing the requirements it's not entirely sensible to criticise the design decisions.

John Ridout
Tuesday, December 16, 2003

I've never found the need to restrict data entry in 'phone fields, even when doing telephony apps (call logging / billing, IVR).

I usually use a 20 char alphanumeric field.

I find more the more restrictions you have = more bugs & more maintenance.

Reminds me of people who delete apostrophes from names rather than fix the SQL.

AJS
Tuesday, December 16, 2003

The problem comes when you want to SEARCH for a phone number. What I really ought to do in this case is to store both but make it transparent for the user. When users dial from different countries you need the full international number.

Where are you and how common are extension numbers? The kind you have to dial after the normal telephone number not the DDI kind.

Not handling apostrophes in SQL makes me cringe, ew, SQL injection, ew.

John Ridout
Wednesday, December 17, 2003

>I'm not prepared to write a custom layout designer.

How about this?

Adding say 20 numeric fields at the end of each entry table and just store field ID numbers to data that is stored externally. This way you can search easily, since all the custom data is stored in one table only. No need to dynamically recreate your tables.

Just add a table along the lines of this:

TblCustomFields
ID_CUSTOM_FIELD NUMBER NOT NULL,
NAME VARCHAR2(30),
VALUE VARCHAR2(4000)

And in your entry tables add :

fldCustom1 - fldCustomNN, and in those numberic fields store the ID_CUSTOM_FIELD. You can also display the correct number of "extra added" custom fields by counting the NOT NULL values in the respective Entry table.

HTH,

Patrik
Wednesday, December 17, 2003

Ah searching for 'phone numbers, that's a little different.

In most systems, 'phone numbers are secondary data, IDs or names being typically searched.

Do what you suggested. Have 2 fields, one 'raw', one 'clean'. Search thru clean, return raw. I've implemented this before, it's identical to handling of Surname/Soundex.

It's possible to store the clean field as numeric to speed up searches & save space. Has it's problems, eg losing leading 0's, so you add a dummy prefix. Partial matches aren't possible, although if you reverse the number before you store it, eg 02-1234 becomes 432120, finding the number (1234) without the prefix is possible, search for the next record past 432100. Don't get too carried away though, space & speed are fairly cheap.

I'm in Australia, all phone numbers are currently 10 digits, this includes landline, modile, freecall, premium rate etc. Local numbers are 8 digit, area codes are 2 digit. There are a few exeptions, 6 digit toll-free, 5 digit directory services etc.

Whether you can direct dial an extension depends on the targets PABX.

About 6 years ago there was a major overhaul of 'phone number in Australia. Local numbers used to be 6 or 7 digits, with a 3 or 2 digit area code, max of 9 digits. Mobiles, etc had 9 digits.

Almost EVERY number changed.

Anyone using a masked edit box got to send out a new software release. Not me!

Once I was programming IVRs. We ran a line for a psychic. Basically, suckers (er clients) rang the 1900 number, the IVR then rang the psychic at home, and patched the two together. She complained that the calls would drop out after a while. Odd, no timeouts were set...

It eventually transpired that her number was 991-2345, the move to 10-digit numbers made it 9991-2345. The IVR came from England. As they all know 999 is the emergency number (000 in Oz). The equipment maker added a feature that the IVR could never make a call longer than 20 minutes to the emergency line, apparently a safety feature in case the IVR ran amok.

The IVR spotted the 999, thought a call was being made to the emergency line, and duly cut it off after 20 minutes.

More features, more bugs.

AJS
Wednesday, December 17, 2003