I have created a custom NewForm in SP to filter a lookup field using my SLAM tables and this works fine. In my SqlDataSource retrieve ListRelativeID and a description field from my SLAM setup to use as the DataValueField and DataTextField. This adds the item to the SP list with selected lookup field item, so far so good.
When it comes to customising the EditForm, I am having trouble. If I use the same SQL query to retrieve the available values in the lookup field and assign the ListRelativeID and description fields to the DDL, when I click Ok to commit the edit, I
get an error:
Invalid data has been used to update the list item. The field you are trying to update may be read only.
If I look at the source code for the page (see below), I see that the entries retrieved by SQL have the ListRelativeID in the value field but the already selected entry has the xml inner text value in the value field. If I attempt to commit the edit,
I get the error above. If I change the lookup field to another item in the list, it saves correctly.
<option selected="selected" value="20">07795 234XXX</option>
<option value="45">07795 370XXX</option>
<option value="101">07884 474XXX</option>
<option value="112">07897 352XXX</option>
<option value="70">07827 016XXX</option>
<option value="1">07919 576XXX</option>
<option value="40">07795 370XXX</option>
Should I be using the ListRelativeID as my value field or the same field used in DataTextField?
If I should use ListRelativeID (which seems to work for the NewForm) how do I use selectedvalue to show the correct entry in the lookup when I edit the list item? Currently it defaults to the entry at the top of the list.
If I should use the description field, is it correct that it differs from the NewForm which only seems to save entries when using the ListRelativeID?