How can I limit the table view data with date range selectors

[snawaz]

Dear Gents,

I need your help to figure out how to limit the table view data using date range selectors. I only want to load the data falling within a pre-selected date range.

Additionally, I prefer not to use the default filters. I'm aware that I can add a filter in table_name_init(&$options, $memberInfo, &$args), but if I were to use the default filters, I would like them to update the table data accordingly.

Below is a screenshot of my table view, where I have already added the date range selector. When I open the table view, I want it to check the date range from the date range selector and then perform the query to load the table data.

I apologize if my question is still unclear. Feel free to ask for clarification before suggesting any code implementations. Thank you

tableView_updated_query.png (123.92 KiB) Viewed 12255 times

[snawaz]

Dear Gents,

I have resolved my query with the provided solution below, which is currently functioning as expected. However, you can see that the start date is hardcoded within the implementation. My objective now is to dynamically retrieve the start date from the date range selectors displayed in the previous snapshot. I would greatly appreciate your assistance in achieving this integration. Thank you for your support.

Code: Select all

function incident_report_init(&$options, $memberInfo, &$args)
{

	$options->QueryWhere = $options->QueryWhere ? $options->QueryWhere . " AND " : "WHERE ";
	$conditions = [
	  "`incident_report`.`start_date` = '2024-04-09'"
	];
	$options->QueryWhere .= implode(" AND ", $conditions); 
	

	return TRUE;
}

[pbottcher]

Hi,

you are on the right track . In order to make it dynamically you need to get the data for your date fields for the start and end date and use them in the query.

You can access the values via the $_REQUEST when you send the data from the tableview with the values of your two date fields.

Hope that helps

[snawaz]

Hello pbottcher,

I have implemented custom date fields on top of the table using the hooks header() function. It seems that to access the data from these custom date fields, I'll need to utilize JavaScript/JQuery. Consequently, I'm wondering how I can send the data from the table view along with the values of these two custom date fields.

Initially, I can use hardcoded dates to limit the table data, which works fine. However, once the table is loaded in the table view, I aim to filter the table data based on the selected dates from my custom date fields.

For instance, I can retrieve the dates with the following code snippet from "footer-extras":

Code: Select all

var start_DateValue = $j('#start_date').val();
var end_DateValue = $j('#end_date').val();

Now, my question is: how can I use these date values within the hooks init() function to reload the table data based on the provided dates? I'm unsure whether this can be accomplished directly within the init() function or if I need to write a separate function in the table hooks file or even create a new file for this purpose.

I apologize if my question seems illogical, as I am not a professional developer; I pursue this as a hobby. I would greatly appreciate it if you could provide a prototype demonstrating the implementation of my query. Thank you.

[pbottcher]

Hi,
assuming that you create input fields with the names start_date and end_date.

You can try in your _init function something like:

Code: Select all

	$conditions.=" AND `incident_report`.`start_date` >='".makeSafe($_REQUEST['start_date'])."'"; 
	$conditions.=" AND `incident_report`.`end_date` <='".makeSafe($_REQUEST['end_date'])."'"; 
	$options->QueryWhere = $options->QueryWhere ? $options->QueryWhere . "  " : "WHERE 1=1 ";
	$options->QueryWhere .= $conditions;

	return TRUE;

The code is not tested and you may need to adjust it to your needs. You may need to convert the start_date and end_date to comply with the mysql date format.

[xbox2007]

hello
please can you share full Code for add this item and search code on table view

tableView_updated_query.png (146.13 KiB) Viewed 9786 times

thanks a lot