Why does ObjectCustomFieldValues store Select values' names rather than IDs?

Hello!

[This is in a 3.4.x instance; I’ve not verified this in a 3.5.x instance
as yet, but will do so later this week]

The subject says it all. I don’t understand why, for Select-type CFs,
selection names are stored as text in the Content column of the OCFV
table rather than as the selection’s CustomFieldValues ID.

Unfortunately, this means that if you change the name of one of your
Select CF’s values, you need to either sweep the OCFV table for the old
value and change each reference to the new value (a simple SQL UPDATE
statement, true). Otherwise, the old value will continue to be displayed
in existing objects. Also, if someone updates a CF in the web UI, the
old value will be lost since it didn’t load into UI properly.

If this is a bug, I could probably work out a patch in the next week or
so. If not, I want to understand the benefit of this before I go change
it. =]

Thanks!

–j
Jim Meyer, Geek at Large purp@acm.org

Hello!

[This is in a 3.4.x instance; I’ve not verified this in a 3.5.x instance
as yet, but will do so later this week]

The subject says it all. I don’t understand why, for Select-type CFs,
selection names are stored as text in the Content column of the OCFV
table rather than as the selection’s CustomFieldValues ID.

Because otherwise it becomes basically impossible to switch select
custom fields to entry custom fields. And the search logic would get
even more complex. We debated long and hard about this one. I’d
certainly love to see a proposed patch to make it easier to rename
values sanely.

Note also that with the current scheme, if you have a “Department"
custom field and start renaming values in 1949, such that “War” becomes
"Defense”, you’ll have tickets which claim their department was
"Defense" way back in 1920. Which will play hell with your internal
auditing.

Jesse

Hello!

A separate question first: I did a short writeup of the relationship
between the CF, CFV, OCF, and OCFV tables once I understood it. I didn’t
(and still don’t) see anything in the wiki that discusses this. Would it
be useful for me to put my writeup in there and, if so, where?On Tue, 2005-10-11 at 12:33, Jesse Vincent wrote:

On Tue, Oct 11, 2005 at 12:05:28PM -0700, Jim Meyer wrote:

Hello!

[This is in a 3.4.x instance; I’ve not verified this in a 3.5.x instance
as yet, but will do so later this week]

The subject says it all. I don’t understand why, for Select-type CFs,
selection names are stored as text in the Content column of the OCFV
table rather than as the selection’s CustomFieldValues ID.

Because otherwise it becomes basically impossible to switch select
custom fields to entry custom fields. And the search logic would get
even more complex. We debated long and hard about this one. I’d
certainly love to see a proposed patch to make it easier to rename
values sanely.

Thanks for the quick explanation! That makes it all make sense and I
definitely see your point regarding the search logic. I was not/am not
looking forward to that part. I also very much appreciate the congruency
of select and entry CFs as it makes what I’m attempting much more
straightforward than it might have been.

We’re messing about with a tagging mechanism (aka folksonomy) which
would allow users to arbitrarily add new keywords after first validating
them against existing keywords (as well as dictionary words) to reduce
the incidence of “broken”/“borken”/“fubar”/“foobar”/etc.

To do this, we’re pondering creating a new CF type, FreeSelect, which
behaves as an “Enter Many” (Freeform) as far as the user is concerned
but is stored as a “Select Many” (Select) as far as the code is
concerned.

It seems that the proliferation of similar tags would be very likely, so
I was trying to find an easy way to administer them by merging them
together (again, a la tickets). Unfortunately, leaving it as is seems to
make that very difficult or perhaps impossible.

I’ll ponder further. If anyone has a thought or two, please feel free to
share. =]

Thanks!

–j
Jim Meyer, Geek at Large purp@acm.org

Hello!

A separate question first: I did a short writeup of the relationship
between the CF, CFV, OCF, and OCFV tables once I understood it. I didn’t
(and still don’t) see anything in the wiki that discusses this. Would it
be useful for me to put my writeup in there and, if so, where?

Yes. I’m not sure where, though. Put it up and link it from somehwere
prominent and someone will move it if it’s not in the right place :wink:

We’re messing about with a tagging mechanism (aka folksonomy) which
would allow users to arbitrarily add new keywords after first validating
them against existing keywords (as well as dictionary words) to reduce
the incidence of “broken”/“borken”/“fubar”/“foobar”/etc.

To do this, we’re pondering creating a new CF type, FreeSelect, which
behaves as an “Enter Many” (Freeform) as far as the user is concerned
but is stored as a “Select Many” (Select) as far as the code is
concerned.

That’s definitely an interesting concept. I’d been thinking about doing
a tags sort of thing as well, but it hadn’t gotten there yet. We’re
using tags in another project. It might be worth looking at Text::Tags
and HTML::TagCloud as you go.

It seems that the proliferation of similar tags would be very likely, so
I was trying to find an easy way to administer them by merging them
together (again, a la tickets). Unfortunately, leaving it as is seems to
make that very difficult or perhaps impossible.

The modern wisdom is that those sorts of things work themselves out over
time and enforcing a wordlist for tags makes it not very folksy :wink:

Jesse