I am working on building a report function within my database, but the feature in Knack does not seem to allow basic functions like sorting. For instance If I am displaying a field on a bar graph and it calculates the total record count. I cannot sort that total record count by high to low. I can only sort by fields in the table.
I am working on creating a top ten list for their results. So getting the record count was the first step, but now I need to sort by that record count High to Low, and that doesnt seem to be a function.
Hi @Ben3,
This is one I’ve wanted to solve for a while. I’ve developed a JS solution (with a little help from ChatGPT) that allows you to sort (asc or desc) and specify top N results that you want to see.
Code
Click here to expand for the code
$(document).on('knack-view-render.any', function (event, view, data) {
if (view.key === 'view_xx') {
sortChart({ chartId: '#kn-report-view_xx-1', topN: 5, descending: true });
sortChart({ chartId: '#kn-report-view_xx-2' });
}
});
// -----------------------------------------------------------------------------------------------
// Purpose: Sorts a Highcharts chart by value, and limits to top N results
function sortChart({ chartId, topN, descending = false, sortSeriesIndex = 0 }) {
const chart = Highcharts.charts.filter(c => c && c.renderTo && c.renderTo.closest(chartId)).pop(); // Find the latest chart in this container (last one is the newest)
if (!chart) return; // Exit if chart does not exist
const categories = chart.xAxis[0].categories;
const combined = categories.map((cat, i) => {
const sortValue = chart.series[sortSeriesIndex]?.data[i]?.y || 0;
return { category: cat, sortValue, values: chart.series.map(s => s.data[i]?.y || 0) };
});
combined.sort((a, b) => descending ? b.sortValue - a.sortValue : a.sortValue - b.sortValue); // Sort by the chosen series' value
const topData = topN ? combined.slice(0, topN) : combined; // Get top N (if specified)
chart.xAxis[0].setCategories(topData.map(d => d.category), false); // Update categories
chart.series.forEach((s, idx) => {
s.setData(topData.map(d => d.values[idx]), true);
});
}
Explanation:
view_xx - The view that contains your charts
#kn-report-view_xx-1 - The first chart’s ID within your view
#kn-report-view_xx-2 - The second chart’s ID within your view
topN: X - An optional parameter to only include the top X items
descending: true - An optional parameter to sort from highest to lowest value
sortSeriesIndex: X - An optional parameter to sort by a different series in the chart if you have multiple (default is 0)
You can identify your chart ID’s using the browser’s developer/inspect tool:
