How To Create Custom Advance Search?

[hadian]

Hi,

I need help with PHPmaker 2019,
I have a View in MySQL with the following query:

SELECT
customers.id AS id,
customers.code AS code,
customers.name AS name,
customers.phone AS phone,
SUM((orders.price * orders.qty)) AS total
FROM
customers
JOIN order ON customers.id = order.customers_id
JOIN orders ON order.id = orders.order_id
WHERE
order.date BETWEEN 'start' AND 'end'
GROUP BY
customers.id,
customers.code,
customers.name,
customers.phone

If you look at the WHERE line,
I want this line

(BETWEEN 'start' AND 'end' order.date)

can be searched dynamically using Advance Search, Search cannot be displayed because I did not call order.date Field on SELECT the problem is i dont want to show order.date field to be displayed on List, because if displayed in total it will be separated by date, which I want is the total to be rounded to 1 customer 1 total with the timeframe specified by order.date, is there a way to make custom advance searches if possible? thank you.

[arbei]

You may use server event such as Recordset_Selecting event to change the WHERE clause, see the topic Server Events and Client Scripts in the help file.

[mobhar]

hadian wrote:
i dont want to show order.date field to be displayed on List, because if displayed
in total it will be separated by date

You may actually hide the "date" field in List Page and show it in Extended/Advanced Search. In other words, you should keep include the "date" field in your SELECT query of your Database View.