display datatable with union select data source, but Editor with one table

mmontoya

I would like to have a data table show the results of a union select statement. But I would like the user to be able to add a new record, always going to just one table. The main reason behind this is I want one place where they can search for someone, even if they don't know the person is a referral or a client.

datatable source:

Select CustomerID as ID, FirstName, LastName, SpouseName, City, State, Zip, 'C' as Type From Customers
Union
Select ReferralID as ID, FirstName, LastName, Null as SpouseName, Null as City, Null as State, Null as Zip, 'R' as Type From Referrals

But when they click the 'Add' button, the editor knows to use the Customers table (referrals are added on another webpage)

Is this possible or do I need to create my own form for them to add Customers and not use the Editor? Also, I'm guessing the datatable would have to be read-only, not editable, right?

allan

Hi,

At the moment, I'm afraid the Editor PHP and .NET libraries do not support a UNION statement. It is something I'd like to add in a future release, but it might be a while off.

So at the moment you would need to make your own SQL query to get the data. That is absolutely fine and valid - you can use whatever db interface you want to make the query on the server-side (PDO in PHP for example) and dump the data out into JSON.

That would indeed be read only, unless you then also added the ability to accept Editor CRUD commands - which is also a perfectly valid thing to do.

Allan

mmontoya

Thanks.

I ended up just making it read only and adding a button to go to a 'Details' page which will have more information and also have an editable DataTable.

However, I am trying to add a Stand-alone editor button to add a new record. After I click the button to save the new record the 'wheel' keeps turning in the upper right of the Editor box and doesn't close. If I click the 'x' to close it manually and/or refresh the page manually I see the record did get added. Why is the Editor window not closing automatically? Also, how do I get the 'read-only' data table to refresh after I submit the new record using the stand-alone?

allan

Why is the Editor window not closing automatically?

Can you give me a link to the page so I can debug it please? I'm not immediately sure why that would happen.

Also, how do I get the 'read-only' data table to refresh after I submit the new record using the stand-alone?

Assuming it is Ajax loaded using the ajax option, use ajax.reload(). Otherwise use the API - clear() and rows.add() to clear and then add new data.

Allan

mmontoya

I will send you an email with a link, username, and password.

thanks

allan

Hi,

Thanks for the link! There is a Javascript error occurring when the Ajax request from the server is returned. That is occurring because the server returns with {"row":null}.

In turn that is being caused by the fact that the CustomerID parameter is being sent to the server as an empty string. I presume that parameter is the primary key value. The easiest fix is simply to not include the primary key in the Editor form. Do you really want it to be editable? It is hidden, so presumably not. I would just remove it.

Allan

mmontoya

That worked perfectly. Thanks. But now I have another dataTable element, called 'table', on that same page. After they add to the newCustomerEditor, how do I refresh the one called table? I tried the code below, but it didn't work...figure the syntax is incorrect?

        $('#addCustomer').on( 'click', function () {
            newCustomerEditor
            .title('Add new customer')
            .buttons('Save')
            .create(table.ajax.reload());
        } );

allan

.create(table.ajax.reload());

That will execute the ajax.reload() method immediately and pass the result to create()...

There is no callback option for create() so I'm not sure what lead you to think that would work - is there something in the documentation that suggests so (and if so can you tell me so I can correct it please)?

I would suggest listening for the submitComplete event and executing the Ajax reload in that event handler.

Allan

mmontoya

Regarding documentation, no, being new to development I am still struggling trying to figure out how to understand the manuals, etc. so I just try things hit and miss sometimes :( :( :(

but thanks for your pointing me towards submitComplete. I was able to put this together:

        $('#addCustomer').on( 'click', function () {
            newCustomerEditor
            .title('Add new customer')
            .buttons('Save')
            .create();
        } );

        newCustomerEditor.on( 'submitComplete', function ( e, json, data ) {
            table.ajax.reload();
        } );

allan

No worries - thanks for the confirmation. I was worried it had invalid information in the manual!

Allan