I have many requirements to be able to show on a ListView/Combobox the results from multiple joined tables. (e.g.Show Transactions but join in the Stock Description from a joined file). I have gone through a lot of the threads here to get hints , and have it working fine on a Combobox , but would like to ensure there are no Gotchas for any of the other list types , or maybe there is a better way.

 

Any comments would be helpful

Hi Gerard,



To show a field description you can do any of the following:

1) Add a Custom Field Property to your main BO that will call an Escalar method to get the description and you will use this new field whenever you need to show the description. This will work just fine as long as you are not showing many records since it will do a trip to the database to get the description for every field loaded in the BO, this is no good for reports but when showing few records it is acceptable.



1a) Using the above method, if you need to show many records you can always have a LookupBO in which you will have all the records to be look at for the description and instead of using an Excalar method you will simply LookupBO.Seek() to find the description record and update the Custom Field Property.



2) Another option for reporting or Browse Dialog is to use an SQL View with your JOIN condition and create a BO to map this view, then use your stored procedure to fill this view and this will be the fastest way to get your description fields.



I am sure there are many other ways but those are the ones I use. There are plenty of sample in the forum about using those methods, just search for "Custom Field Property" or "CFP" or "Browse Dialog" and have fun!

Edhy Rijo

I see no issues with this approach, assuming your just using the dummy BO to get data. If you want to use it to update/add/delete data, it will get...er...interesting.



I used similar techniques for similar reasons (though it took me two years to figure out I could just type in the column names!) and it has worked great.



The single table thing is really only important when writing back to the db. It's pretty common to read all kinds of data into a BO. There are definitely different ways to do this, but not sure about better.



The only thing you might consider is looking into the FillMultipleDataTables method of a BO (static). This loads multiple BOs with one trip to the db and is a good thing to look into if a form is loading slowly. I had a form that was making something like 30 trips to the db when loading, due to a bunch of combos/lists. Switched to using FillMultipleDataTables, so all 30 got loaded at once and that really improved performance.

As Greg pointed out the only issue is if you try to do an update, add or delete. The advantage of adding a dummy BO to the DDT is that all of the fields and property descriptors are generated for you. If you use scalar methods or add additional fields to your SELECT or stored procedures using a "real" BO, you have to add the custom properties and descriptors if you want to bind to them. However if you do an add, update, delete on a "real" BO, it will ignore the added fields. I've used both approaches. My only issue is that using the dummy BO results in "invalid" methods in the BO. At one time I was overriding the update, delete, and add methods to prevent any exception if they were called but I found it to be a lot of work. What I'd like to see is an additional BO class added to SF that was "read or lookup only" and had all of the add, delete, and update methods removed.

That's an excellent idea Larry. I second that suggestion!

Hi guys. Many thanks for all your comments which I have found very helpful.

The reson I thought that a Dummy BO would be the best of the available options to handle multiple joins is that it sems to be the most straightforward......just set up an additional Dummy BO with just one field...no need to set up views etc on the dataabase.

Also, these dummy BO would just be set up for lists (i.e. Read only) so unless I code something specifically in to 'Save' the Bo, there should be no possibility that attemps will be made to update the backend tables.....I will have a separate BO (the real BO) to do this.