In Virtual field calculation, get the next id value after a current value from a View, using Order By

[DesiLogi]

Hi,

Not sure if this is possible...

I have  a tabular report results list that uses a button (shows on each record) to send a url with the clicked record's ID value as a parameter to open a popup submission form. The submission form captures that ID value in a field and the user 'does other stuff' and submits a new record to a different table (now related to the original tabular results record the button was clicked from by that id value). 

What I need to do is, upon submission, instead of closing the submission popup, have the submission form clear but automatically go to the next record in the original tabular results list--sort of how a Details form can click through to the next record after update. This would have to be done in the Destination of the submission form, sending it back to itself BUT with the ID value of the NEXT record in the tabular results list as the parameter. 

I figure you could use a Virtual field in the submission form, set to calculation, to lookup the source View of the origin tabular datapage and get the 'next' ID value after the one the user originally clicked from, using the same Order By regime a the origin tabular results datapage uses (which has 4 OrderBy properties).

It would be something like: 

SELECT TOP 1 ItemID FROM _V_myView WHERE myAuthID = '[@authfield:Users_AuthID]' ORDER BY firstField ASC, secondField ASC, thirdField ASC, fourthField ASC

This obviously doesn't work.  I don't think 'Top 1' wouldn't get the next ItemID in the recordset AFTER the current ID value stored in the submission form, based on the 4 Order By properties. 

So for example, if the tabular datapage results list shows 6 records and the order by properties puts them in the sequence ItemID=5, ItemID=3, ItemID=7,ItemID=2, ItemID=9, ItemID=12 and the user clicks on the record with ItemID=7 to open the submission form, passing ItemID=7 as a parameter, then the submission form's Virtual field calculation needs to get ItemID=2 as the NEXT record. Then in the submission form's destination I can use that value as a parameter when refreshing the form. This way the user can 'click through' the whole recordset in sequence, when submitting. 

Any help would be greatly appreciated, if this is even doable. 

 

[vidierre]

I'm afraid you are faced with the workflow CASPIO limits. I am interested too into this.

I think it could be done if this idea from ideabox should be deployed: https://caspio.uservoice.com/forums/164206-caspio-bridge/suggestions/36533800-run-tasks-outside-of-caspio-website

Please if you agree vote it!

[DesiLogi]

Hi @vidierre,

We actually got this worked out. Here's the calculation for using 3 levels of ordering in the View that's also the source of the tabular datapage, so the user can click through and go to the next record, from a submission form--this calculation is in a Virtual field that will bring up the next ItemID (the autonumber value) in the ordered list and is used in the Destination to go to the next record in sequence.: 

SELECT Top 1 v1.ItemID

FROM _V_my_View v1

WHERE v1.PortalID = '[@authfield:Users_PortalID]'

AND (

        ISNULL(v1.Items_Client_Input_Order, '') + '_' + ISNULL(v1.Projects_Project_Name, '') + '_' + ISNULL(v1.Area, '') + ISNULL(v1.ItemNum, '')

) > ISNULL((

        SELECT TOP 1 ISNULL(Items_Client_Input_Order, '') + '_' + ISNULL(Projects_Project_Name, '') + '_' + ISNULL(Area, '') + ISNULL(ItemNum, '')

        FROM _V_my_View

        WHERE ItemID = [@ItemID]

), '')

ORDER BY v1.Items_Client_Input_Order ASC,

v1.Projects_Project_Name ASC,

v1.Area ASC,

v1.ItemNum ASC