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
You could always submit an IDEA to Tessitura as an enhancement, but I definitely have not heard rumblings of an expansion of custom fields from anyone, nor heard many people talk of that notion either, so I would not be particularly expecting of additional updates there. It never hurts to try, but I just would not get my hopes up.
For our custom needs, much like Alan Draper, we use custom tables. Is there a reason a custom table will not work for your needs here? Seems like a custom table based on the order/contribution reference number would get the job done, and certainly your custom table(s) would be as flexible as you need it(them) in terms of number of columns and their types and dimensions.
John A. Moskal II
Thanks, I certainly have considered a custom table. I think the lack of a UI is the hang up. If some of the fields the client wants are only needed in reports, then I suppose those could be moved into a custom table, assuming one can join custom tables in a report?
Assuming you are talking about a SQL/SSRS report, then you most certainly can use custom tables there. I have PLENTY such reports myself. If you mean in Analytics, I am not well versed enough in that to know one way or the other, though it would not surprise me at all if custom tables were inaccessible (at least for now) in Analytics.
And yeah, lack of a UI can be tricky for things that need to be updated/entered by Box Office staff rather than just coming through online. I have one custom table which is updated by a custom utility. I am trying to figure out a good flow for creating custom tabs in Tessitura to make such updates. Thus far all of my custom tabs have been InfoMaker based, but as that is going away, I am trying to get into creating the web based custom tabs that seem to make sense for the future. But that would seem like the most logical way to give your users an easy way of entering that data.