Hi,

I'm trying to figure out the best way to handle descriptive lookup values associated with foreign keys on a business object.  I've read some of the past discussions (e.g. http://forum.strataframe.net/Topic12507-7-1.aspx?Highlight=RowPopulating+browse) and it is looking like custom field properties may be the way to go in many cases. 

For example, I have a staffBO for all teachers in a school district.  Each record has an FK_School foreign key pointing to the teacher's school.  On my StaffMaintenanceForm Browse Dialog I want the school title (not FK) to show in the results column.  So I added a cSchoolTitle custom field to StaffBO as shown below (using a call to SchoolBO.FillByPrimaryKey()). 

This works fine, but I'm concerned about the speed and network demands of repeatedly doing a FillByPrimaryKey() each time a staff record is retrieved.  I'm also trying to better understand what is going on behind the scenes, so please bear with me...

Does the FillByPrimaryKey() actually query the backend School table for each staff record?... or is some fancy buffering going on?  I know doing this for a few hundred staff records may be trivial, but other tables with similar lookup requirements may have 10,000 records.

Is there a better way to just pull the entire School table into a local BO one time (using FillAll()) and look up the school titles there?  Would this alternate approach be preferable for small lookup tables?

I've considered using a multi-table BO (based on SQL views that have joins to needed lookup tables) but was stymied by how to make these updatable.

Thanks in advance,

Larry

Trent,

This is a very nice way to solve the lookup problem efficiently, with one trip to the server no matter how many staff records are pulled.  It is like using a multi-table view for my BO (joining staff and schools), but without the headache of dealing with an updatable SQL view. 

Thanks very much,

Larry

Glad to help

Trent, I'm getting back to testing this approach to custom field properties and have a few questions/problems.  I added a new field to a BO.Fill method and added a custom property as below in my ClaimBO:

Unfortunately I get an error when adding a new record on my ClaimSFMaintentanceForm after loading the form with my ClaimBO.FillAll() method:

KeyNotFoundException
  The specified key was not present in the dictionary.

Source     : MicroFour StrataFrame Base

Stack Trace:
   at MicroFour.StrataFrame.Data.DataBasics.GetFieldFromDictionaryOrdinalKey[T](Dictionary`2 dict, String key)
   at MicroFour.StrataFrame.Data.DataBasics.InitializeNewRow(DataRow NewDataRow, StringCollection IgnoredFields, Dictionary`2 FieldNativeTypes)
   at MicroFour.StrataFrame.Business.BusinessLayer.NewRow()
   at MicroFour.StrataFrame.Business.BusinessLayer.Add(Boolean CheckSecurity)
   at MicroFour.StrataFrame.UI.Windows.Forms.BaseForm.Add(Boolean CheckSecurity)
  ....

If I don't load the ClaimSFMaintanenceForm with any records and press New, I get a similar error:

BusinessLayerException
  An error occurred while refreshing the data from field 'ClaimBO.ses_Date' to property 'Text' on control 'Textbox5.'
TargetInvocationException
  Exception has been thrown by the target of an invocation.
ArgumentException
  Column 'ses_Date' does not belong to table Claim.

Source     : MicroFour StrataFrame Business

Stack Trace:
   at System.Data.DataRow.GetDataColumn(String columnName)
   at System.Data.DataRow.get_Item(String columnName)
   at TBNet.ClaimBO.get_ses_Date() in E:\TBNet\TBNet\BOs\ClaimBO.vb:line 85
   at System.RuntimeMethodHandle._InvokeMethodFast(Object target, Object[] arguments, SignatureStruct& sig, MethodAttributes methodAttributes, RuntimeTypeHandle typeOwner)
 ....

The second error says "ses_Date does not belong to table Claim"... which is true unless my FillAll() method is used to fill the underlying table.   I assume this has something to do the with "dictionary" mentioned in the first error... but I need some additional guidance.  My impression is that I need to do more to fully inform the default SF methods / engine about this field that doesn't exist on the source Claim table in the database.

TIA,

Larry

Hi Larry,

I am not at a computer with SF installed, but in the BO instance in your form, there is a property where you can tell which fields should be ignored for update.  Sorry I don't remember the name of the property either, but that may be what you need.

Edhy Rijo

Actually the problem here may be that when you perform a query the ses_date field is there.  But if you have not performed a query and just add a new record, the ses_date field is not within the column collection.  In this case, you need to manually add the column to the CurrentDataTAble.COlumns collection so that your code can reference this field without error.

Since you are getting this error, I would imagine that you are pulling from the strong-typed field property either via binding or in code without having performed a query...and thus the column doesn't exist within the BO.  I have a shared method in our medical software that I use called EnsureColumnExists that I will place in different places depending on what I am trying to accomplish that accepts a BO, field name, and field type and then makes sure that the column always exists so that this error doesn't creep up on you.

Larry,

Here you can find the shared method Trent is talking about: http://forum.strataframe.net/FindPost16437.aspx

Edhy Rijo

Thanks, Edhy

Trent,

Thanks for the explanation and Edhy, thanks for the reference.  I'll give it a try.

Larry