Discussions

Expand all | Collapse all

Next and Previous Record IDs based on derived sort

  • 1.  Next and Previous Record IDs based on derived sort

    Posted 16 days ago
    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). 

    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!

    Thanks

    David




    ------------------------------
    dmlaycock2000 dmlaycock2000
    ------------------------------


  • 2.  RE: Next and Previous Record IDs based on derived sort

    Posted 16 days ago
    Is the sort field unique?

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Next and Previous Record IDs based on derived sort

    Posted 16 days ago
    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
    ------------------------------



  • 4.  RE: Next and Previous Record IDs based on derived sort

    Posted 16 days ago
    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. 






    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Next and Previous Record IDs based on derived sort

    Posted 15 days ago

    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 




    ------------------------------
    dmlaycock2000 dmlaycock2000
    ------------------------------



  • 6.  RE: Next and Previous Record IDs based on derived sort

    Posted 15 days ago

    Glad to hear you got it working. 

    I would guess you needed to use a pipeline to call an API to do the purge



    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Next and Previous Record IDs based on derived sort

    Posted 15 days ago
    Yep

    I call API_PurgeRecords using a simple webhook HTTP Get request.

    D

    ------------------------------
    dmlaycock2000 dmlaycock2000
    ------------------------------