Our client's custom field needs have expanded to the point where every one of the 10 slots for Orders and Contributions have been filled. To satisfy new data requirements, we've had to start double-purposing fields and embedding JSON inside the field value. Has anyone had success expanding custom fields in other ways? The only other idea I have so far is to alter the custom field columns in the database table to allow more than 255 characters, so that JSON data size won't need to be restricted as much. However, this may have unintended side effects elsewhere in the system and may not survive a major upgrade.
Ideally custom fields would be stored in a separate table called T_CUSTOM_FIELD that would relate to Orders, Contributions or any other type of object via a generic "entity_no" column. There would also be a "keyword_no" column to associate it with the keyword definition of the field and the "value" column would be a TEXT type to allow for very long values if needed. For backwards-compatibility, you could still assign up to 10 fields that would be stored in both the new table and in the current format with the custom_[x] columns, so the legacy columns would always be a mirror of the data in the new table.
Does anyone know if a better custom field system like this is on the Tessitura roadmap?
I use a custom table to do the job for web orders, but it isn't available in the application.
CREATE TABLE dbo.LTR_PRODUCT_DETAILS ( order_no int NOT NULL, product_id int NOT NULL, product_type varchar(50) NOT NULL, name varchar(50) NOT NULL, product_data ntext NOT NULL ) ON[PRIMARY] GO ALTER TABLE dbo.LTR_PRODUCT_DETAILS ADD CONSTRAINT PK_LTR_PRODUCT_DETAILS PRIMARY KEY CLUSTERED ( order_no, product_id, product_type, name ) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] GO