I have a content type ProductPage that has a field productID. The productID field is a single drop down list type field, that uses an XML Application Lookup Query for the Choices.
The XML Application Lookup Query reads from a database table Product. It reads three columns from the table: ProductName, ProductID, CMSID.
If I set up the lookup with ProductName as the PSXDisplayText and ProductID as the Value, I can successfully go into Content Explorer and choose New Item -> ProductPage, and it will open the window to create the content item.
If instead I set up the lookup with ProductName as the PSXDisplayText and CMSID as the Value, when I go into Content Explorer and choose New Item -> ProductPage, the pop up window comes up but never loads - it gets a Status 500 error page from IE. However, if I use the test URL copied from the Request Properties window in the workbench, the Lookup appears to be working and returning a valid set of values.
The only difference I can see between ProductID and CMSID is that ProductID does not allow nulls and CMSID does allow nulls. Both columns are type int in the database.
Would a column that allows null cause the lookup to timeout or otherwise cause the window not to load in the Content Explorer? If so, what can I do to fix this? I tried setting up a WHERE clause on my lookup to only include records where CMSID is not null, but it doesn’t seem to have changed anything.
For anyone else who runs into this problem: It took a lot of trial and error, but it finally turns out that basically whatever column you use for the Value part of a Lookup that you’re going to use to populate a drop down list, that column has to have a Unique constraint on it in the database. Logically it makes sense once you know that’s what’s going on, but it’d be really nice if that was included in the documentation somewhere. Maybe it is and I just didn’t see it, but I don’t think so.
I discussed your situation with our chief architect, and the unique constraint should not be required, and may be a bug. Duplicate entries might cause a failure as well, which would explain why a column allowing nulls may have caused the behavior you reported. Failing to handle that problem gracefully would also be a bug.
I’m going to try reproducing the behavior you reported. To be sure I reproduce it accurately, I’ll summarize my understanding:
The lookup table consists of two columns, one with a datatype of string, the other with a data type of int. The int column allows nulls and does not have a uniqueness constraint. (I’m omitting the third column because the issue seems to be the configuration of the second column.)
This table provides the data for an XML lookup query that supports the choices for a sys_DropDownSingle contol. Using this configuration, the Content Editor does not load, and the CM Server returns a status 500 error.
If we add a uniqueness constraint to the int column of the lookup table, the Content Item loads as expected and the Content Item can be created successfully.
I created a table with no uniqueness constraint on the value field and used it in a lookup application for a Content Editor. When the content of all Value nodes in the returned XML document was unique, the Content Editor worked correctly. It also worked correctly even if multiple PSXDisplayText nodes contained the same content but the Value nodes contained different content.
If the lookup application used for a control returns a document that includes duplicate content in the Value nodes (including duplicate null values), the system should return the following error: An unexpected exception occurred. The reason was: Choice entries with duplicate values found for a control.
The uniqueness constraint on the table column itself is not necessary. The requirement is that the output of the application not return duplicate content in the Value nodes. I can imagine cases where a table might contain duplicate values returned in different situations (e.g., different labels for different Locales). What is necessary is that all Value nodes in the returned XML document contain different content.
I was not able to reproduce the hung Content Editor. If anyone that has encountered this behavior can post a reproducible case, I will test it and log a bug if I can reproduce it. If no reproducible case is available, I suggest that the next time someone encounters it, they contact Tech Support for further investigation.