Scrolling Datatable -- NOT the "footer" ("Showing 1 to 20 of 256 entries" and paging)

menashe · January 2023

I have a parent-child Datatable in a DIV with a border. When I expand a child, I am able to keep the data displayed within the DIV border (using "overflow: scroll"). However, everything gets pushed down!
Is there a way (there always is!) to scroll on the data, but not the footer, so that it is always visible?
Thanks!

kthorngren · January 2023

I have a parent-child Datatable in a DIV with a border. When I expand a child

Are you referring to child detail rows like this example?

Is there a way (there always is!) to scroll on the data, but not the footer, so that it is always visible?

I suspect there are some generic CSS settings you can make. Without seeing what you have its impossible to make suggestions. Please post a link to your page or a test case replicating the issues so we can help debug.
https://datatables.net/manual/tech-notes/10#How-to-provide-a-test-case

Kevin

menashe · January 2023

OK. I'll play around first.

But that is exactly what I mean--you'll notice that when you expand the child, everything drops lower, potentially exceeding the surrounding DIV.

I solved that issue, but I want the scrollbar for the "data" only--not the footer.

kthorngren · January 2023

but I want the scrollbar for the "data" only--not the footer.

This horizontal scrolling example seems to work as your thread title suggests. Meaning the Datatables elements are stationary while scrolling. Is this what you are looking for?

Kevin

paulakarhi · January 2023

k

menashe · January 2023

Hi Kevin,

Are you referring to child detail rows like this example?

Sorry for the delay. Yes, that example. (https://datatables.net/examples/api/row_details.html).

As you can see, when you expand a child, everything moves down. I have that working inside a DIV--that is, it does not overflow the DIV, it adds a scrollbar--but I want the only the data to have the scrollbar--the header and footer should stay fixed!

Any ideas?

allan · January 2023

Something like this: http://live.datatables.net/mojoxuha/1/edit ?

Allan

menashe · January 2023

Bless you!!

menashe · January 2023

On another note, I have Selectize working flawlessly--except to add/create a new item.
It return the item name, but...
1) I do not understand how to get the new item into the database?
2) I cannot see how to get the numerical id for that new item from the database?

allan · January 2023

I was just looking at the Selectize documentation, and unless I'm missing something I don't see a way for it to dynamically add new options as they are typed in?

Allan

menashe · January 2023

Hi Allan,

After much thought, I think that it (may) boils down to this.

If I do an "Add" on a simple editor form, with "->debug(true)" on the Editor PHP (which generates the SQL), I can see that an INSERT is done.

What do I need to do to "force" Selectize (or any plugin) to perform an Insert??

allan · January 2023

There is a missing piece of information here that I'm not sure what is. When you click the "Create" button and then submit the form, it will create an INSERT command at that point.

What I'm not sure about is when you want the Selectize insert to happen. Do you able to be able to type in a joined table's label (name) and if it doesn't exist, it should insert into that joined table? Or is Selectize just showing a list of string options from the host table.

If you could link to the page and show your server-side code, that would be useful.

Allan

menashe · January 2023

I will get around to reading the instructions that you sent regarding linking to my code.

It is the first scenario--I am showing values from a joined table. If an entry is Added, I want it to be inserted into the joined table.

(On a related note, what is the proper way to perform an INSERT in the Editor back-end PHP code?)

menashe · January 2023

To elaborate on that previous post, https://editor.datatables.net/manual/php/getting-started very clearly describes the Editor PHP and mentions "create" several times--but what do I need to do so that it recognizes the request as a Create??

menashe · January 2023

I JUST DID IT!!!

menashe · January 2023

<?php

// DataTables PHP library
include("../../Editor-PHP-2.0.10/lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$term = $_POST['manufacturer'];

$editor = Editor::inst($db, 'manufacturers')
  ->fields(
      Field::inst('id'),
  );  
  
if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
    $editor
    ->fields(
      Field::inst('manufacturer'),
  );      
  $_POST ['data']['manufacturers']['manufacturer'] = $term;
}   
    
$manufacturer = $editor 
    ->process($_POST)
    ->data();
    
echo json_encode( $manufacturer );

This was the key:

$_POST ['data']['manufacturers']['manufacturer'] = $term;

Can you please explain to me what I did??

allan · January 2023

Not really, as I wouldn't expect that to work! The parameters in the $_POST['data'] object are primary key values. So that would suggest that manufacturers is a primary key value, which seems rather unlikely.

Without being able to see the client-side code, I can't really say why that would work. All I can say is that I am very surprised that it does, and I would not suggest relying upon it until it is determined why it works.

Allan

menashe · January 2023

This is the second time in one day that I used a word out-of-context! By "this is the key" I simply meant that that line was what allowed me to add a new manufacturer to the database; it is not the key field.

I based my PHP on https://datatables.net/forums/discussion/23321/editor-i-need-to-be-insert-now-in-sql-database.

menashe · January 2023

Allan,

To say that I am as shocked as you would be the understatement of the month! What I sent you is "simple" code; with some tweaking, it now also works for my joined query.

**Here is the pertinent (I believe) client-size code:
**

            var packagingEditor = new $.fn.dataTable.Editor({
                ajax: {
                    dataType: "json",
                    type: "POST",
                    url: 'server_side/scripts/packaging.php',
                    data: function(d) {
                        d.item_id = itemsRowData.id;
                    },
                },
                serverSide: true,
                table: "#packaging",
                fields: [{
                        type: 'hidden',
                        name: 'packaging.item_id',
                        default: function() {
                            return itemsRowData.id;
                        },
                    },
                    {
                        fieldInfo: 'Please select or add a Manufacturer/Distributor...',
                        label: 'Distributor/Manufacturer:',
                        name: 'packaging.manufacturer_id',
                        type: 'selectize',
                        optionsPair: {
                            label: 'manufacturer',
                            value: 'manufacturer_id',
                        },
                        opts: {
                            create: function(input, callback) {
                                var formData = '&manufacturer=' + input + '&action=create';
                                $.ajax({
                                    type: 'POST',
                                    url: 'server_side/scripts/manufacturer.php',
                                    data: formData,
                                    success: function(result) {
                                        if (result) {
                                            console.log('value = ' + result.data[0].id);
                                            callback({
                                                value: result.data[0].id,
                                                text: input
                                            });
                                        }
                                    }
                                }),
                                $('#packaging').DataTable.ajax.reload();
                            },

I somewhat "simplified" the PHP that I sent yesterday:

<?php

// DataTables PHP library
include("../../Editor-PHP-2.0.10/lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

$editor = Editor::inst($db, 'manufacturers')
  ->fields(
      Field::inst('id'),
      Field::inst('manufacturer'),
  );  
  
if ( isset($_POST['action']) && $_POST['action'] === 'create' ) {
    $_POST ['data']['manufacturers']['manufacturer'] = $_POST['manufacturer'];
}   
    
$manufacturer = $editor 
    ->process($_POST)
    ->data();
    
header('Content-Type: application/json; charset=utf-8');
echo json_encode( $manufacturer );


**This is the PHP with the joined tables:
**
<?php

// DataTables PHP library
include("../../Editor-PHP-2.0.10/lib/DataTables.php");

use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

if ( ! isset($_POST['item_id']) || ! is_numeric($_POST['item_id']) ) {
    echo json_encode( [ "data" => [] ] );
}
else {
    Editor::inst($db, 'packaging')->debug(true)
    ->field(
        Field::inst('packaging.id')->set(false),
        Field::inst('packaging.item_id')
                ->options(Options::inst()
                        ->table('items')
                        ->value('id')
                        ->label('item')
                        ),
        Field::inst('items.item'),
        Field::inst('packaging.manufacturer_id')
            ->options(Options::inst()
                    ->table('manufacturers')
                    ->value('id')
                    ->label('manufacturer')
            )
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('manufacturers.id'),
        Field::inst('manufacturers.manufacturer'),
        Field::inst('packaging.brand_id')
            ->options(Options::inst()
                    ->table('brands')
                    ->value('id')
                    ->label('brand')
            )
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('brands.id'),
        Field::inst('brands.brand'),
        Field::inst('packaging.outer_packages')
                        ->setFormatter( Format::ifEmpty( 1 ) ),
        Field::inst('packaging.inner_packages')
                        ->setFormatter( Format::ifEmpty( 1 ) ),
        Field::inst('packaging.inner_items')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('packaging.unit_quantity'),
        Field::inst('packaging.unit_id')
            ->options(Options::inst()
                    ->table('units')
                    ->value('id')
                                ->label('unit' )    
                        ),
        Field::inst('units.unit'),
        Field::inst('units.description'),
        Field::inst('packaging.upc_ean')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('packaging.prefix')
                        ->setFormatter( Format::ifEmpty( null ) ),
        Field::inst('packaging.suffix')
                        ->setFormatter( Format::ifEmpty( null ) ),
        )
    ->leftJoin('items', 'items.id', '=', 'packaging.item_id')
    ->leftJoin('brands', 'brands.id', '=', 'packaging.brand_id')
    ->leftJoin('manufacturers', 'manufacturers.id', '=', 'packaging.manufacturer_id')
    ->leftJoin('units', 'units.id', '=', 'packaging.unit_id')
    ->where( 'item_id', $_POST['item_id'] )
    ->process($_POST)
      ->json();
}

**This is "packaging.php _before_ the change:**

[
    {
        "DT_RowId": "row_162",
        "packaging": {
            "id": 162,
            "item_id": 272,
            **"manufacturer_id": 53,**
            "brand_id": 115,
            "outer_packages": 1,
            "inner_packages": 1,
            "inner_items": 2,
            "unit_quantity": "16.00",
            "unit_id": 3,
            "upc_ean": "300650363785",
            "prefix": null,
            "suffix": null
        },
        "items": {
            "item": "3% Hydrogen Peroxide Cleaning &amp; Disinfecting Solution"
        },
        **"manufacturers": {
            "id": 53,
            "manufacturer": "Alcon Laboratories, Inc."
        },**
        "brands": {
            "id": 115,
            "brand": "Clear Care Plus"
        },
        "units": {
            "unit": "oz",
            "description": "ounce"
        }
    }
]

This is "packaging.php after the change:

{
    "DT_RowId": "row_162",
    "packaging": {
        "id": 162,
        "item_id": 272,
       ** "manufacturer_id": 171,**
        "brand_id": 115,
        "outer_packages": 1,
        "inner_packages": 1,
        "inner_items": 2,
        "unit_quantity": "16.00",
        "unit_id": 3,
        "upc_ean": "300650363785",
        "prefix": null,
        "suffix": null
    },
    "items": {
        "item": "3% Hydrogen Peroxide Cleaning &amp; Disinfecting Solution"
    },
    **"manufacturers": {**
        **"id": 171**,
        **"manufacturer": "An entry that I added in Selectize!**"
    **},**
    "brands": {
        "id": 115,
        "brand": "Clear Care Plus"
    },
    "units": {
        "unit": "oz",
        "description": "ounce"
    }
}

Full Disclosure: I am CLUELESS how all of the entries in the joined tables (bolded above) got updated

The only(??) issue remaining--in my Editor window, my Selectize box displays "undefined". When I click "Update" to close the Editor window, my (DataTable) table is updated correctly (and the entry is added to the database)

Edited by Allan - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

allan · January 2023

Can you reuse existing manufacturers?

The PHP libraries do have some left join editing abilities, but I generally discourage their use. Nested editing is much more reliable.

Allan

menashe · January 2023

I'm not sure what you mean by "reuse". I can assign a manufacturer to more than one item.

I use nested editing in a different context. I very much want to have autocomplete (Selectize) available here!

allan · January 2023

I can assign a manufacturer to more than one item.

That's basically what I meant. I just wanted to be sure it wasn't adding a new manufacturer to the table every time (e.g. having ten "ACME Ltd" companies wouldn't be useful!).

Allan

menashe · January 2023

It is adding each (new) item twice, and I cannot figure out why?

Also, I have not been able to successfully update the Selectize pulldown to reflect a new value--I have to refresh the browser, which is obviously less than useful!

allan · January 2023

It is adding each (new) item twice, and I cannot figure out why?

Right - it is seeing the new label coming in, and is creating a new one every time. It isn't attempting to lookup the table.

Is the nested editing example an option for you? I think it would resolve this issue in the simplest manner possible.

If it isn't an option, then what to do is to first get it working with a simple select (i.e. remove the complexity of Selectize, and get the base case working). Then, make it a Selectize field (without the ability to add new items) and check that is working (that should be a simple swap of the option). Finally, after that, add in support for Selectize creating new items. As I mentioned before, I didn't see in the Selectize documentation how to do that, so I'm not certain what the right calls would be though. Have you seen that this is possible in the Selectize documentation?

Allan

menashe · January 2023

Good Morning Allan,

Is it an option? Anything is an option! This is for "fun" at home; I am building an app (yes, on the Desktop) that has all of the features that I never found in the app store!

In real life, I program C# .NET, with a heavy emphasis on back-end Oracle SQL.

I do use nested editing in this application; I am just trying to incorporate other features for "robustness," but...

One question: When you say "I didn't see in the Selectize documentation how to do that,..." are you referring to adding new items?? Because they have the "create:" option!

menashe · January 2023

Allan,

Your advice is GOLDEN!! And I thank you!!

I removed the Selectize statements; still had the issue where the app hung after pressing "Update" on the Editor modal (the Selectize are in the Editor).

But just having visually removed that complexity allowed me to see within moments what the issue was.

Fixed, and all the Selectize are now working--famously, as you might say!

The only remaining issue is the "undefined" in the Selectize box--though it does save properly to the DataTable and the database. I suspect that I simply need to use "addOption" to add the newly-created entry to the Selectize dropdown.

So... it seems that it does work. Happy to share if you need!

allan · January 2023

Excellent - sounds like you've make great progress.

I totally missed the create option in their docs. This looks relevant. I need to spend some more time and investigate properly

Allan

menashe · January 2023

Question about Editor PHP and Validation.
1) Can I display those validation messages in a Modal window, rather than at the bottom of the Editor form?
2) How can I set validation for if/when the user attempts to delete a primary record which still have a foreign key (in a secondary table) referencing it?

allan · January 2023

Can I display those validation messages in a Modal window, rather than at the bottom of the Editor form?

Sure - return an error for the field that is in error. See the fieldErrors array here.

How can I set validation for if/when the user attempts to delete a primary record which still have a foreign key (in a secondary table) referencing it?

Two options:

Set the row to cascade on delete in the database schema, or
Use a validation function to check if the row being deleted is being referred to from anywhere else.

We don't actually have a built in validator for that - I'll look into an option for that.

Allan

Scrolling Datatable -- NOT the "footer" ("Showing 1 to 20 of 256 entries" and paging)

Scrolling Datatable -- NOT the "footer" ("Showing 1 to 20 of 256 entries" and paging)

Replies

Howdy, Stranger!

Categories

DataTables

Scrolling Datatable -- NOT the "footer" ("Showing 1 to 20 of 256 entries" and paging)

Scrolling Datatable -- NOT the "footer" ("Showing 1 to 20 of 256 entries" and paging)

Replies

Howdy, Stranger!

Quick Links

Categories

DataTables