• Replies 3 replies
  • Subscribers 336 subscribers
  • Views 144 views
  • Users 0 members are here
Related

Custom field in Plans with a drop down

Jess Levy
$organization

Hi!

I am trying to build a custom field in Plans that will have a drop down and be available for use in lists/extractions. The custom element shows up, but there are no options in the drop down and I get a invalid entry message of "Ambiguous column name 'key_value'."

In T_Keyword, I have the following entries:

Data Type: String
Edit Mask: None
Detail Tbl: T_PLAN
Detail Col: !.custom_1
Ref Tbl: t_kwcoded_values
Ref Idcol: key_value
Ref Descol: key_value
Ref Where: keyword_no = 595
Ref Sort: key_value
Category: Plans
Use for List: Plan
Custom Id: 1

I then have the drop down options built in T_KWCODED_VALUES.

What am I missing?

Thanks
Jess Levy
San Francisco Opera
415-551-6319

Parents
  • $organization

    Hi Jess.

    I just tried this out in our test system (v 12.5.1) while running a trace. The reason for the error is that the constructed SQL to return your values reads:

    SELECT key_value, key_value FROM T_KWCODED_VALUES WHERE keyword_no = 595 ORDER BY key_value

    SQL doesn't know which key_value in the SELECT to order by as both fields are named the same. I tried aliasing the description field as v2 ("Ref Desccol" value is "key_value v2") but the SQL generated was as follows:

    SELECT key_value AS Id, key_value v2 AS Description FROM T_KWCODED_VALUES WHERE keyword_no = 16

    so the app knows to create an alias, but doesn't seem to quite do it right (a bug?)

    The only solution I've found at the moment is to change the "Ref Idcol" value to "id" - the "Ref Sort" value can be left as is. Interestingly, just removing the "Ref Sort" value causes the application to fatally crash with an "Error importing XML" message, followed by a "Powerbuilder application execution error" message, and changing "Ref Sort" to (for example) "id" causes the same crash.

    I don't know if storing the id value will help or hinder you in writing the list or extraction, but I can't find another workaround at the moment...

Reply
  • $organization

    Hi Jess.

    I just tried this out in our test system (v 12.5.1) while running a trace. The reason for the error is that the constructed SQL to return your values reads:

    SELECT key_value, key_value FROM T_KWCODED_VALUES WHERE keyword_no = 595 ORDER BY key_value

    SQL doesn't know which key_value in the SELECT to order by as both fields are named the same. I tried aliasing the description field as v2 ("Ref Desccol" value is "key_value v2") but the SQL generated was as follows:

    SELECT key_value AS Id, key_value v2 AS Description FROM T_KWCODED_VALUES WHERE keyword_no = 16

    so the app knows to create an alias, but doesn't seem to quite do it right (a bug?)

    The only solution I've found at the moment is to change the "Ref Idcol" value to "id" - the "Ref Sort" value can be left as is. Interestingly, just removing the "Ref Sort" value causes the application to fatally crash with an "Error importing XML" message, followed by a "Powerbuilder application execution error" message, and changing "Ref Sort" to (for example) "id" causes the same crash.

    I don't know if storing the id value will help or hinder you in writing the list or extraction, but I can't find another workaround at the moment...

Children