Below is the SQL query (not that it will make much sense) but let me explain what is going on and where I think the issue is happening)..
The grid (lets call it "miles") that is built off of this query has an argument (:qtr) which is always prompted for and is a dropdown box of options, this "miles" grid (and the accompanying prompt dialog) is opened using a button on a separate grid (lets call it "trucks") that is housed in each row. When "miles" is opened I am wanting to filter based on the "tripsheets.truckID" value set equal to a value in the grid row of "trucks" using the filter option in the action javascript to open a grid component. When I click the button the first time it asks for a value for ":qtr" which I pick and hit submit. The "miles" grid opens but it isn't filtered (using a filter in the open grid component action javascript). However if I close the window and hit the button in the "trucks" grid (doesn't matter what row) I am not prompted to pick a ":qtr" and the "miles" grid opens and is filtered exactly as I want using the correct value from the "trucks" grid.
I can continue closing the "miles" grid and re-opening it using the different rows in the "trucks" grid and it will continue working until I close the tab holding the "trucks" grid and re-open it. The really weird thing is this all works perfectly fine in working preview but becomes broken in live preview (and also once published). However, the other weird thing is in live preview it always prompts for a ":qtr" value and therefore never works even on subsequent calls to open the "miles" grid.
Any help would be great! I'm not sure if it is the fact that the query uses an argument (:qtr) that is prompted for or if this is just a freak accident.
SELECT tripsheets.company_id AS company_id, tripsheets.truckId AS truckId, trucks.UnitNumber AS UnitNumber, trucks.VIN AS VIN, Sum(tt_miles_per_state.miles_driven) AS sum_miles, Sum(tt_miles_per_state.toll_miles) AS sum_toll_miles, tt_miles_per_state.state_driven AS state_driven, FUEL_GAL_PER_STATE(tripsheets.truckId, tt_miles_per_state.state_driven, :qtr) AS fuel_gal_state, FUEL_EXP_PER_STATE(tripsheets.truckId, tt_miles_per_state.state_driven, :qtr) AS fuel_exp_state
FROM (tripsheets tripsheets
INNER JOIN tt_miles_per_state tt_miles_per_state
ON tt_miles_per_state.trip_id = tripsheets.ID
INNER JOIN trucks trucks
ON tripsheets.truckId = trucks.ID )
WHERE tripsheets.DispatchDate >= QTR_DATE_START(:qtr) AND tripsheets.DeliveryDate <= QTR_DATE_END(:qtr) AND tripsheets.company_id = :company_id
GROUP BY tripsheets.truckId, tt_miles_per_state.state_driven
ORDER BY tripsheets.company_id, tripsheets.truckId
The grid (lets call it "miles") that is built off of this query has an argument (:qtr) which is always prompted for and is a dropdown box of options, this "miles" grid (and the accompanying prompt dialog) is opened using a button on a separate grid (lets call it "trucks") that is housed in each row. When "miles" is opened I am wanting to filter based on the "tripsheets.truckID" value set equal to a value in the grid row of "trucks" using the filter option in the action javascript to open a grid component. When I click the button the first time it asks for a value for ":qtr" which I pick and hit submit. The "miles" grid opens but it isn't filtered (using a filter in the open grid component action javascript). However if I close the window and hit the button in the "trucks" grid (doesn't matter what row) I am not prompted to pick a ":qtr" and the "miles" grid opens and is filtered exactly as I want using the correct value from the "trucks" grid.
I can continue closing the "miles" grid and re-opening it using the different rows in the "trucks" grid and it will continue working until I close the tab holding the "trucks" grid and re-open it. The really weird thing is this all works perfectly fine in working preview but becomes broken in live preview (and also once published). However, the other weird thing is in live preview it always prompts for a ":qtr" value and therefore never works even on subsequent calls to open the "miles" grid.
Any help would be great! I'm not sure if it is the fact that the query uses an argument (:qtr) that is prompted for or if this is just a freak accident.
SELECT tripsheets.company_id AS company_id, tripsheets.truckId AS truckId, trucks.UnitNumber AS UnitNumber, trucks.VIN AS VIN, Sum(tt_miles_per_state.miles_driven) AS sum_miles, Sum(tt_miles_per_state.toll_miles) AS sum_toll_miles, tt_miles_per_state.state_driven AS state_driven, FUEL_GAL_PER_STATE(tripsheets.truckId, tt_miles_per_state.state_driven, :qtr) AS fuel_gal_state, FUEL_EXP_PER_STATE(tripsheets.truckId, tt_miles_per_state.state_driven, :qtr) AS fuel_exp_state
FROM (tripsheets tripsheets
INNER JOIN tt_miles_per_state tt_miles_per_state
ON tt_miles_per_state.trip_id = tripsheets.ID
INNER JOIN trucks trucks
ON tripsheets.truckId = trucks.ID )
WHERE tripsheets.DispatchDate >= QTR_DATE_START(:qtr) AND tripsheets.DeliveryDate <= QTR_DATE_END(:qtr) AND tripsheets.company_id = :company_id
GROUP BY tripsheets.truckId, tt_miles_per_state.state_driven
ORDER BY tripsheets.company_id, tripsheets.truckId