Way to go Peter  

I spotted this one this morning and as I was reading down through it, that was going to be my suggestion, but you beat me to it

Yes, for each column in the grid that is an FK, configure the column to be a combo box column and populate it from the lookup.  Problem solved.

Useful?  Peter, you have made my day!! 

I just tried it out.  It is a solution that is quite simple and full of finesse!

THANKS A TON!  I owe you a cup of coffee!! 

G'day

We use devexpress and have numerous instances of grids where we display a table with numerous foreign keys which we want display as the description related to the foreign key. We simply put a gridlookupedit in the grid column  using the description (e.g. colStatusDesc) as the display member and the the FK as the Value Member (e.g. colStatus). If we don't want it editable/changeable we simply set the flag to make the column in the grid read only (or something like that).

We populate the lookup from BO through a business binding source (if more than one FK on the grid one BO instance and BBS for each lookup). You could develop a BO especially for each of these lookups (which we did until recently) but we have a few generic BOs that handle this kind of Task, boLookupTable, boLookupEnum and boLookupCol (these eliminated about 100 BOs from our system). The boLookupTable for example has a few properties that use the system.componentmodel attributes to make them settable at design time in the Visual Studio Properties window, which include the name of the stored procedure to populate it with, whether it should be populated at "parent form loading", and a couple of others to do with filtering. The boLookupEnum takes as an attribute the name of the enum that should be used to populate it. All of these have just two columns PK and Name, and are mapped with the Business Object Mapper to a generic view used for just this purpose.

When we have a grid with a FK we drop a boLookupTable instance onto the form, set the properties, add a BBS, configure a lookup on the column, and we're done.

So in your instance the stored procedure could pull together whatever description you want

This has worked well for us, hope you find the idea useful.

Peter

Hmmm...Can't say too much about the grid method, as I'm still a newbie with grids (that's about to change...)



However, this approach is what I use to off load work to the db server and limit the number of calls to the db. My limiting factor is usually network latency, as 95% of my users are remote (I'm using Enterprise Server which was a bloody Godsend), so < db calls is good. To increase speed even more, I'd use either a view or a sproc. I guess what I'm saying is that if you need the data for a bunch of FKs, it has to be gotten at some point. This method gets what you need with one call to db, using db server optimizations as needed to increase speed.



Another option might be to cache the FK data, if it doesn't change much, then you'd just get it once. You could still use custom properties in that case, just pull from the cache.



Yep, I spend waaaaaay too much time attempting to decide which of the 100 ways there is to do something that might be the best I hope there are some more responses, as these posts are always great for learning more/better ways of doing things.

Thanks, Greg!

I would entertain this approach for a very small tabular footprint; however, one of my tables is quite busy with foreign keys, so this approach doesn't appeal all too much.  On the other hand, I was researching the grid way of doing it...that is, going the unbound column route with binding the descriptions in the "as you go" manner.  Thinking about this leads me to another impass.  Should I spend the time fleshing out the BO (as you describe) or spend the time working on the grid?  The decision must be based on how much time I will spend at either task.  I can see that either approach will be time consuming.

Decisions, decisions...

One thing you might try is making custom properties for all the foreign key extra info (like status desc). Here's how I've done something like this (this is psuedo code, I've left out the necessary attributes cause I can't remember them off the top of my head, and I'm doing it in C#, which I don't normally code in, ect...hopefully you'll get the idea):

I am using a view to populate a grid via the business binding source.  All is well.

Now, I want to populate a grid from a BO that has custom properties.  No big deal.  All is well there, too.

Except, any foreign key fields will not pull data from the appropriate table (BO) reference.  This has probably been asked before, but after reading about a dozen threads on the issue, I am no further to getting a solution than when I started.  Thus, I post.

Here goes...

I have a table with, say, three fields:  colIndex (PK), colDesc, colStatus (FK).
In addition, as declared in the BO, I have a custom property: myCustomProp.

I want to fill a grid with the following:  colDesc, colStatusDesc, myCustomProp.

Please note that colStatusDesc is a field from the foreign table that is related to the main table. 

I cannot create a view, since the custom properties do not exist within the RDBMS.  If I just use the base BO, I do not have access to the foreign table's description field.  Or, do I?

Now, this is a very simplistic example.  My actual grid has several of these foreign key thingies (length unit, inventory unit, statuses, divisions, etc.; also several custom properties, too).  I cannot have parent-child BOs handling all of these relationships.  At least, I do not know of a way in SF to do this.

So, what do I do?

Thanks for any help on this!
Bill