31:T71a,# Table with Search and Filters This notebook is a proof-of-concept (POC) to test out Observable's data [table input](https://github.com/observablehq/inputs/blob/main/README.md#Table) and interactive [user interface components](https://github.com/observablehq/inputs/blob/main/README.md) with the following features: - **sortable columns**: a built-in feature of [table](https://github.com/observablehq/inputs/blob/main/README.md#Table); click column name - **automatic column widths**: built-in property; full column names fit inside columns (with horizontal scroll bar displayed if needed) - **search bar**: added through [Inputs.search](https://github.com/observablehq/inputs/blob/main/README.md#search) - **show/hide columns list**: added through multi-select [Inputs.select](https://github.com/observablehq/inputs/blob/main/README.md#Select); allows to select/de-select columns to display; - **filter menu**: opens a set of filter inputs ([range slider](https://github.com/observablehq/inputs/blob/main/README.md#Range), [drop-down menu]((https://github.com/observablehq/inputs/blob/main/README.md#Select)) for numerical columns **Customized Feature:** - **pagination**: POC for basic pagination buttons. More functionality can be added as required **Data** Test data is a subset (first 87 rows) from Public Sector Salary Disclosure 2020 on ontario.ca. Data is read in from a csv file in this demo, but can be called by API when the data API becomes available. **Export** The notebook elements can be exported to HTML and assembled and styled as desired. **Ref** - originally forked from Observable [search-table template notebook](https://observablehq.com/@observablehq/search-table).32:T1045,// https://observablehq.com/@observablehq/introduction-to-views viewof pages = { // Find which column has been clicked for sorting, and the direction of the sort // "▾" : descending sort (currentReverse = true) // "▴" : ascending sort (currentReverse = false) function clickedCol() { let currentReverse = false; let thisCol; d3.selectAll("th span")._groups[0].forEach((d, i) => { if (d.innerHTML) { thisCol = d.nextSibling.data; // name of clicked col currentReverse = d.innerHTML === "▴" ? false : true; // direction of sort } }); return [thisCol, currentReverse]; } const rowsPerPage = page_settings.rowsPerPage + 1; const numPagesGeneral = Math.ceil(search.length / rowsPerPage); const numPages = (search.length <= (numPagesGeneral - 1) * rowsPerPage) ? numPagesGeneral - 1 : numPagesGeneral; // Draw the buttons in a div const element = html`

${Array.from({ length: numPages }).map( (_, i) => { let thisClass = i === 0 ? "btnactive" : "pgbtn" return html``; } )}

`; // Initial return values for pg0 const initSortCol = "Salary paid" const initSortReverse = false; element.value = [search, // no limits needed since pg0 cutoff by "rows" option of Inputs.table initSortCol, initSortReverse, // initially sort ascending ]; // Define button click event element.onclick = (d) => { // Store id of previous and currently clicked button const prevClicked = d3.select(".btnactive").attr("id"); const nowClicked = d.target.id; // Get action label from button div // For later, to distiguish between page buttons and "Previous", "Next" and "Last" buttons // when they get set up let thisAction = d3.select(`#${d.target.id}`).attr("action"); // Handle Previous and Next buttons separately if (thisAction === "page") { if (nowClicked === prevClicked) { return; // do nothing if button now clicked is already clicked } else { // Get name of column that table is sorted by (initally "Salary paid") let thisCol = clickedCol()[0]; // Get sort direction let currentReverse = clickedCol()[1]; // Page clicked let thisPg = parseInt(d.target.id.split("pg")[1]); // Deactivate previously clicked button & activate current button d3.select(`#${prevClicked}`).attr("class", "pgbtn"); d3.select(`#${nowClicked}`).attr("class", "btnactive"); // Define starting row of data array for selected page let lim0 = thisPg * (rowsPerPage); // Define end row of data array for selected page. If end row // is > length of array, stop at length of array. let lim1 = lim0 + rowsPerPage - 1; //(lim0 + rowsPerPage) > search.length ? search.length : lim0 + rowsPerPage; // Assign row limits to returned array let sortedSearch = {}; if (typeof search[0][thisCol] == "string") { sortedSearch = currentReverse ? search.sort((a, b) => (a["First name"] < b["First name"] ? 1 : -1)) : search.sort((a, b) => (a["First name"] > b["First name"] ? 1 : -1)) } else if (typeof search[0][thisCol] == "number") { sortedSearch = currentReverse ? search.sort((a, b) => b[thisCol] - a[thisCol]) : search.sort((a, b) => a[thisCol] - b[thisCol]); } // Set up return values in array element.value = [d.target.id === "pg0" ? sortedSearch : sortedSearch.slice(lim0, lim1), // returned, sorted page data thisCol, // col to sort by currentReverse, // sort direction ]; } } element.dispatchEvent(new Event("input", {bubbles: true})); }; return element; }33:T846,34:T96d,// https://observablehq.com/@observablehq/introduction-to-views viewof testlims = { const pages = Math.ceil(data.length / page_settings.rowsPerPage); const element = html`

${Array.from({ length: pages }).map( (_, i) => { let thisClass = i === 0 ? "btnactive" : "pgbtn" return html``; } )}

`; element.value = [0, 13]; element.onclick = (d) => { const prevClicked = d3.select(".btnactive").attr("id"); const nowClicked = d.target.id; // Get action label from button div let thisAction = d3.select(`#${d.target.id}`).attr("action"); // Handle Previous and Next buttons separately if (thisAction === "page") { if (nowClicked === prevClicked) { return; } else { console.log("display new page") // Page clicked let thisPg = parseInt(d.target.id.split("pg")[1]); // Define starting row of data array for selected page let lim0 = thisPg * (page_settings.rowsPerPage + 1); // Define end row of data array for selected page. If end row // is > length of array, stop at length of array. let lim1 = (lim0 + page_settings.rowsPerPage) > data.length ? data.length : lim0 + page_settings.rowsPerPage; // Assign row limits to returned array element.value = [lim0, lim1]; } } else if (thisAction === "last") { const lastPg = Math.ceil(data.length / page_settings.rowsPerPage) - 1; const lim0 = lastPg * (page_settings.rowsPerPage + 1); const lim1 = (lim0 + page_settings.rowsPerPage) > data.length ? data.length : lim0 + page_settings.rowsPerPage; // Assign last page row limits to returned array element.value = [lim0, lim1]; } element.dispatchEvent(new Event("input", {bubbles: true})); }; return element; }35:[{"id":"94fc285b09ae99fa2a19d2ece526fec83eaa472ee4e552648a8a6ef960162e4fffb9726d5b053168f816ba7e5709e9551e18fd3ec9df15ee7989388dc4f720ef","href":"https://static.observableusercontent.com/files/94fc285b09ae99fa2a19d2ece526fec83eaa472ee4e552648a8a6ef960162e4fffb9726d5b053168f816ba7e5709e9551e18fd3ec9df15ee7989388dc4f720ef","name":"test-tbs-pssd-compendium-en-2020.csv","type":"text/csv","lastModified":1640271008520.348,"size":10825},{"id":"cb6f8a8f101404540b6ed0481225b08c99c231f46e52a359f90e0bdd85aec35179d917e827da7718774901d109a102715a70726168eb8fa5ccc1c4f94496b9cf","href":"https://static.observableusercontent.com/files/cb6f8a8f101404540b6ed0481225b08c99c231f46e52a359f90e0bdd85aec35179d917e827da7718774901d109a102715a70726168eb8fa5ccc1c4f94496b9cf","name":"Screen Shot Observable Demo Table.png","type":"image/png","lastModified":1641825164453.291,"size":257862}] 2f:{"title":"Table with Search and Filters","cells":[{"id":4,"value":"$31","pinned":false,"hidden":false,"mode":"md"},{"id":2329,"value":"### Load data\nData read from attached csv file, but can be obtained via API using e.g. `d3.json(URL)` (see [d3 fetch](https://github.com/d3/d3-fetch) options). ","pinned":false,"hidden":false,"mode":"md"},{"id":3138,"value":"initSortCol = \"Salary paid\"","pinned":false,"hidden":false,"mode":"ojs"},{"id":2486,"value":"data = {\n const text = await FileAttachment(\"test-tbs-pssd-compendium-en-2020.csv\").text();\n // Clean currency columns values and cast them to number; sort final data rows by initSortCol\n return d3.csvParse(text, (d) => ({\n Sector: d.Sector,\n \"Last name\": d[\"Last name\"],\n \"First name\": d[\"First name\"],\n \"Salary paid\": +d[\"Salary paid\"].trim().split(\"$\")[1].replace(/,/g, ''),\n \"Taxable benefits\": +d[\"Taxable benefits\"].trim().split(\"$\")[1].replace(/,/g, ''),\n Employer: d.Employer,\n \"Job title\": d[\"Job title\"],\n \"Calendar year\": +d[\"Calendar year\"]\n })).sort((a, b) => a[initSortCol] - b[initSortCol]);\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":3266,"value":"### Demo","pinned":false,"hidden":false,"mode":"md"},{"id":6,"value":"viewof search = {\n let n = Inputs.search(\n data\n .filter(d => {\n // Filter by Salary Paid\n let thisSal = d[\"Salary paid\"];\n // Filter by Taxable Benefits\n let thisTax = d[\"Taxable benefits\"];\n \n return (salCond === \"> =\" ? thisSal >= filterSal : thisSal <= filterSal) && \n (taxCond === \"> =\" ? thisTax >= filterTax : thisTax <= filterTax);\n }),\n {label: \"Search\", width: 500}\n )\n\n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":116,"value":"viewof selCol = Inputs.button(\"Show/Hide Columns\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":1665,"value":"// Create Show/Hide Column dropdown menu\nviewof theseCols = {\n let n = Inputs.select(data.columns, {\n //label: \"Select Columns\", \n multiple: true,\n value: data.columns\n })\n\n // Add an id to element, set col width, and set toggle class to false\n d3.select(n).attr(\"id\", \"colMenuBox\")\n .attr(\"class\", \"col-tog col-md-2\")\n .classed(\"active\", false);\n\n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":411,"value":"viewof filterBtn = Inputs.button(\"Filter\")","pinned":false,"hidden":false,"mode":"ojs"},{"id":1503,"value":"viewof salLabel = {\n let n = html`\n

Salary paid:

`;\n\n // Set toggle class to false\n d3.select(\"#salLabel\")\n .classed(\"active\", false);\n\n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":977,"value":"// Create Condition dropdown menu for Salary Paid\nviewof salCond = {\n let n = Inputs.select([\"> =\", \"< =\"], {\n value: [\"> =\"],\n label: \"Condition\",\n width: condWidth\n })\n \n // Add an id to element, set col width, and set toggle class to false\n d3.select(n).attr(\"id\", \"salCond\")\n .attr(\"class\", \"tog col-md-2\")\n .classed(\"active\", false);\n \n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1145,"value":"viewof filterSal = {\n const incr = 10;\n \n let n = Inputs.range(\n [parseInt(data_settings.salaryMin), Math.floor(data_settings.salaryMax / incr) * incr], \n {\n step: 1, \n //label: \"Salary paid:\", \n value: parseInt(data_settings.salaryMin),\n //format: x => x.toLocaleString()\n //step: incr,\n //width: sliderWidth\n }) \n\n // Add an id to element, set col width, and set toggle class to false\n d3.select(n).attr(\"id\", \"filterSal\")\n .attr(\"class\", \"tog col-md-6\")\n .classed(\"active\", false);\n \n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1537,"value":"viewof taxLabel = {\n let n = html`\n

Taxable benefits:

`;\n\n // Set toggle class to false\n d3.select(\"#taxLabel\")\n .classed(\"active\", false);\n\n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1217,"value":"// Create Condition dropdown menu for Taxable Benefits\nviewof taxCond = {\n let n = Inputs.select([\"> =\", \"< =\"], {\n value: [\"> =\"],\n label: \"Condition\",\n width: condWidth\n })\n \n // Add an id to element, set col width, and set toggle class to false\n d3.select(n).attr(\"id\", \"taxCond\")\n .attr(\"class\", \"tog col-md-2\")\n .classed(\"active\", false);\n \n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":1186,"value":"viewof filterTax = {\n const incr = 10;\n let n = Inputs.range([parseInt(data_settings.taxMin), Math.floor(data_settings.taxMax / incr) * incr], {\n step: 1, \n //label: \"Taxable benefits:\", \n value: parseInt(data_settings.taxMin),\n //step: incr,\n width: sliderWidth\n })\n\n // Add an id to element, set col width, and set toggle class to false\n d3.select(n).attr(\"id\", \"filterTax\")\n .attr(\"class\", \"tog col-md-6\")\n .classed(\"active\", false);\n \n return n;\n}","pinned":false,"hidden":false,"mode":"ojs"},{"id":2979,"value":"viewof mytable = Inputs.table(\n pages[0], \n{\n columns: theseCols,\n rows: page_settings.nrows,\n sort: pages[1],\n reverse: pages[2],\n format: {\n \"Salary paid\": d3.format(\"($,.2f\"),\n \"Taxable benefits\": d3.format(\"($,.2f\"),\n \"Calendar year\": d3.format(\"d\") // format as \"2020\" rather than \"2,020\"\n}\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":3324,"value":"pages","pinned":true,"hidden":false,"mode":"ojs"},{"id":2093,"value":"$32","pinned":true,"hidden":false,"mode":"ojs"},{"id":3270,"value":"### Export to HTML\nThe demo can be exported to HTML and all elements can be assembled as desired. Screenshot:","pinned":false,"hidden":false,"mode":"md"},{"id":3274,"value":"md`${await FileAttachment(\"Screen Shot Observable Demo Table.png\").image()}`","pinned":false,"hidden":false,"mode":"ojs"},{"id":2403,"value":"### Settings\nSettings for inputs.","pinned":false,"hidden":false,"mode":"md"},{"id":1267,"value":"condWidth = 100; // width of Condition dropdown menu","pinned":false,"hidden":false,"mode":"ojs"},{"id":1272,"value":"sliderWidth = 300; // width of slider input box","pinned":false,"hidden":false,"mode":"ojs"},{"id":2380,"value":"// for current table display styles, setting rows=30 in table option displays 13 rows on the page\npage_settings = ({\n nrows: 32,\n rowsPerPage: 32/2 - 2\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":2412,"value":"data_settings = ({\n salaryMin: Math.min.apply(Math, data.map(function(o) { \n\t\t\t return o[\"Salary paid\"]; \n })),\n salaryMax: Math.max.apply(Math, data.map(function(o) { \n\t\t\t return o[\"Salary paid\"]; \n })),\n taxMin: Math.min.apply(Math, data.map(function(o) { \n \t\t\treturn o[\"Taxable benefits\"]; \n })),\n taxMax: Math.max.apply(Math, data.map(function(o) { \n \t\t\treturn o[\"Taxable benefits\"]; \n }))\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":951,"value":"### Functions\nFunctions to control interactivity.","pinned":false,"hidden":false,"mode":"md"},{"id":1288,"value":"{\n selCol;\n\n // Toggle active class of column menu on and off\n let els = d3.select(\".col-tog\")\n els.classed(\"active\", !els.classed(\"active\"));\n \n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":743,"value":"// Handles Filter button click\n{\n filterBtn; // registers click\n\n // Toggle active class of filter elements on and off\n let els = d3.selectAll(\".tog\")\n els.classed(\"active\", !els.classed(\"active\"));\n\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":454,"value":"### Styles","pinned":false,"hidden":false,"mode":"md"},{"id":30,"value":"// https://observablehq.com/@triptych/google-fonts-in-observable\nfontName = \"Open Sans\";","pinned":true,"hidden":false,"mode":"ojs"},{"id":32,"value":"$33","pinned":true,"hidden":false,"mode":"html"},{"id":503,"value":"### Modules","pinned":false,"hidden":false,"mode":"md"},{"id":1414,"value":"// https://getbootstrap.com/docs/4.1/getting-started/introduction/#css\nhtml`css `","pinned":true,"hidden":false,"mode":"ojs"},{"id":511,"value":"bootstrap = require('bootstrap')","pinned":false,"hidden":false,"mode":"ojs"},{"id":417,"value":"## Tests","pinned":false,"hidden":false,"mode":"md"},{"id":637,"value":"$34","pinned":true,"hidden":false,"mode":"ojs"},{"id":1450,"value":"html`\n

Salary paid

greater than

slider

`;","pinned":true,"hidden":false,"mode":"ojs"},{"id":1044,"value":"## References\n\n- https://observablehq.com/@observablehq/introduction-to-views\n- [Inputs.search](https://github.com/observablehq/inputs/blob/main/README.md#search)\n- [Inputs.table](https://github.com/observablehq/inputs/blob/main/README.md#table)\n- [d3.format](https://observablehq.com/@d3/d3-format)","pinned":false,"hidden":false,"mode":"md"},{"id":2241,"value":"## Notes","pinned":false,"hidden":false,"mode":"md"},{"id":2632,"value":"- load csv file straight up: \n `rawdata = FileAttachment(\"test-tbs-pssd-compendium-en-2020.csv\").csv()`","pinned":false,"hidden":false,"mode":"md"},{"id":1047,"value":"- Table with filter:\n\n`Inputs.table(athletes.filter(d => d.weight < weight * 1.1 && weight * 0.9 < d.weight), {sort: \"weight\", columns})`\n\nhttps://observablehq.com/@observablehq/hello-inputs","pinned":false,"hidden":false,"mode":"md"},{"id":1350,"value":"ns = Inputs.text().classList[0]","pinned":false,"hidden":false,"mode":"ojs"},{"id":3256,"value":"// html = htl.html","pinned":true,"hidden":false,"mode":"ojs"},{"id":369,"value":"- Styling for the columns menu to make it pop out.\n\n```\n\n```\n\nFor this notebook, it is better to keep the dropdown menu inline","pinned":false,"hidden":false,"mode":"md"},{"id":2619,"value":"- If csv file column names have no spaces, can read in by:\n\n```\ndata = {\n const text = await FileAttachment(\"temperature.csv\").text();\n const parseDate = d3.utcParse(\"%Y-%m-%d\");\n return d3.csvParse(text, ({date, temperature}) => ({\n date: parseDate(date),\n temperature: +temperature\n }));\n}\n```\nfrom: [Learn D3 notebook](https://observablehq.com/@d3/learn-d3-data)","pinned":false,"hidden":false,"mode":"md"},{"id":2652,"value":"- when CSV column names have spaces, pass in `d`:\n```\nconst data = d3.csvParse(string, (d) => {\n return {\n year: new Date(+d.Year, 0, 1), // lowercase and convert \"Year\" to Date\n make: d.Make, // lowercase\n model: d.Model, // lowercase\n length: +d.Length // lowercase and convert \"Length\" to number\n };\n});\n```\n\n\nfrom [d3-dsv documentation](https://github.com/d3/d3-dsv#dsv_parse)","pinned":false,"hidden":false,"mode":"md"},{"id":3149,"value":"- number format:","pinned":false,"hidden":false,"mode":"md"},{"id":3146,"value":"(1234).toLocaleString(\"en-US\", {\n style: \"currency\",\n currency: \"USD\"\n})","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W35","stdlib":"1","titling":"auto"}