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

Hi Govinda,

I don't fully understand your question, but if you are creating an Order and want to update the OrderStatusTypeID you can use a combobox to allow the user to select the Status, or if you know that the status for new orders will be 1 or 2 simply use the Orders business object SetDefaultValues to assign the default value.

Edhy Rijo

Thanks for the reply Edhy.



I guess I'm trying to find a way not to hard-code IDs. But I think that's what I should do anyway rather than worry about IDs changing down the line (ie Deployment).

If you don't know the description of the StatusID, then don't assign any as a default, simply mark that field as a Required field in the BO, and use the combobox to allow user to select whatever ID they want to use.

If this feature is a most, later on, you can have a Setup Option form which will allow the end user to select Default Values for some fields and then you can use those values in the Setup Option BO as default for your regular BOs.

Edhy Rijo

I totally agree with Edhy. I'll add that if you decide to not set a default, I'd return/set an alternate value on NULL (likely zero, as this will work well with the required values check). Then when you get to the UI, you'll likely use a combo and the TopMostValue is your friend. You set it up with a space (or some text indicating they need to choose value) and zero (the alternate value on null) and when the user opens the form, they'll see that the value isn't set.

I do know what the default values name should be, but I want to get the value OrderStatusType.Id from the database rather than hardcoding it.



So I was going to do something like this...

For child items that may change after deployment, I third Edhy's idea of not setting a default, or making the default soft so that the user can decide which of the dynamic values should be used.

If you are expecting a certain value to be in that table, however, then you could either:

A) Add an IsUserModifiable boolean field to your child table. Any values that you distribute, set that to false, and respect that value on the maitnenance form(s) so that you can rely on your destributed values to be present and predictable. This would be best in situations where you are distributing a "base" set of values that you will reference in code (i.e. for defaults), but the user can then go and add there own values in addition to the base set.

B) If you already know all of the possible values and the end user will not add to or edit those values, then use an enumeration rather than a child table. Just use an integer between 1-x, and define an enumeration to specify what each value is.

Hope it helps

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.