Editor: Accessing MJoin Columns

kmbonin

I am using an MJoin to grab the last "time" record for an employee that has logged in to they system. The login/logout work fine, but I need to check to see if the employee is logged in/out, which is not working.

JSON snippet (MJoin returned):

"logan_dvpr_timeLog":[{"logan_dvpr_timeLog":{"taskID":"1462","userID":"38","loggedINYN":"Y","timeOUT":null,"timeIN":"11/5/2016"}}]}

JS snippet:

var table = $('#ProductionData').DataTable( {
        ajax: './api/DVPRTaskData/DVPRTaskData/' + dvprID,
        order: [1, 'asc'],
        fixedHeader: true,
            columns: [            
            {
                "data": "logan_dvprTasks.taskID", "visible": false, "targets": 1
            },
            {
                "data": "logan_dvpr_timeLog[0].loggedINYN", "visible": true
            }
            ] ...

I am trying to access the first value of loggedINYN from MJoined table logan_dvpr_timeLog. Any ideas?

allan

Use:

"data": "logan_dvpr_timeLog.0.loggedINYN"

instead of the [] array notation. Its not quite correct Javascript, but it does make sense when the string gets broken up into its component parts and I can just do object[property] (i.e. it becomes data['logan_dvpr_timeLog'][0]['loggedINYN']).

Allan

kmbonin

Thanks for the reply. I get this error when I use the dot notation: Requested unknown parameter 'logan_dvpr_timeLog.0.loggedINYN' for row 0, column 1.

allan

Could you show me the full JSON returned by the server please?

Thanks,
Allan

kmbonin

Here are the first few records (the whole thing is quite extensive). You should be able to see the pattern here.

{"draw":null,"data":[{"DT_RowId":"row_1462","logan_dvprTasks":{"taskID":"1462","dvprID":"8","taskName":"Contract set-up in EPICOR","taskNumber":"7","DisplayOrder":"1","taskNotes":"sdfsdsfdsfdsfsdfdsfsdwerjdfeklm","miscTaskName":"","taskCompleteYN":"","required":"0","responsibleID":"0","resourceID":"27","estTime":"1","estStartDate":"07/18/2016","loggedIn":"Y","lastLoginDate":"11/5/2016","estCompDate":"07/18/2016"},"logan_dept_DVPR":{"department":null},"logan_user_DVPR":{"fullName":"Brandon Satterfield"},"logan_dvpr":{"dvprNumber":"2016009","jobNumber":"011002-1-1","customerDesc":"Chevron Dolly for CTLF"},"TaskName":"Contract set-up in EPICOR","TaskNotes":"sdfsdsfdsfdsfsdfdsfsdwerjdfeklm","loggedIn":"Y","TaskNumber":7,"logan_dvpr_timeLog":[{"logan_dvpr_timeLog":{"taskID":"1462","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}}]},{"DT_RowId":"row_1463","logan_dvprTasks":{"taskID":"1463","dvprID":"8","taskName":"Schedule Kick-off meeting via e-mail","taskNumber":"8","DisplayOrder":"2","taskNotes":"SO 11002, Job 011002-1-1","miscTaskName":"","taskCompleteYN":"","required":"1","responsibleID":"9","resourceID":"27","estTime":"0","estStartDate":"07/18/2016","loggedIn":"N","lastLoginDate":null,"estCompDate":"07/18/2016"},"logan_dept_DVPR":{"department":"Project Management"},"logan_user_DVPR":{"fullName":"Brandon Satterfield"},"logan_dvpr":{"dvprNumber":"2016009","jobNumber":"011002-1-1","customerDesc":"Chevron Dolly for CTLF"},"TaskName":"Schedule Kick-off meeting via e-mail","TaskNotes":"SO 11002, Job 011002-1-1","loggedIn":"N","TaskNumber":8,"logan_dvpr_timeLog":[{"logan_dvpr_timeLog":{"taskID":"1463","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}},{"logan_dvpr_timeLog":{"taskID":"1463","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}},{"logan_dvpr_timeLog":{"taskID":"1463","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}},{"logan_dvpr_timeLog":{"taskID":"1463","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}}]},{"DT_RowId":"row_1464","logan_dvprTasks":{"taskID":"1464","dvprID":"8","taskName":"Formal Project Kick off meeting","taskNumber":"10","DisplayOrder":"3","taskNotes":"","miscTaskName":"","taskCompleteYN":"","required":"1","responsibleID":"9","resourceID":"27","estTime":"4","estStartDate":"07/19/2016","loggedIn":"N","lastLoginDate":null,"estCompDate":"07/19/2016"},"logan_dept_DVPR":{"department":"Project Management"},"logan_user_DVPR":{"fullName":"Brandon Satterfield"},"logan_dvpr":{"dvprNumber":"2016009","jobNumber":"011002-1-1","customerDesc":"Chevron Dolly for CTLF"},"TaskName":"Formal Project Kick off meeting","TaskNotes":"","loggedIn":"N","TaskNumber":10,"logan_dvpr_timeLog":[{"logan_dvpr_timeLog":{"taskID":"1464","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}},{"logan_dvpr_timeLog":{"taskID":"1464","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}},{"logan_dvpr_timeLog":{"taskID":"1464","userID":"38","loggedINYN":"N","timeOUT":"11/5/2016","timeIN":"11/5/2016"}}]},

allan

This is the key part:

                "logan_dvpr_timeLog": [{
                    "logan_dvpr_timeLog": {
                        "taskID": "1462",
                        "userID": "38",
                        "loggedINYN": "N",
                        "timeOUT": "11/5/2016",
                        "timeIN": "11/5/2016"
                    }
                }]

To access loggedINYN from that you would need to use:

data: 'logan_dvpr_timeLog.0.logan_dvpr_timeLog.loggedINYN

There are two things worth noting here - 1. You'd get an error if the array was empty. There would be missing data if there was more than one entry in the array.

You could use:

data: 'logan_dvpr_timeLog[, ].logan_dvpr_timeLog.loggedINYN

to address that. i.e. get all entries from the array and comma separate them in the display.

Allan