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

FillByPrimaryKey will just fill a BOs datatable with a single row, the hard work is done by the SQL server which with a primary key can go straight to the row very efficiently.

You could always make the schoolbo global, populate it once with all records, and then instead of FillByPrimaryKey use NavigateToPrimaryKey, but this is only going to be better for small tables where every row is likely to be accessed many times.

Peter

This is exactly the kind of alternate approach I was wondering about.  How do you make a BO global?

Larry

To make a BO Global, there are a couple of options, drop it on your form, or declare it in the global section of your form. You then have to populate it, either within the bo itself in the parent form loading event handler, or within the form in the form loading event handler.

Another thing to note is that if you are just going to use a bo for a lookup, you only need the Primary key, and whatever you want to display, anyhting else just uses extra memory for no good purpose. So you might want a lookup BO that is different to your maintenance/update/transactional BO.

Peter

I will try this.  Thinking about it, my lookup based on the FillByPrimaryKey() is probably more than efficient enough.  It also has the benefit of immediately incorporating any new or edited lookup records that some other user may have created while the original user was active.  (The retrieved global BO would not "know" about these unless it was requeried). 

Larry

Filling the entire row is not a good idea in this scenario.  You can take two approaches.  First, I would put the load on SQL Server and include the field in the query versus making another trip to the server.  The fewer trips the better, and this can always be included in your query if necessary:

Using this, you can create a custom property in the BO that uses the TitleName (which will already be retrieved from the initial query:

The other way, which you may need to do within the BrowseResults, is to use the PopulatedThroughEvent and RowPopulating event (lots of samples and docs on this).  It is the same as a ListView.  In this case, you would want to use a Scalar method to retrieve the value...MUCH faster than pulling a full query as there is no TDS (tabular data stream) when pulling back a scalar method:

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.