I am having  a problem displaying fields in a Devex Grid. The only fields that will diplay, are the Original Fields from my Main Table BO

I want to select out different fields from a few joined Tables and display them in  Grid. For this purpose , I have set up a Dummy BO, with just two fields, and I use this as a sort of Repository to hold the fields I select out. So say my Dummy Table (And BuisnessObject) has just 2 fields D01_pk and D01_Val1.

The only purpose of the Dummy Table is to be able to join the fields and end up with one Table, but the only fields I can get to display are the fields from the original Source BO (In my case just 2 fields). All other fields just show blanks, even though the Business Object populated with the correct count of rows.

I have the following code in the Load of the form (I cannot assign the BBS to a business object on the form parameters as I have not yet created the BusinessObject, this is why I am setting the BusinessObject to the BBS in the Form Load)

Kernel_BOLibrary.XBO myBO = new XBO();        // My Dummy Business Object whcih I use to JOin other Tbales into
SqlCommand comm = new SqlCommand();
comm.CommandText = "Select 1 as D01_PK,'XXX' as D01_Val1,  MyField1,MyField2,MyField3 from TABL1 join TABL2 on X=Y ";
myBO.Clear();
myBO.FillDataTable(comm);
bbsPost.BusinessObject = myBO;
gridPost.DataSource = bbsPost;

So in the Grid, DO1_Pk and D01_Val1 display fine, but I cannot get the other fields, which I have joined (MyField1,MyField2,Myfield3) to display

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 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.

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

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, 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)

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

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