MultiRow per Cell and Formula

[Vivek]

Clay,

This is probably me last question and I would request you to answer this question too. You have already answered my other questions.

Scenario/Question: I will try to explain in detail.

I want to have two grids on a form (lets name them Topgrid and Bottomgrid). My bottom grid is display only. User can’t not change the cell value, it is based on a formula using Topgrid.

In the Topgrid I want to use the functionality of MultiRow per cell (each row has basically multiple rows in a cell). I want to have cell from each column have same number of rows.

|-----------------------------------------------|
|Row1—Name | Row1—Name |
|Row2—Id | Row2—Id |
|Row3—Per Hour Salary | Row3— Per Hour Salary |
|-----------------------------------------------|
|Mr. Joan | Mr. Juil |
|6887 | 6887 |
|34.00 | 45.00 |
|-----------------------------------------------|
|Mr. Kamal | Mr. Umak |
|6887 | 6887 |
|33.00 | 55.34 |
|-----------------------------------------------|

So if I change each cell has three rows and if change anything on any of rows within a cell it changes the value in corresponding cell in Bottomgrid.

List Of Questiona:

Can I make whole grid read-only?

Can be achieve this multirow functionally as I described above?

Is it possible to have this multirow functionally based on some chaeckboxes, like how many or what are the things (information) user wants to see. They select 2,3,4… number of checkbox and we display as many rows in a cell.

Can be used formula for Bottongrid based on vales of each different rows in a cell, like if you change one row in a cell ant it changes the value in corresponding cell in read-only grid.

I know it would be too much to ask for a sample, so if have then it is okay.

Please do reply.
VivekS

[Administrator]

In general, you can set a whole grid readonly by setting grid.Readonly = true. But there are other ways to do it too. (grid.TableStyle.ReadOnly = true or grid.TableStyle.CellType = "Static" or ???).

For the TopGrid, I would probably try to use a GridDataBoundGrid, and use the technique shown in Syncfusion\Essential Suite\Grid\Samples\DataBound\MultiRowRecord. You can use change the LayoutColumns based on checkboxes if you want. You can listen to the top grid''s CurrentCellValidating, and based on the topGrid.CurrentCell.RowIndex, topGrid.CurrentCell.ColIndex, you can explicitly change the values in teh bottom grid. To get the new value in teh topgrid, use topGrid.CurrentCell.Renderer.ControlText.

I am reaonably sure what you want to do can be done, though I am not sure exactly what problems you might run into as you try to do it.

[Vivek]

Thanks clay,

In bottom Grid all I have is Formulas based on the Topgrid. So how would I set formula using individual values from a cell (I am assuming that there are 3 rows per cell).

Lets try put in Example:

Let assume a cell at [3,2] at Topgrid and BottomGrid. Cell at Bottomgrid has formula based on the three different values in Topgrid’s cell.

Cell[3,2] at TopGrid has following three valuses (in three rows)

Price—row1
Discount—row2
Units---row3

Cell[3,2] at bottomgris should use following calculation

(Price * discount * Units)/100

Now if I change anything in topgrid it should change the bottomgrid cell too.

How would I create the formula, basically I am looking for syntax for referring different rows within a cell and use in formula for some other grid.

Please do reply!

thanks

>In general, you can set a whole grid readonly by setting grid.Readonly = true. But there are other ways to do it too. (grid.TableStyle.ReadOnly = true or grid.TableStyle.CellType = "Static" or ???).
>
>For the TopGrid, I would probably try to use a GridDataBoundGrid, and use the technique shown in Syncfusion\Essential Suite\Grid\Samples\DataBound\MultiRowRecord. You can use change the LayoutColumns based on checkboxes if you want. You can listen to the top grid''s CurrentCellValidating, and based on the topGrid.CurrentCell.RowIndex, topGrid.CurrentCell.ColIndex, you can explicitly change the values in teh bottom grid. To get the new value in teh topgrid, use topGrid.CurrentCell.Renderer.ControlText.
>
>I am reaonably sure what you want to do can be done, though I am not sure exactly what problems you might run into as you try to do it.
>
>

[Administrator]

You probably do not want to try to use formula cells in a GridDataBoundGrid. There is additional work that you have to do that can be avoided in you restrict your use of formula cells to GridControls that hold the data.

You will have to handle this is code. The grid will not handle this for you, automatically mapping differnt lines in the top grid to formauls in a the bottom grid. When you get the cell value from the cell in the top grid, you would get ''Price\r\nDiscount\r\nUnits''. You would then have to parse this string to pick out the values you want, and then explicitly use them in the bottom grid. You would not use formulas to do this.

Alternatively, you could explicitly put each one of the values Price, Discount, and units in different cells in a GridControl. Then you could use formula cells in the bottom grid to track things.

[Vivek]

Thanks Clay,

So what I understood so far is following:

I have to use GridDataBoundGrid class for my TopGrid (which hold multiple rows in a cell) and I should use the GridControls class for my BottomGrid. My BottomGrid is a very simple read-only grid.

Now let me ask this,
Q.1
Are you suggesting me that we can’t use GridDataBoundGrid class as base for a cell in a different grid for a cell of type “Formula”

Are you trying to say above scenario would not work because I can’t create cell of type “Formula” based from a GridDataBoundGrid’s cell? So you mean it has to be GridControls class which can be used as base for cell of type “Formula”.

Q.2
Is there any event which I should handle whenever there is any change in cell’s value in Topgrid, so that I can set the value in Bottomgrid’s cell?

Otherwise advise me how should I proceed or what is the best way of achieving the desired functionality, if I have to do this?

Thanks
VivekS

[Vivek]

Clay Following is what I am looking for:

Desired Functionality:

A GridDataBoundGrid grid as TopGrid because I want to display multiple rows in a cell. These multiple rows in a cell are like three different type of information. If we say each grid display “Employee” records as a row in grid then each cell in that row has following three values in form of three row
Price
Discount
Units

A GridControls grid as BottomGrid. In bottom grid each cell is Formula cell and it is based on each corresponding cell in Topgrid.

So there is no Formula cell in Topgrid. But each cell in bottomgrid is formula cell.

What kind of grid (GridDataBoundGrid VS GridControls ) should i use for my case?

Please advise me how should I achieve this. do you think that whatever i am trying to do can be done, if yes then what would be the best way.

I will be checking my mail al night. i have to give this report to upper level, i need to make sure that it can be done.

Thanks

>Thanks Clay,
>
>So what I understood so far is following:
>
>I have to use GridDataBoundGrid class for my TopGrid (which hold multiple rows in a cell) and I should use the GridControls class for my BottomGrid. My BottomGrid is a very simple read-only grid.
>
>Now let me ask this,
>Q.1
>Are you suggesting me that we can’t use GridDataBoundGrid class as base for a cell in a different grid for a cell of type “Formula”
>
>Are you trying to say above scenario would not work because I can’t create cell of type “Formula” based from a GridDataBoundGrid’s cell? So you mean it has to be GridControls class which can be used as base for cell of type “Formula”.
>
>Q.2
>Is there any event which I should handle whenever there is any change in cell’s value in Topgrid, so that I can set the value in Bottomgrid’s cell?
>
>Otherwise advise me how should I proceed or what is the best way of achieving the desired functionality, if I have to do this?
>
>Thanks
>VivekS
>
>
>

[Vivek]

Please find the attached file which explain TopGrid little more. > >Clay Following is what I am looking for: > >Desired Functionality: > >A GridDataBoundGrid grid as TopGrid because I want to display multiple rows in a cell. These multiple rows in a cell are like three different type of information. If we say each grid display “Employee” records as a row in grid then each cell in that row has following three values in form of three row >Price >Discount >Units > >A GridControls grid as BottomGrid. In bottom grid each cell is Formula cell and it is based on each corresponding cell in Topgrid. > >So there is no Formula cell in Topgrid. But each cell in bottomgrid is formula cell. > >What kind of grid (GridDataBoundGrid VS GridControls ) should i use for my case? > >Please advise me how should I achieve this. do you think that whatever i am trying to do can be done, if yes then what would be the best way. > >I will be checking my mail al night. i have to give this report to upper level, i need to make sure that it can be done. > >Thanks > > > >>Thanks Clay, >> >>So what I understood so far is following: >> >>I have to use GridDataBoundGrid class for my TopGrid (which hold multiple rows in a cell) and I should use the GridControls class for my BottomGrid. My BottomGrid is a very simple read-only grid. >> >>Now let me ask this, >>Q.1 >>Are you suggesting me that we can’t use GridDataBoundGrid class as base for a cell in a different grid for a cell of type “Formula” >> >>Are you trying to say above scenario would not work because I can’t create cell of type “Formula” based from a GridDataBoundGrid’s cell? So you mean it has to be GridControls class which can be used as base for cell of type “Formula”. >> >>Q.2 >>Is there any event which I should handle whenever there is any change in cell’s value in Topgrid, so that I can set the value in Bottomgrid’s cell? >> >>Otherwise advise me how should I proceed or what is the best way of achieving the desired functionality, if I have to do this? >> >>Thanks >>VivekS >> >> >> topgrid_7339.zip topgrid_5318.zip

[Administrator]

You did not say how the data is organized. For example, are all 12 pieces of data for Mr. X from the same record in a particular DataTable (meaning you have a fields like JanPrice, FebPrice, MarPrice, JanUnits, FebUnits, MarUnits, etc. all in the same DataTable, or do you have to get the prices for a price DataTable and the units from a Units Datatable, etc., or is teh data organized in some other way?

Do you want to edit this data directly in the grid?

There are a whole series of questions like this that would affect how I would try to implement this task. So far from what I know, I think your requirements can be met, but without actually spending the time to do the work, I cannot layout a step by step tasklist that will design the work for you. A lot of questions would have to be answered and some unexpected problems would have to be resolved.

Knowing very little about your specifics, here is what I would try as a first attempt. For the top grid, use a GridDataBoundGrid. Have a single (extended-height) row for each employee. Build a DataTable (based on your checkboxes) where each column is a month. In the values for each employee row, have a single string with the data items you want to see separated by \r\n. This will give you the multirow look you want. It also simplifies things as there will only be a single grid row no matter how items of data you are showing. (As a second approach that would take more time, you could actually have a grid as the cell control for these cells. That way, the editing would be done on a item by item basis. This would take more time to set up, but is doable. See the In Depth\GridInCells sample.)

For the bottom grid, I would use a GridControl. If you want to use formula cells, then you would need to add custom formula(s) (see QuickStart\FormulaGrid to see a sample). The custom formula would know how to pick out the particular value you want to use from the \r\n delimited string values in the GridDataBoundGrid, and calculate the things you want to see.

I don''t think there is a problem getting this done, but it would require extending the default behavior of our grids. If you tried to do this and could not get it done, Syncfusion offers consulting services that could do this for you.

[Vivek]

I really appreciate your effort and time you are putting here.

To answer two of your questions

Q1
Yes I can get my data organized in a datatable wher each row has 12 (months) * 3 (price, discount, unit) = 36 values. (fields like JanPrice, FebPrice, MarPrice, JanUnits, FebUnits, MarUnits… )

Q2
Yes I want to edit data in each row of a cell (because each cell is multiRow cell).

Would you still recommend setting values in a cell as string with the data items separated by \r\n? Don’t you think it would still let me edit each value in different row?

I know I am bothering too much but please help me.

Thanks
VivekS

[Administrator]

Go ahead and set up the top grid using the single row with 3 lines of text in the single cell. Then you will see how it behaves.

I suspect that as you edit, say the second line, everything will be OK but you would be able to delete things so that the 3rd line would become the 2 line (and so on). Now, you can handle CurrentCellValidateString to prevent this from happening. This is the kind of thing I was referring to when I said there would be problems that you would have to resolve.

Once you get the top grid working the way you want, then start on the bottom grid. If you get stuck doing something, then you can post a specific question here or submit a Direct Trac support incident on the problem, and we will try to help.

[Vivek]

Hi Clay,

My boss would like to talk to some sale person regarding buy grid and talk about some references. Could you please provide the name and number for contact sales person?

Last thing, could you please provide some help on custom formula involving parsing a string?

Thanks

[Administrator]

The Sales contact information can be found at this link. Depending on where you are located, your call will be forwarded to a sales rep that handles your region.

http://www.syncfusion.com/Sales/contactsales.aspx

One simple way to parse a delimited string is to use the string.Split method.

//sample string to be parsed
string s = "Price1\r\nCode1\r\nItem1\r\n";

string[] parsed = s.Split(''\r'');
parsed[0] = parsed[0].Trim(); //strip off the \n
parsed[1] = parsed[1].Trim(); //strip off the \n
parsed[2] = parsed[2].Trim(); //strip off the \n
Console.WriteLine("FirstValue-{0}", parsed[0]);
Console.WriteLine("SecondValue-{0}", parsed[1]);
Console.WriteLine("ThirdValue-{0}", parsed[2]);