At first, I would say that behavior of LightSwitch I will show you today is a bug. I haven’t investigated that much about it in Microsoft Connect. It happens in one edge case I will describe today.
Say we have data model like this :
We have entity that has reference to another entity. Employee has reference to Status. This second Status table has set for some business requirement that his Summary property must be set to value that can repeat. I know this makes it hard to tell which value from all possible was picked, but say it is so. Another reason is that summary property is displayed as selected value in grid.
OK, so far so good. Here comes trouble.
This is, how the screen would look like. If we select first 1200 and save it, data would be saved correctly. But if I would select second 1200, update to database will be triggered with ID of first (I know that, because I spent some time with investigating on this error and first thing that came to my mind – it’s not you code, debug the result, SQL 🙂 ). From my experience, when you have multiple same values, the first from top will be selected (like if you would search a list and take first one) and this value will be used to update record.
OK, what we can do about it.
There are two possibilities :
- Choose some unique value column as Summary Property of your entity (but you should be damn sure there will be no duplicates, maybe you could put a unique constraint on that column in DB or so, because some user can put same value there),
- Create a computed column that will be unique (I’m creating it from two other columns), like I did here
You have to also set it as Summary Property.
Then UI could look like this :
With this approach Lightswitch app will work as expected. Hopefully. 🙂
As always, both solutions to elaborate on can be downloaded here :
https://skydrive.live.com/redir.aspx?cid=78a5783de37d2ebe&resid=78A5783DE37D2EBE!1788&parid=78A5783DE37D2EBE!1779 and look for AutoCompleteProblem.zip 66MB file.
Hope this helps.