I have a page, with a table and several drop downs for the user to customize the query and table data.
From there, I have a popup to modify the data, the ID is passed to the popup, a WHERE ID = X is put in the a new data set and everything is working as intended.
I’m trying to create a “next” and “previous” button on the popup, to cycle through the custom queried results.
I can pass the entire dataset in a new dataset parameter but I’m having trouble figuring out what will need to be done done to identify the NEXT and PREVIOUS IDs.
I don’t know if you found a solution to this, but for posterity, this is what I have used before. I don’t know if there’s an easier way… Definitely open to suggestions!
This gets the next tank in a list of tanks, and cycles around to the beginning if at the end.
--Gets the next tank. If we're at the last tank, then returns the first tank
SELECT TOP 1 MIN(TankNumber) as TankNumber, MIN(TankPrefix) as TankPrefix, MIN(TankFarm) as TankFarm FROM (
SELECT TankNumber, 'A' as SORT, TankPrefix, TankFarm
FROM cfgTanks
WHERE TankNumber > {Tank Navigation.TankNumber} AND TankFarm = '{Tank Navigation.TankFarm}'
UNION ALL
SELECT TankNumber, 'B' AS SORT, TankPrefix, TankFarm
FROM cfgTanks
WHERE TankNumber = (SELECT MIN(TankNumber) FROM cfgTanks WHERE TankFarm = '{Tank Navigation.TankFarm}')
) C
GROUP BY SORT
ORDER BY SORT ASC
And this is to get the previous tank:
--Gets the previous tank. If we're at the first tank, then returns the last tank
SELECT TOP 1 MAX(TankNumber) as TankNumber, MAX(TankPrefix) as TankPrefix, MAX(TankFarm) as TankFarm FROM (
SELECT TankNumber, 'A' as SORT, TankPrefix, TankFarm
FROM cfgTanks
WHERE TankNumber < {Tank Navigation.TankNumber} AND TankFarm = '{Tank Navigation.TankFarm}'
UNION ALL
SELECT TankNumber, 'B' AS SORT, TankPrefix, TankFarm
FROM cfgTanks
WHERE TankNumber = (SELECT MAX(TankNumber) FROM cfgTanks WHERE TankFarm = '{Tank Navigation.TankFarm}')
) C
GROUP BY SORT
ORDER BY SORT ASC