Is there a way to combine the Child Rows and Row Grouping?

mmcnair80

I woiuld like to have some of the effects of both.

I have a table that is setup for reviewing invoice data. There are invoice numbers, project numbers, Capital, Expense, Removal, TotalAmount and a few others that don't really matter for what I want to do here.

What happens is there are invoices that have multiple projects associated with them. The projects have their own Capital, Expense, and Removal cost (and therefore their own totals).

I would like to have these projects be the drop down for the Invoice that they are associated with. Then the Invoice will have the TotalAmount from all the Projects.

I would also like to have the drop down child records have all the same columns as the parent, that is where the row grouping comes in. When I click the + for the drop down I would want the parent to have all the projects associated with it grouped underneth it.

allan

It should be quite possible - they use different parts of DataTables to operate, and they should be able to exist together.

RowGroup itself doesn't provide an expand / collapse option for the group. But if you want to expand a single row, that shouldn't be an issue - you would just do it the normal way using row().child().

Allan

mmcnair80

@allan
I need some help with this. I'm not sure how to get it to work. What I have is a table "Invoices" that looks like this:

+------+-------+---------------+-----------------+-------------+--------------------+----------+---------+--------+
|  Id  | State | InvoiceNumber | ProjectWONumber | InvoiceDate | TotalProjectAmount | Capital  | Expense | Status |
+------+-------+---------------+-----------------+-------------+--------------------+----------+---------+--------+
| 1938 | CT    |      00000462 |         3109473 | 2017-05-25  |            657.190 | NULL     | 657.190 | Paid   |
| 3613 | CT    |      00000462 |         3107492 | 2017-05-25  |            657.190 | NULL     | 657.190 | Paid   |
| 3614 | CT    |      00000462 |         3106222 | 2017-05-25  |           1690.970 | 1690.970 | NULL    | Paid   |
| 3615 | CT    |      00000462 |         3107795 | 2017-05-25  |           1302.380 | 1302.380 | NULL    | Paid   |
| 3616 | CT    |      00000462 |         3096444 | 2017-05-25  |            675.190 | NULL     | 675.190 | Paid   |
| 3617 | CT    |      00000462 |         3109473 | 2017-05-25  |            328.590 | NULL     | 328.590 | Paid   |
+------+-------+---------------+-----------------+-------------+--------------------+----------+---------+--------+

As you can see the Invoice Number is the same for all lines, but the ProjectWONumber is different and there are different Capital and Expense amounts. What I am trying to do is have a Parent line that will have the common data like State, Invoice Number, and Invoice Date and have the Total Project Amount then have a button or arrow or something on the side that would be used to show or hide the child rows. so that it would look something like this when all rows are shown:

+-----+------+-------+---------------+-----------------+-------------+--------------------+----------+----------+--------+
| PId |  Id  | State | InvoiceNumber | ProjectWONumber | InvoiceDate | TotalProjectAmount | Capital  | Expense  | Status |
+-----+------+-------+---------------+-----------------+-------------+--------------------+----------+----------+--------+
|   1 |      | CT    |      00000462 |                 | 2017-05-25  |           5311.510 | 2993.350 | 2318.160 |        |
|     | 1938 | CT    |      00000462 |         3109473 | 2017-05-25  |            657.190 |          |  657.190 | Paid   |
|     | 3613 | CT    |      00000462 |         3107492 | 2017-05-25  |            657.190 |          |  657.190 | Paid   |
|     | 3614 | CT    |      00000462 |         3106222 | 2017-05-25  |           1690.970 | 1690.970 |          | Paid   |
|     | 3615 | CT    |      00000462 |         3107795 | 2017-05-25  |           1302.380 | 1302.380 |          | Paid   |
|     | 3616 | CT    |      00000462 |         3096444 | 2017-05-25  |            675.190 |          |  675.190 | Paid   |
|     | 3617 | CT    |      00000462 |         3109473 | 2017-05-25  |            328.590 |          |  328.590 | Paid   |
+-----+------+-------+---------------+-----------------+-------------+--------------------+----------+----------+--------+

With the first row with PId = 1 being the parent and all the others being the Child rows. But I don't know how to go about creating this effect. I can make the Parent rows and have some kind of key to connect them to the correct children I don't know how to get the show/hide effect of the child rows. I thought that there would be a way, I just cannot figure it out.

Here is the beginning of a test I just got it set up to show what I have from above. As I test things I'll add them here as well so that you can hopefully guide me as to what to do to get this to work for me.
live.datatables.net/jigogeye/1/edit?html,css,js,console,output

allan

Hi,

What to do is to get it working as a plain table without row grouping first. Make sure you include the invoice number in the table display, but don't worry about the row grouping. That's easy to add at the end!

You could use Generator to get the HTML / JS required for the basic table.

Once you've got that, use the RowGroup extension to insert the grouping row.

Allan

mmcnair80

I have tried to add the RowGrouping without success. I've tried:

var table = $('#DataTableAdd').DataTable({
    "rowGroup":
    {
        dataSrc: 6
    }
});

And I've tried:

var table = $('#DataTableAdd').DataTable({
    "rowGroup":
    {
        dataSrc: "InvoiceNumber"
    }
});

Neither has worked. I tried adding the RowGroup extension like this:

<link rel="stylesheet" type="text/css" href="DataTables/RowGroup-1.0.2/css/rowGroup.dataTables.css"/>
<script type="text/javascript" src="DataTables/RowGroup-1.0.2/js.dataTables.rowGroup.js"></script>

Am I missing something? I'm not able to find much information on how to use RowGrouping, though I might be looking in the wrong place. I've looked at this and this

allan

Perhaps you can give me a link to your page so I can take a look and suggest what might be missing or needed to make RowGroup work.

Allan

mmcnair80

@allan Sorry for taking so long to get this posted, here's a link to what I have. It's not exactly what I have on my actual site, it's an intranet site so I can't give you direct access to it, but this is close enough that I think it should work.

Example

If you have any questions let me know.

There are only 10 rows, but there are 3 that the row grouping should work on. It doesn't seem to be doing anything though.

allan

The example linked to didn't include the RowGroup extension. Adding it in allows it to work as expected.

Allan

mmcnair80

@allan I see. That works much better. I'll have to update my website accordingly. I do have another question then. How do I modify what is shown in the Grouping header?

mmcnair80

In the example, I would like to have the totals for the TotalProjectAmount, Capital, Expense, and Removal in the Grouping Header to be sums of the rows that are grouped.

allan

The rowGroup.startRender option is used for that.

Allan

mmcnair80

@allan I've begun using that, but I'm not sure how to start getting the Totals that I'm looking for, or how to get everything into the placing that I want so it matches up with what is already in the table.

Are there any examples of how to go about this? I did see on the page about startRender that there were 3 types of returns. It seems to me that I need the second one:

A tr node which contains a row that will be injected into the table as the group start row. This is useful if you wish to use multiple cells in the grouping row - e.g. to provide alignment with the host data.

allan

Yes, the second one sounds right. This example shows how to return a tr node and also how to do aggregates.

Allan

mmcnair80

@allan Perfect! That is what I needed to get the rest of it working. Thanks!