Displaying Child Entity Data In A Grid

Question

I have a simple scenario consisting of a parent table, in this case containing bill of materials data, and a relationship to a child table containing part description information ( i.e. the parts library ). The relationship between the two tables is on the PartNumberID.

Displaying the bill-of-materials in a grid, I can see the part number as a summary field, and then the remaining fields from the parent table in the grid columns. I could of course change the part number to be a modal window picker etc.

I now want to display a bunch of fields from the part library table ( i.e. the part description, generic part reference, primary supplier etc. etc. ) in the same grid as the bill of materials data. It seems the only way I can do it is to add the parts library entity again, set this to be a columns layout group, and then mess about with the weighted column setting to try and get the columns to show some kind of relative width. Obviously the part description field needs to be longer than the generic part reference field.

Is there a better way to show child entity data and have the grid columns an appropriate width ?

All I'm really trying to do is have the data in columns in one grid.

I hope that made sense!

Thanks,

Nick

    

Answer 1

What I think you're after is to have two bands in the same Data Grid, one showing the Bill of Materials (BoM) rows and, for each row in that table, a separate band, under the BoM row, showing the parts for that BoM.

This is a common way of showing "shaped" data and lends itself to expanding and collapsing the parent rows to show or hide the child rows. As far as I know that is not a scenario that is supported by LightSwitch's Data Grid control. The closest you can get is to embed a whole child grid in a column of the parent grid which is not at all nice.

I think you should either keep your parent and child grids separate and accept that you can only see the children of the one selected row OR you will have to look for a custom grid control.

---

Simon Jones

Answer 2

Hi Nick

The following article you will find interesting information that facilitates the redistribution of controls on a Screen.

Tips and Tricks for Using the Screen Designer (Sheel Shah)

Jaime

Answer 3

Hi Simon,

No, its only one band I'm after...

Forgetting the parent child scenario for a minute, let's assume the following columns come from one table and are shown in a grid:-

PartNumber

PartDescripton

GenericPartReference

PrimaryVendor

( 3 or 4 checkboxes showing different things)

QuantityPer

Comments

In realty this is exactly what I want to see, however the PartDescription, GenericPartReference, PrimaryVendor, and the checkboxes actually come from the related parts library table.

It's not a one-to many-relationship in this instance, its a one-to-one relationship which is why I really want to see it in one grid.

Many thanks for your response,

Nick 

Answer 4

Thanks Jaime, I'll take a look...

Answer 5

In that case you could construct a VIEW in the SQL Server database that joins the two tables together to present them as one set of rows.

In a truly one-to-one relationship you can use an INNER JOIN.

If it is actually a one-to-zero-or-one relationship, you would have to use an OUTER JOIN to ensure you got all the rows from one table whether or not there was a corresponding row in the related table.

(You get NULL values where there isn't a corresponding row when you use an OUTER JOIN. If you use an INNER JOIN you don't see the row from the one table if it doesn't have a corresponding row in the other table.)

EG

SELECT T1.PartNumber, T1.QuantityPer, T2.PartDescription, T2.PrimaryVendor

FROM T1 LEFT OUTER JOIN T2 ON T1.PartNumber=T2.PartNumber

 

---

Simon Jones

Answer 6

You may, alternatively, be able to add the extra columns from the second table to your grid by selecting the Data Grid Row in the screen designer, clicking "Add" at the bottom of the field list for the row and selecting "Other Screen Data".

This gives you a dialog where you can specify what other data to add to the grid. Select the second table name, type a dot (full stop/period) and then select the field you want from the second table. Click OK and the extra column is added to your table. You can then drag it to where you want it in the Data Grid Row.

Providing LightSwitch knows the correct relationships between your tables, this method should get you columns from the two tables in the same grid without requiring you to define a VIEW in SQL Server.

---

Simon Jones

Answer 7

Hi Simon,

Great suggestions - thanks!

The "other screen data" idea worked perfectly, and I can now see the grid data exactly as required :o)

I'm sure using a view to populate the grid would also have been equally successful.

Thanks again for your assistance....

Nick

 

 

Answer 8

Hi Nick

We now have another good example on the link below:

Course Manager Sample Part 5 Detail Screens (Andy Kung)

 

Jaime