Blank value for dropdown column is changed

arnorbld

Hi all,

I have a table with 12 columns, but only 4 columns are editable. Any of those columns can contain a blank value, but one, in particular, can have anywhere from all to no rows with a blank value.

This column has a dropdown defined as:

{label: "Status", name: "postatus",
    type: "select",
    def:  function () { return ''; },
    options: [
        {value: "S", label:"Submitted"},
        {value: "A", label:"Accepted"},
        {value: "R", label:"Rejected"},
        {value: "M", label:"Modified"},
        {value: "D", label:"Done"},
        {value: "V", label:"Verified Done"},
        {value: "F", label:"Final"},
    ]},

What happens if I change ANY of the other columns and save, is that this column is set to the first value in the list "S" This comes through to the $_POST as:

array(2) {
  ["data"]=>
  array(1) {
    ["2968263d-b166-48a1-9afd-37684ab439fa"]=>
    array(2) {
      ["startdate"]=>
      string(10) "08-26-2020"
      ["postatus"]=>
      string(1) "S"
    }
  }
  ["action"]=>
  string(4) "edit"
}

After some head scratching, I changed the code to:

{label: "Status", name: "postatus",
    type: "select",
    def:  function () { return ''; },
    options: [
        {value: "", label:""},
        {value: "S", label:"Submitted"},
        {value: "A", label:"Accepted"},
        {value: "R", label:"Rejected"},
        {value: "M", label:"Modified"},
        {value: "D", label:"Done"},
        {value: "V", label:"Verified Done"},
        {value: "F", label:"Final"},
    ]},

With a BLANK first entry. This works and the column is not updated if it has no value. But this also adds a blank selection option into the dropdown list:

That is not acceptable because the values can ONLY be one of the actual selected values. With the blank option, the user can set the status to blank, which could cause major problems down the line.

I guess I could intercept the $_POST before it gets to the editor code in the ajax PHP script, fetch the row it's updating and reset the postatus, but before I mess with that, I would like to know if there is a way to fix this problem with the editor configurations.

Essentially what I need is that if there is NO value in it, it should not add a value. I prevent the editor from attempting to edit blank values in those cells with:

$('#task-and-po-table').on('click', 'tbody td.editable', function (e) {
    var cellValue = table.cell(this).data();
    if (cellValue) {
        editor.inline( this, {
            buttons: { label: 'S', fn: function () {
                    this.submit();
                }
            }
        });  // editor.inline( this
    } // if cellvalue
});  //  on('click')

Any ideas on how to prevent this selection on a BLANK cell?

colin

You could try setting form-options - set submit to changed - that way only changed values will be sent back to the server. To show you how, this example is also setting those form-options.

Colin

arnorbld

Hi Colin,

Thank you so much! Will try that out and let you know!

arnorbld

Hi Colin,

I added this into the Editor configuration:

editor = new jQuery.fn.dataTable.Editor({

    table: '#task-and-po-table',
    formOptions: {
        main: {
            submit: 'changed'
        }
    },

It still submits the value for the blank cell. The $_POST still contains the field and the first value from the dropdown:

{
  ["data"]=>
  array(1) {
    ["5089f4f1-dd8d-43da-9023-5deca619b535"]=>
    array(2) {
      ["startdate"]=>
      string(10) "08-03-2020"
      ["postatus"]=>
      string(1) "S"
    }
  }
  ["action"]=>
  string(4) "edit"
}

There is another column in the list, "PO Doc Number" and if it is blank, the status column will never have data. So is there a way in the editor to specify something to the effect of:

if(!podocument_num){
  status = '';
}

where podocument_num is the other column.

arnorbld

Hi Colin,

Had an idea. Since I'm constructing the html for the table I figured that perhaps if I don't apply the "editable" class to the cell where they should be blank it would prevent it from sending the data over. No luck. Even if the cell doesn't have the "editable" class it still sends the data over.

arnorbld

Hi Colin,

We ended up having to add a second column for this - not because of this problem, but because of limitations we needed to apply to the select options.

Question: Is it possible to reset the selection options at runtime for each cell based on some condition? For example, if column "A" has A, B or C then the dropdown in column "B" has 1, 2, and 3 but if column "A" has a value of T then the dropdown in column "B" can only have 6, 7 or 8.

If you want I can post this in a new thread.

colin

This example from this thread should help - it's adding an Office location to the select options if the current value isn't in the existing set of options. This shows how you can call field().update() to change the values. Hope that does the trick.

Colin

arnorbld

Hi Colin,

Excellent! Will check it out!

arnorbld

Hi Colin,

Well, adding a second column did not help as then that column gets updated when blank with the first option from the dropdown when any of the fields are saved.

I need to find a solution to this. Any other ideas on how to solve this?

allan

Can you give us a link to your page so we can take a look and see what is going wrong please?

Allan

arnorbld

Hi Alan,

Sorry for the late reply - got buried with other stuff.
I will get you set up later today.

arnorbld

Hi Alan,

Nothing I have tried to do has helped I have updated the site that I sent you the login for on January 11th.

1. Open the page and it will load a list of jobs.
2. Select the top one and click on the "Get Task/PO Data for Selected jobs" The lower list will load.
3. Observe that the "Status" column on the far right is blank. If not, use any row where the last column is blank.

4. On the first row change the Task status from "Start Date" to Done:
   

5. Hit the "S" button to submit the selection.

6. Observe that the "status" cell now has value.

After the data is saved, the status shows the FIRST entry in the dropdown for that column. This shows both the status having been updated and the next row with the selection dropped down. No matter what I do, when I save any data the "Status" column is set to "D"

I have tried the initEdit, preEdit and preSubmit events and no matter what I do I can't prevent the column from updating!

I have tried "def" in the fields like this:

                {label: "Status", name: "postatus_new",
                    def: 'V',
                    type: "select",
                    options: [
                        {value: "D", label:"Done"},
                        {value: "V", label:"Verified Done"},
                    ]}

and it does not set the value to "V" - it still sets it to "D"

The only thing I CAN do is to add a blank option to the drop down. The problem with that, is then that can be selected on cells that are set to either D or V, which would be a major problem for data integrity.

I don't quite understand WHY it is automatically selecting values for cells that haven't changed. It seems to me that it needs to detect when a selection or new selection is made with the drop down or date picker, etc. and if nothing has been selected or no change made it should not send any data over.

arnorbld

Hi Alan,

Think I may not have been clear. AFTER I select a different task status or start/end dates and submit the change, the "Status" column is ALWAYS updated to "D" in status cells that are blank. In my opinion, it should NEVER update blank cells with values unless that value is specifically selected. This data is saved to the SQL table causing all sorts of problems. There is a specific verification process that requires users to select the status at specific points in the process and setting it to Done when it's not is a major issue.

The only solution I have found is to add a blank option to the dropdown. That is not a good solution as then processes that have been set to Done or Verified Done can be changed BACK to blank, which can also cause all sorts of problems.

I really hope you can figure out what is going on and how to solve this.

colin

Are you able to link your page, please, as Allan asked in his last post. That will allow us to get a better idea of what's going on.

Colin

arnorbld

Hi Colin,

Alan has the login information. I'll send it to you privately.

allan

Hi,

Many thanks for the updates. I believe I see the issue - your postatus field has the following options:

                    options: [
                        {value: "D", label:"Done"},
                        {value: "V", label:"Verified Done"},
                    ]

But, when the state is loaded, postatus is an empty string. That isn't an option in the list, so the downdrop will default the the first option in the list which happens to be D in this case. Hence why it is always going to Done.

What I believe you need to do to resolve this is add another option to the list:

                    options: [
                        {value: "", label:"Not done"},
                        {value: "D", label:"Done"},
                        {value: "V", label:"Verified Done"},
                    ]

Then the value won't change if the value is one of those three options.

Allan

arnorbld

Hi Alan,

Yes, this is the only option I have found. However, it is a BAD option because it means that a user can BLANK out a value that they should not be able to blank out. I.e. a blank value in this is not a valid selection from the user's standpoint. I can code around it in this case, but there are cases where that could be a real challenge.

allan

The select has three optional parameter that you could use to address this: placeholder, placeholderDisabled and placeholderValue. Try:

placeholder: 'Not done',
placeholderDisabled: true,
placeholderValue: ''

Allan

arnorbld

Hi Alan,

Thanks! Can you put this in context for me? I tried adding this into the editor definition for the postatus_new but I didn't see that it made any difference.

colin

This example here shows placeholder being used - the other two would slot into the same place. Their usage is described in select,

Colin

arnorbld

Hi Colin,

Ah, now I see what it does! Thanks for the suggestion! I'll present this to my client.