Next and Previous Record IDs based on derived sort

Question

Hi.Table A contains circa 1000 constantly changing records.The order of records in this table (for various purposes) is determined by a sort field B (numeric formula based upon a couple of other fields).&nbsp;Just being able to report the records in the right order in a report is not sufficient.I need each record to 'know' the record ID of its predecessor record and successor record (based upon the sort field).I've tried everything I can think of to do this (reverse looks to a master record etc), but can't work it out.Sure I've seen a solution to something like this in a previous post based upon creating a connected table, but cannot find the post and I'm now pulling my hair out, having tried everything I can think of (reverse lookups - master parent etc).No doubt I'm missing something obvious!A nudge in the right direction would be massively appreciated!ThanksDavid------------------------------dmlaycock2000 dmlaycock2000------------------------------

markshnier__you · Answer

Is the sort field unique?------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

systemadmin4 · Answer

Hi Mark,Yep it's unique - and I've used it to create a connected table with that sortfield as the unique key (with the record ID of the original record in Table A in there too). Both are numerics.Feels like I'm heading in the right direction with this but my brain just can't figure out where to go next with this!D------------------------------dmlaycock2000 dmlaycock2000------------------------------

markshnier__you · Answer

What happens if you export to another table called COPY table. I am wondering if when you do a saved table to table import it will respect the default sort of the table and the records will come across in the correct sequence.

If that works you could have a hourly process run by a pipeline which word delete the records in the other table and reload them. You would set the key field in that other table to be the sort field.

Then you would have a relationship back to your real table based on that sort field and you would be able to coax the Record ID from your real table up into the Copy table.

The records and the Copy table will be sequential. So you can have a relationship of the Copy table to itself to look up the previous or next record ID and then get the record ID from your main table of the previous and next records into the Copy table.

Then lookup those Record ID values back down to the main table.

systemadmin4 · Answer

Hi Mark,Thanks for the nudge.Managed to implement a solution based upon your recommendation.Pipelines took a lot of getting used to (i hadn't used it before) and nearly ended up resorting to Zapier, but perseverance paid off.I'm running the refresh daily (which should suffice). I've found that the copy table does need to be purged before each import, and I've had to set up the import as an import CSV from a CSV report with the records sorted in the order I need, to make sure they always end up in the copy table in the correct order, with sequential record IDs and no gaps.As you say, once this was in place, it was relatively straight forward:- Create a Record ID+1 and Record ID-1 field in the copy table- Relate my original table to my copy using the Table A record ID (which I included in the import)- Pull the RID+1 and -1 down to the original record, then relate these back to the copy, to allow me to get the Table A Record IDs for the previous and next record.- I also pulled in a few other fields into the original import which mean I don't need to create further relationships to get other info via the Table A Record IDs.All works fine in the end - not 100% live, but a daily refresh will be more than sufficient.
Thanks for your help with this Mark.
David&nbsp;------------------------------dmlaycock2000 dmlaycock2000------------------------------

markshnier__you · Answer

Glad to hear you got it working.&nbsp;
I would guess you needed to use a pipeline to call an API to do the purge------------------------------Mark Shnier (YQC)Quick Base Solution ProviderYour Quick Base Coachhttp://QuickBaseCoach.commark.shnier@gmail.com------------------------------

Forum Discussion

Next and Previous Record IDs based on derived sort

Related Content

Previous and Next Record Buttons

A way to look up a previous Record ID's [Date Created] Field

Custom Record Titles

Using Grid Edit to Edit Multiple Records at Once

Record Details