Hi Trevor.

The approcah I use here is to have a 'Dummy' BO, with say just one field in it, that comes form a 'Dummy' table, so it might have one column .

Then if I need a BO populated with fields from Joined Tables, I just run a Join and indicate the Fields I need in the Select String. This ends up with lots of fields that are not in the Original BO, but unless you are writing back to tables, this does not seem to matter. (I tend to use these for enquiry type screens)

The pont I was making in the  thread is that Devexpress (which I now use for all my grids) does not seem to like Fields like this that are not taken from the originating table (if a BusinessBindingSource is the Source) .

If however, the Undrlying Datatable  is specified as the Source of the Grid, these fields are recognised ok by Devexpress

PMFJI,

" I have a Call to code that Creates a BO "

could you elaborate on the code that does this.

Thanks,

Trevor

I have this working now satisfactorily, as follows:

1. In the Load Event , and a 'Refresh' button, I have a Call to code that Creates a BO (Which includes a Few Joins to other Tables). I am using a Base Generic type Bo, which I will use for all instances like this where I need to join a few tables

2. I have a Devexpress grid set up, and in Code, set the DataSource of the Grid to the Underlying DataTable in the BO. Doing this, I can see all the fields in the DataTable

3. I then can use the BO.GetEnumeraable  to Process the data from the Grid  (Any changes done in the Grid are rfelected in the underlying DataTable, and also on the BO)

4. I am not using a BBS, but this has not caused any particular issues as all Grid Changes are reflected in the BO anyway.

Many thanks for all assistance

You are welcome Ger. 
I am currently working on final touches of an update, but there is always some time for the SF community.  I sent you a Private Message with some info, after that you can post your final decision here.

Edhy Rijo

Hi Edhy, many thanks for your comments, and if you have the time, I would like to flesh this out  a little more, as I want to end up doing things in a standard way.

Say I have 50 screens in my app (in the process of being designed right now) , and each screen will access possibly 10 tables.

Altogether, the App would use say 30 tables, whcih translates into 30 BusinessObjects. For a lot of my Forms, they are not really maintenance Forms, but Processing Type forms, where I want to display data from a number of joined tables, and if say a particular Row is ticked, do something

So, I would have the following Scenario:

Form1:  Select * from Table1 join Table2 join Table3

Form2:  Select * from Table4 join Table5 Join Table6

Form3:  Select * from Table7 Join Table8 Join Table9

etc for my 50 screens, so yes,  I do know the fields in advance ,and I will want each field from myselect to end up on a Grid in the Form

 Do I then have to set up 50 different Views ? I have read in a few places that setting up views may not be the most elegant way of doing things, partcicularly when the ONLY purpose of the view is to get over the limitiation that I cannot place all the fields from a BBS onto a Grid.

(Obviously for my day to day maintenance and Data Entry Forms, I use bog standard Business Objects and thet work well)

The problem is that you should be aware of the purpose of the BO, it is basically a wrapper of your data table columns and using the BOM you can control its properties, but the internal data table will have all the columns returned by the SELECT statement, when using a BBS, the SF BBS class is wired to recognized all the fields in the BO not in the data table, so if you need to create virtual fields to show up in the BO then you create Custom Field Properties (CFP), but the situation here is that you don't know what the columns would show up, even though, you must know then since there are hard coded in the SELECT, then the idea of create a view with all columns you would have in the SELECT is the way to go to keep things simpler and easier to maintain.
I don't know the details of your application or how this "unknown columns" fit within it, but unless you allow the user to create their own JOIN SELECT you must know ahead to hard code it, and if that is the case, then just create a view, if the same grid would use many different JOIN SELECT combination, then a programmatic approach would be needed to build this generic solution which involves looping the data table columns and create them on the fly into the grid and managing your own record pointer movement, you will still be able to use method of that BO to save, delete records as long as you defined the PK, but some other validations would be hard code since there would be no field properties for those unknown columns at runtime.

Edhy Rijo

HI Edhy.

I thought of doing something like this, but what I want to do is come up with a 'Strategy' of being able to 'Join' Tables, and present them in the Grid. I dont know at this stage what tables I will be joining, but I think there are going to be a lot of them, different joins in different screens.

The whole system has maybe 50 different tables, so I think I would probably need to create a view with every field from  every table, otherwise I suspect I will be going back amending the view all the time.....I am not sure if this is the right way to go now, so I'll go with the DataTable approach.

Being able to select out ANY field into a BO is great, but a bit restrictive if I cant then use the fields in a Grid

Hi Ger,

No problem I understand now. 
Well, since your need is to have some sort of generic solution, if you want to have it all working together and look natural, you can do the following:

1. Unless you are using SELECT * in your fill method.
2. Create an SQL View with all the fields expected by the grid. 
   
3. Create a BO and map it with BOM to the SQL Database View, this will give you the data schema expected.
4. Add  a BBS and your BO to your form, assign the BO based on the view to the BBS and use the BBS as the grid source.  This will give you all the columns in your view where you can setup captions and other properties.

If you really don't know the column names of your SELECT statement, then you would need to go with the data table route and use code to loop through all the columns of the data table and create columns with captions.

Edhy Rijo

Hi Edhy



Sorry I gave the wrong impression.... still cant see the columns if coming from a BBS, but can see them if DataSource is a DataTbale, but with a DataTable, column names have to be the same case as the select statement

Regards,

Gerard

Hi Trent. Thanks for replying.

My initial route was to go with the BBS, but I found that Selecting out and joining other tables and ending up with Columns in the Instantiated BO (that are not in the Original BO) meant that these columns were not visible in the Grid. This is the reason I went with the DataTable as the dataSource of the Devex Grid,, where I Could  see all the columns . Is this the expected behavior or am I doing something wrong