Hi,



Orders

Id

OrderDate

ItemId

OrderStatusTypeId



OrderStatusTypes

Id

Name



Can someone explain a decent way to handle setting a default value for a field that contains the ID of a value in a lookup table?



One way I was considering was in the SetDefaultValues() event handler, creating a temporary LookupBO and using that to get the value based on the name of the value I want as the default.



Or would it be better to do an "this.ExecuteScalar(...)" to get the value I need?



Thank You

Govinda

Govinda,



To answer your question, yes, the code posted would work to get the ID based on the name and it will be nice and fact (assuming you've indexed the name column in your OrderStatusTypes table).



If you did it this way, I'd likely move the actual retrieval to the OrderStatusTypesBO, using a method something like GetIDbyName(String name).



However, I think we're all a little confused as to why you'd ever know for sure the Name of a record in this table without knowing the ID. Probably just a lack of understanding on our part. To reiterate the approaches suggested:



- Use an Enum. If the order statuses are used for logic that your coding and any changes to the status break things, use an enum. It makes things strongly typed and easy to find errors. Default set via enum.

- Use table, deploy required values that aren't modifiable by user. This might be the best approach if some of the values are required by your app but others aren't. This is were the additional IsUserModifiable column would come in. Default set via the ID of a required item, since you are deploying the item.

- Use table, all values set by user. Allow user to select the default value and store that default in some user setting store (db table, xml file, registry, etc). Use the user selected default value.

- Use table, all values set by user. Don't set a default value, but make it required, use a top most item in UI to indicate no selection is made.



As I typed this, I realized there is another situation that could require a solution like you are suggesting:

- An existing app is being modified to require a one or more new OrderStatusTypes. This new, required order status type will be deployed, but the ID will be unknown because existing deployments will have any number of existing order status types. In that case, you'd need to use the approach you're suggesting and look up the ID.



In this last case, I'd likely use some sort of caching scheme so I only needed to lookup the ID once though. Likely I'd do this in the OrderStatusTypeBO via a static method, look it up using ExecuteScalar the first time, store in static property of BO, then return the cached value on any request after the first one.



Hope that helps!

Thank you all for your very helpful responses.



This is a new app and I was probably over thinking / over engineering. I was trying to be flexible and I think I pulled something



The app is going to have a set of preconfigured types we'll be aware of and the user will not be able to create new ones. So I think the best solution for me here is to use the 2nd approach that Greg reiterated. That should be sufficient for me to set a default value that will probably never change.



I'll also create an enum that will mirror the type IDs in the database in case the IDs change during development (like I change the order). I'll use that to set the default value and in queries and such.