Creating an attendance register report
Creating an attendance register report
Glyndwr
Posts: 117Questions: 32Answers: 0
I want to create an attendance register report that looks like this:
The information above the dates is the lesson for that day.
The JSON being returned to the jQuery is:
[{"attId":"120","cdId":"MTQ5","attMeeting":"A","attDate":"2019-01-11","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Cycling","attNewChum":"N"},{"cdId":"ODA=","cdSurname":"Alistair","cdFirstName":"Tricia","attDescription":"Running","attNewChum":"N"},{"cdId":"MTQ5","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Cycling","attNewChum":"N"},{"attId":"121","cdId":"ODA=","attMeeting":"P","attDate":"2019-01-12","cdSurname":"Alistair","cdFirstName":"Tricia","attNewChum":"N"},{"attId":"122","cdId":"MTQ5","attMeeting":"P","attDate":"2019-01-13","cdSurname":"Achilles","cdFirstName":"Tendon","attDescription":"Rowing","attNewChum":"N"},{"attId":"123","cdId":"ODA=","attMeeting":"P","attDate":"2019-01-13","cdSurname":"Alistair","cdFirstName":"Tricia","attDescription":"Rowing","attNewChum":"N"}]
The mySQL that extracts the data has a Left Join so all the names are extracted if there is a date (i.e., you can have a name followed by all columns having blanks).
Is it possible to do this?
Kind regards,
Glyn
This question has an accepted answers - jump to answer
This discussion has been closed.
Answers
Yes it is certainly possible to display a DataTable like that, but you'd need to transform the JSON into a row based structure. DataTables requires that each item (array or object) in the array of data fed to the table represent a whole row.
Allan
Hi Allan,
I have been able to transform the JSON into a row based structure:
jsonString: [{"attId":"MTIw","cdId":"MTQ5","cdSurname":"Achilles","cdFirstName":"Tendon","cdArchived":"null","attNewChum":"N","2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"A","2019-01-11":"null"},{"attId":"NzQ=","cdId":"ODA=","cdSurname":"Alistair","cdFirstName":"Tricia","cdArchived":"null","attNewChum":"N","2016-08-23":"a","2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-24":"f","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-25":"a","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-26":"Y","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-27":"a","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2016-08-28":"null","2016-09-30":"Y","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-03-04":"y","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-03-05":"Y","2017-07-05":"null","2017-07-12":"null","2019-01-11":"null","2017-07-05":"Y","2017-07-12":"null","2019-01-11":"null","2017-07-12":"Y","2019-01-11":"null","2017-07-12":"null","2019-01-11":"null"}]
I have then tried to use this example:
https://datatables.net/forums/discussion/comment/117303/#Comment_117303
However, I get the console error:
My code is:
HTML:
JSON:
Please note that the code I added from the example is:
Without this code the columns defined in the HTML are displayed. So all I need to do now is add the dynamic date columns (there can be 0 to n date columns).
Hi @Glyndwr ,
That's a lot of coding going on there. We're happy to take a look, but as per the forum rules, please link to a test case - a test case that replicates the issue will ensure you'll get a quick and accurate response. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.
Cheers,
Colin
Hi Colin,
I am not sure how to do this as I am using a json string not html embedded data. Never the less, I have tried to do it here: http://live.datatables.net/fegacevu/1/edit
Not sure what your intention is there, but I don't think that's even valid Javascript. Drop those two lines.
If you are getting a JSON parsing error from that line, then
responseJson1a
is not a valid JSON string. Use JSONLint to validate the string (i.e. addconsole.log( responseJson1a );
just before that line and then put the string into the linter.Allan
Yes, that works to complete the columns:
<th>Att. ID</th>
<th>CD ID</th>
<th>Surname</th>
<th>First Name</th>
<th>Archived Date</th>
<th>New Chum</th>
All I need is how to dynamically create the column headers for the date columns:
"2016-08-24":"null","2016-08-25":"null","2016-08-26":"null","2016-08-27":"null","2016-08-28":"null","2016-09-30":"null","2017-03-04":"null","2017-03-05":"null","2017-07-05":"null","2017-07-12":"null","2019-01-11":"A","2019-01-11":"null"},
I tried:
from the example: https://datatables.net/forums/discussion/comment/117303/#Comment_117303
However, that gave me the console error:
Without that code the first six columns are correctly populated. So I need the correct code to dynamically create the column headers for the date columns.
Kind regards,
Glyn
Did you use JSONLint as Allan recommended?
Have you updated your example?
We'd really need a test case showing the issue in this case. I'm afraid we can't really help without one.
Allan
Hi,
I very much appreciate your help. I have created another link http://live.datatables.net/kuhobodu/1/edit
I hope this is acceptable. The trouble is I do not know how to provide an input file in place of the HTML in order to better demonstrate the problem.
Your example Javascript is showing errors. They include "dataType: "json"; which Allan already suggested should be removed..
Hi Tangerine,
Thank you for your help. I have been working on this in my spare time and found a few things. I have removed the "dataType: "json"; and this makes no difference. I have also found that:
placed in the ".done" function displays the key and value and so replaces:
So now I need to find out how to use this code to generate the dynamic headings. I will continue to plug away with trial and error.
Kind regards,
Glyn
That looks like all you should need for dynamic headings. Just pass your
columns
array into thecolumns
option.Allan
Thanks for everyone's help. I have found a solution so I thought I would post it here in the hope that it helps others.
This code creates the header row:
Then the following:
Note:
data: will load the table data from your input independent of your column headings (I did not understand this, some one may be able to explain this more eloquently)
columns: loads the headings you have created above. VERY IMPORTANT do not use [varColumns] it is without the []. The joy I had when I removed the [] and it suddenly worked!!