how to pass date control value to sql in list page?

To achieve the functionality where a user first selects a date range and then the list page populates data based on that selection in PHPRunner, you can follow these steps:

1. Create Input Fields for Date Selection: In your list page, you need to add two input fields (e.g., start_date and end_date) for the user to enter the date range.

   
   

2. Modify the SQL Query: In your SQL query, you should include conditions to filter results based on the selected dates. You can use the WHERE clause to filter your results based on the input fields.

   
   

3. Use PHPRunner Variables: PHPRunner allows the use of variables in the SQL editor. You will use these variables to filter your SQL view based on the input from the user.

   
   

4. Add a Button or a Trigger: After the user selects the date range, you will need a button or a trigger that submits the selected dates and refreshes the page with the filtered results.

   
   

Here’s a step-by-step guide:

Step 1: Add Date Fields

1. In the PHPRunner, open your list page and go to the "Fields" section.
2. Add two new fields for the date selection, e.g., start_date and end_date.
3. Set these fields as input boxes (or date pickers) for user convenience.

Step 2: Modify SQL Query

1. Open the SQL query for your view in the "SQL" section of PHPRunner.
2. Modify the SQL to include the date filtering. For example:

SELECT *
FROM your_view_name
WHERE (date_column BETWEEN [start_date] AND [end_date])
Make sure to replace date_column with the name of the date column in your view.
Step 3: Define Variables
In PHPRunner, go to the "Settings" > "Variables" section.
Define variables that correspond to your input fields, e.g., start_date and end_date.

Step 4: Add a Submit Button
Add a button or link that the user can click after selecting the dates.
Configure this button to submit the form with the date parameters and refresh the list.

Step 5: Test Your Implementation
Save your changes and run the application.
Select the date range and click the button to ensure that the list page filters the data correctly based on the selected dates.

Additional Notes
If you want to make the initial load of the list page show all records until a date range is selected, you can set default values for the date fields (e.g., use the current date or leave them empty).
Consider adding validation for the date inputs to ensure users select valid dates.
You may also want to implement error handling in case no records are found for the selected date range.

By following these steps, you should be able to create a dynamic list page in PHPRunner that populates data based on a user-selected date range. If you encounter any issues or need further assistance, feel free to ask!

Prakash,

you can either point users to the Advanced Search page first ( as opposed to the List page ) or you can use 'Hide data until search' option on Miscellaneous screen.

Dear Sergey, thank you for your advice. I've already tried your suggestion but could not achieve this functionality. This results aggregate of data based on individual date and not within the Between Dates selected.
my custom sql view has this SQL. Please review and advice a way if possible!

Sql is:
WITH
-- Sales Van CTEs
sv AS (
SELECT id, date, customer_id, van_id
FROM sales_van
),
svli AS (
SELECT sv_id, itemsize_id, qty, i_amt
FROM sv_lineitems
),
-- Sales Counter CTEs
sc AS (
SELECT id, date, customer_id, branch_id
FROM sales_counter
WHERE is_c = 1
),
scli AS (
SELECT sc_id, itemsize_id, qty, i_amt
FROM sc_lineitems
)
-- Main Query using UNION ALL
SELECT
(SELECT customer_name FROM customers WHERE customers.id = sv.customer_id) as customer,
svli.itemsize_id,
SUM(svli.qty) AS total_qty,
SUM(svli.i_amt) AS total_amt,
'van' AS sale_source
FROM sv
LEFT JOIN svli ON sv.id = svli.sv_id
WHERE sv.date BETWEEN '2025-06-01' AND '2025-06-05'
GROUP BY sv.customer_id, sv.date, svli.itemsize_id

UNION ALL

SELECT
(SELECT customer_name FROM customers WHERE customers.id = sc.customer_id) as customer,
scli.itemsize_id,
SUM(scli.qty) AS total_qty,
SUM(scli.i_amt) AS total_amt,
'counter' AS sale_source
FROM sc
LEFT JOIN scli ON sc.id = scli.sc_id
WHERE sc.date BETWEEN '2025-06-01' AND '2025-06-05'
GROUP BY sc.customer_id, sc.date, scli.itemsize_id

ORDER BY total_amt DESC;

Dear Sergey,
while creating a sql view, what is the sql variable I need to use for date(between) for data population for list page?
eg: sale_date is the date field.
SELECT customer_id, sum(items), sum(amount) from customer_account
WHERE sale_date BETWEEN @search_sale_date1 AND @search_sale_date2.

Please help me get correct variable name I need to use. I've used @search_sale_date1 and @search_sale_date2 but it is not working.
appreciate your support.

with regards
Prakash

I think that you need to add this 2 virtual fields to the Fields list in the SQL view,
and then they should apear in the variable list (on the sql view screen Insert Variable) and then show this fields in the search panel or in filter panel
and be sure to tick the Hide data until search in Misc
Hope this will get you closer