List Control - Server-Side Filter Expression

[Pizzadude]

I have a list control in a UX, based on a mySQL table.

I want to apply server-side filters, as in Selwyn's video http://www.alphasoftware.com/video-l...&search=filter

I am using Action Javascript List Control Actions - Filter list - Server-side.

I 'm having trouble with the filter expression when I try to filter on a column in a joined table. It works fine using a column in the parent table: Status = 'Booked' where Status is the column name and Booked is the value I want to match.

Here is the sql query from the list builder:

SELECT DISTINCT loads.Load_ID, loads.FreightBillNum, loads.ShipFrom, loads.DeliverTo, loads.Status, loads.DateRec, stops.Stops_PK, stops.PUorDEL, stops.StopDate 
FROM loads loads
	 INNER JOIN stops stops
		 ON  loads.Load_ID = stops.Loads_FK  
WHERE loads.DateRec BETWEEN :whatStartDate AND :whatEndDate 
GROUP BY loads.DateRec, loads.Status, loads.Load_ID 
ORDER BY loads.FreightBillNum

The parent table is the loads table and the child is the stops table.

I have unbound date controls on the UX called StartDate and EndDate that are supplying the argument values for :whatStartDate and :whatEndDate.

I want the list to return a loads record if any of the child stops records has a value in the StopDate column that matches the StartDate control.

The filter expression should be something like: stops.StopDate = {StartDate} - but that does not work.

[Pizzadude]

Re: List Control - Server-Side Filter Expression

It seems the issue here is not that the filter expression fails to recognize the StopDate column because it is in a joined child table.

If I use this argument in the filter: 11-22-2016|||D|whateverDate

And then use this filter expression: stops.StopDate = :whateverDate - it works just fine. But it is hard coding the date.

I need to use a date from a control where the user can select a date.

[lgrupido]

Re: List Control - Server-Side Filter Expression

How is your argument :whatStartDate set up? You can set the value of an argument to be set from the value of a control. Then if you change the control it should update the argument if you refresh the list.

[Pizzadude]

Re: List Control - Server-Side Filter Expression

Apparently the value of a control on a UX is not available to the List Control Action - Filter list - Server-side, to be used as an argument.

At least I have not figured out how to pass the value of a control to the builder.

The builder allows arguments, but it only seems to allow explicit values, like my example above: 11-22-2016|||D|whateverDate

[lgrupido]

Re: List Control - Server-Side Filter Expression

You cannot use a control directly, but you CAN use an argument and the argument value can be set to the value of a control. I am not at my computer otherwise I would send you an image.

[Pizzadude]

Re: List Control - Server-Side Filter Expression

When you get a chance, I would like to know how to do that.