Discussions

 View Only
Expand all | Collapse all

Lookup record before the current record

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training06-19-2018 12:24

EOM Develepors

EOM Develepors06-19-2018 21:25

QuickBaseCoach Dev./Training

QuickBaseCoach Dev./Training06-19-2018 21:26

EOM Develepors

EOM Develepors06-19-2018 21:43

  • 1.  Lookup record before the current record

    Posted 06-19-2018 02:58
    I needed to lookup a values of a record before the record I am currently on.

    I created a formula [Record ID#]-1

    I created a relationship pointing to the same table with proxy of this formula field

    This should point to the record before the current record.

    I added the fields I want to pull from the previous record but the data does not show up. When I look at the previous record it's there but its not pulling the values for some reason.

    Am I doing anything wrong?

    Thanks


  • 2.  RE: Lookup record before the current record

    Posted 06-19-2018 04:08
    the record ID of any new record is not created until the record is saved for the first time.  so, you probably want the self-relationship to include a summary field which is 'MAX - Record ID', so that you know the largest Record ID# CURRENTLY in existence


  • 3.  RE: Lookup record before the current record

    Posted 06-19-2018 04:12
    i'd point out that your formula field that is currently [recordID]-1, would change to something like if(isnull([recordID#),[MAX-Record ID],[RecordID]-1)


  • 4.  RE: Lookup record before the current record

    Posted 06-19-2018 12:14
    Thanks for your reply. I am looking at saved records where the record id was already generated. I also see the record ID -1 showing up correctly. But the values of that record are not there.


  • 5.  RE: Lookup record before the current record

    Posted 06-19-2018 12:24
    What is the reference field on the right side of the relationship?


  • 6.  RE: Lookup record before the current record

    Posted 06-19-2018 20:06
    The field that calculates my record ID - 1

    The formula looks like this: [Record ID#]-1


  • 7.  RE: Lookup record before the current record

    Posted 06-19-2018 20:13
    It should work. I have done this before.  I'm not sure what to suggest.


  • 8.  RE: Lookup record before the current record

    Posted 06-19-2018 20:58
    The problem was that the record ID was not the key field for this table.....


  • 9.  RE: Lookup record before the current record

    Posted 06-19-2018 20:59
    I asked what the field was on the right side of the relationship, but not on the left!
    Mark


  • 10.  RE: Lookup record before the current record

    Posted 06-19-2018 21:09
    The right side shows the reference which is the formula field I created. The issue is that the formula should have been. [call ID]-1 not [Record ID#]-1 since call ID is the key field in my table. Now I need to lookup the Call ID before this one but some call ID are skipped since this is imported from a SQL server. Were you successful in adding a loop in a formula field? If I can do that I would loop until I find a Call ID that exists.

    Thanks for your reply


  • 11.  RE: Lookup record before the current record

    Posted 06-19-2018 21:18
    if only a few are skipped, then you could have [Record ID#]-2 and another relationship.  Then on each record you have a formula checkbox field set to true. called [Parent Exists?] 

    Look that up to the child.

    Then you have a formula field that is [Record ID - x (final)] which uses the value in [Record ID#]-1 if the first relationship found a parent, else [Record ID#]-2 if it did not.  of course, you could continue this pattern up to even say 10 records back.

    Then use the (final) field to have the "real" relationships and do your lookup fields just once from that relationship.  So the other relationships are there just to figure out the correct parent to use in the  (final) relationship. 

    So no looping.  Quick Base does not loop in a traditional sense of my Fortran days

    Do I = 1,10
    .
    .
    .
    Continue


  • 12.  RE: Lookup record before the current record

    Posted 06-19-2018 21:25
    Let me study this idea. Thanks


  • 13.  RE: Lookup record before the current record

    Posted 06-19-2018 21:26
    You have the disadvantage of knowing SQL :)


  • 14.  RE: Lookup record before the current record

    Posted 06-19-2018 21:29
    Yup... :)

    How about looking up the maximum Call ID but only until the current record. That will always show the call ID before the current one but only if it exists. Any way to lookup maximum only if its less than the current call id?

    Thanks again_


  • 15.  RE: Lookup record before the current record

    Posted 06-19-2018 21:33
    >Quick Base does not loop in a traditional sense of my Fortran days

    Well you are in luck Mark as being an engineer and knowing FORTRAN a new feature called WASM will allow you to use FORTRAN with QuickBase once WASM is widely supported. WASM will eventually allow almost any language to inter-operate with the Web.

    Until WASM lands use JavaScript.

    Awesome WebAssembly Languages Awesome
    WebAssembly, or wasm for short, is a low-level bytecode format that runs in the browser just like JavaScript. It is designed to be faster to parse than JavaScript, as well as faster to execute which makes it a suitable compilation target for new and existing languages. This repo contains a list of languages that currently compile to or have their VMs in WebAssembly(wasm)
    https://github.com/appcypher/awesome-wasm-langs">https://github.com/appcypher/awesome-wasm-langs">https://github.com/appcypher/awesome-wasm-langs


  • 16.  RE: Lookup record before the current record

    Posted 06-19-2018 21:34
    I can't picture how that could work.maybe there is a solution if there was a table of all the record ID's, but I have not thought this though.


  • 17.  RE: Lookup record before the current record

    Posted 06-19-2018 21:43
    I have a table that has all record ID's.