mvc project - select options based off another selected field

montoyam

I have two tables: Actions and ActionStatus. One Action can have multiple ActionStatus:

Action: Action1--> Refused, Not home, Completed, etc...
Action: Action2 --> In Process, Pending, Waiting on Others

So, in my Editor, when the user selects Action1, I need the ActionStatus dropdown to be limited to only those ActionStatus for Action1.

Is this possible?

    var CaseActionsEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: 'api/CaseActions',
            data: function (d) {
                var selected = CasesTable.row({ selected: true });
                if (selected.any()) {
                    console.log(selected.data().Cases.CaseID);
                    d['CaseFilterID'] = selected.data().Cases.CaseID;
                }
            }
        },

        table: '#CaseActions',
        fields: [
            { name: "CaseActions.CaseID", label: "CaseID", type: "readonly" },
            {
                label: "Action:",
                name: "CaseActions.ActionID",
                type: "select",
                placeholder: "<Select Action>",
                placeholderValue: 0,
                placeholderDisabled: false
            },
            {
                label: "Defendant:",
                name: "CaseActions.CaseDefendantID",
                type: "select",
                placeholder: "<Select Defendant>",
                placeholderValue: 0,
                placeholderDisabled: false
            },
            { name: "CaseActions.ActionDate", label: "Action Date", type: "datetime" },
            { name: "CaseActions.FollowUpDate", label: "FollowUp Date", type: "datetime" },
            {
                name: "CaseActions.ActionStatusID",
                label: "Action Status",
                type: "select",
                placeholder: "<Select Status>",
                placeholderValue: 0,
                placeholderDisabled: false
            },
            {
                name: "CaseActions.FollowUpStaffID",
                label: "Staff to Follow Up",
                type: "select",
                placeholder: "<Select staff>",
                placeholderValue: 0,
                placeholderDisabled: false
            },
            { name: "CaseActions.ActionNote", label: "Note", type: "textarea" },
            { name: "CaseActions.EnteredBy", label: "Added By" },
            {
                label: "Date Added:",
                name: "CaseActions.DateAdded",
                type: "readonly",
                def: function () {
                    var d = new Date();
                    return d;
                }

            }
        ]
    });

    public class CaseActionsController : ApiController
    {
        [Route("api/CaseActions")]
        [HttpGet]
        [HttpPost]
        public IHttpActionResult CaseActions()
        {
            var request = HttpContext.Current.Request;
            var settings = Properties.Settings.Default;

            using (var db = new Database(settings.DbType, settings.DbConnection))
            {
                var response = new Editor(db, "CaseActions", "CaseActionID")
                    .Model<CaseActionsModel>("CaseActions")
                    .Model<CaseDefendantsModel>("CaseDefendants")
                    .Field(new Field("CaseActions.ActionID")
                        .Validator(Validation.NotEmpty())
                        .Options(new Options()
                                    .Table("Actions")
                                    .Value("ActionID")
                                    .Label("ActionName")
                        )

                    )
                    .Field(new Field("CaseActions.CaseDefendantID")
                        .Options(new Options()
                                    .Table("CaseDefendants")
                                    .Value("CaseDefendantID")
                                    .Label(new[] { "DefendantFirstName", "DefendantLastName" })
                                    .Render(row => row["DefendantLastName"] + ", " + row["DefendantFirstName"])
                        )
                    )
                    .Field(new Field("CaseActions.ActionDate")
                        .Validator(Validation.NotEmpty())
                        //.SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("CaseActions.FollowUpDate")
                        .SetFormatter(Format.NullEmpty())
                        .GetFormatter(Format.DateSqlToFormat("MM/dd/yyyy"))
                    )
                    .Field(new Field("CaseActions.ActionStatusID")
                        .Validator(Validation.NotEmpty())
                        .Options(new Options()
                                    .Table("ActionStatus")
                                    .Value("ActionStatusID")
                                    .Label("StatusName")
                                    //.Where(q => q.Where("ActionID", CaseActions.ActionID, "="))
                        )
                    )
                    .Field(new Field("Cases.CaseNumber"))
                    .Field(new Field("Actions.ActionName"))
                    .Field(new Field("ActionStatus.StatusName"))
                    .Field(new Field("Staff.StaffFirstName"))
                    .Field(new Field("CaseActions.DateAdded")
                        .Set(false)
                    )
                    .LeftJoin("Cases", "Cases.CaseID", "=", "CaseActions.CaseID")
                    .LeftJoin("CaseDefendants", "CaseDefendants.CaseDefendantID", "=", "CaseActions.CaseDefendantID")
                    .LeftJoin("Actions", "Actions.ActionID", "=", "CaseActions.ActionID")
                    .LeftJoin("ActionStatus", "ActionStatus.ActionStatusID", "=", "CaseActions.ActionStatusID")
                    .LeftJoin("Staff", "Staff.StaffID", "=", "CaseActions.FollowUpStaffID")
                    .Where("CaseActions.CaseID", request.Form["CaseFilterID"])
                    .Process(request)
                    .Data();
                return Json(response);
            }
        }
    }

rf1234

This thread has the solution for it:
https://datatables.net/forums/discussion/comment/173268#Comment_173268

My first answer is the one you are looking for.
Just replace "project" with "action" and "user" with "actionStatus".

In your case the Javascript should look similar to this:

yourEditor
    .dependent('CaseActions.ActionID', function (val, data, callback) {
        var self = yourEditor;
        if ( val <= 0 ) { //no action id selected => no actionStatus selectable
            self.field('CaseActions.ActionStatusID').update([], false);
        } else {
            $.ajax({
                type: "POST",
                url: 'get the relevant actionStatuses for the dropdown',
                data: { ActionID: val },
                dataType: "json",    
                success: function (data) {
                    self.field('CaseActions.ActionStatusID').update(data, false);
                    //you would also need to search the returned options and
                    //find out whether the current value of actionStatus id is in that
                    //list. If not: empty CaseActions.ActionStatusID
                    if ( self.val('CaseActions.ActionStatusID') > 0 ) {
                        var i = 0;
                        var relevantActionStatusIds= [];
                        while ( data[i] ) {
                            relevantActionStatusIds.push(data[i].value);
                            i++;
                        }
                        if ( $.inArray(self.val('CaseActions.ActionStatusID'), relevantActionStatusIds) < 0 ) {
                            self.set({'CaseActions.ActionStatusID': ''});
                        }
                    }
                }
            });
        }
    })

montoyam

sorry to be taking so long to get back to this one...
I am getting the wrong values in the select. The ajax is returning the correct filtered data, but the selectbox has values like: draw, data, recordsTotal, etc. Which, I see these are at the data level. I tried changing to data.data but that didn't seem to work:

    CaseActionsEditor
        .dependent('CaseActions.ActionID', function (val, data, callback) {
            var self = CaseActionsEditor;
            if (val <= 0) { //no action id selected => no actionStatus selectable
                self.field('CaseActions.ActionStatusID').update([], false);
            } else {
                var actionID = CaseActionsEditor.get('CaseActions.ActionID');
                $.ajax({
                    type: "POST",
                    url: 'api/GetActionStatus?ActionID='+actionID,
                    data: { ActionID: val },
                    dataType: "json",
                    success: function (data) {
                        console.log(data);
                        debugger;
                        self.field('CaseActions.ActionStatusID').update(data, false);
                        //you would also need to search the returned options and
                        //find out whether the current value of actionStatus id is in that
                        //list. If not: empty CaseActions.ActionStatusID
                        if (self.val('CaseActions.ActionStatusID') > 0) {
                            var i = 0;
                            var relevantActionStatusIds = [];
                            while (data.data[i]) {
                                relevantActionStatusIds.push(data.data[i].value);
                                i++;
                            }
                            if ($.inArray(self.val('CaseActions.ActionStatusID'), relevantActionStatusIds) < 0) {
                                self.set({ 'CaseActions.ActionStatusID': '' });
                            }
                        }
                    }
                });
            }
        })

montoyam

well, I was able to patch together code from different forum posts... there may be an easier way to do it, but it works...

    CaseActionsEditor
        .dependent('CaseActions.ActionID', function (val, data, callback) {
            var self = CaseActionsEditor;
            if (val <= 0) { //no action id selected => no actionStatus selectable
                self.field('CaseActions.ActionStatusID').update([], false);
            } else {
                var actionID = CaseActionsEditor.get('CaseActions.ActionID');
                $.ajax({
                    type: "POST",
                    url: 'api/GetActionStatus?ActionID='+actionID,
                    data: { ActionID: val },
                    dataType: "json",
                    success: function (data) {
                        var jsonData = data.data;
                        var items = [];
                        for (var i = 0; i < jsonData.length; i++)
                            items.push({
                                label: jsonData[i]["StatusName"],
                                value: jsonData[i]["DT_RowId"]
                            });
                        self.field('CaseActions.ActionStatusID').update(items, false);
                        //you would also need to search the returned options and
                        //find out whether the current value of actionStatus id is in that
                        //list. If not: empty CaseActions.ActionStatusID
                        if (self.val('CaseActions.ActionStatusID') > 0) {
                            var i = 0;
                            var relevantActionStatusIds = [];
                            while (data.data[i]) {
                                relevantActionStatusIds.push(data.data[i].value);
                                i++;
                            }
                            if ($.inArray(self.val('CaseActions.ActionStatusID'), relevantActionStatusIds) < 0) {
                                self.set({ 'CaseActions.ActionStatusID': '' });
                            }
                        }
                    }
                });
            }
        })

montoyam

oops. well, if a value is already selected when you open the editor it is not showing. i see that is what lines 25-35 are supposed to do so I will look into what is going on there.

montoyam

oh, that was easy...line 29:

relevantActionStatusIds.push(data.data[i].ActionStatusID);

so everything:

    CaseActionsEditor
        .dependent('CaseActions.ActionID', function (val, data, callback) {
            var self = CaseActionsEditor;
            if (val <= 0) { //no action id selected => no actionStatus selectable
                self.field('CaseActions.ActionStatusID').update([], false);
            } else {
                var actionID = CaseActionsEditor.get('CaseActions.ActionID');
                $.ajax({
                    type: "POST",
                    url: 'api/GetActionStatus?ActionID='+actionID,
                    data: { ActionID: val },
                    dataType: "json",
                    success: function (data) {
                        var jsonData = data.data;
                        var items = [];
                        for (var i = 0; i < jsonData.length; i++)
                            items.push({
                                label: jsonData[i]["StatusName"],
                                value: jsonData[i]["ActionStatusID"]
                            });
                        
                        self.field('CaseActions.ActionStatusID').update(items, false);
                        //you would also need to search the returned options and
                        //find out whether the current value of actionStatus id is in that
                        //list. If not: empty CaseActions.ActionStatusID
                        if (self.val('CaseActions.ActionStatusID') > 0) {
                            
                            var i = 0;
                            var relevantActionStatusIds = [];
                            while (data.data[i]) {
                                relevantActionStatusIds.push(data.data[i].ActionStatusID);
                                i++;
                            }
                            if ($.inArray(self.val('CaseActions.ActionStatusID'), relevantActionStatusIds) < 0) {
                                self.set({ 'CaseActions.ActionStatusID': '' });
                            }
                        }
                    }
                });
            }
        })