Lookup record before the current record
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 fie...
Forum Discussion
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
- 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)
- 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.
- What is the reference field on the right side of the relationship?
- The field that calculates my record ID - 1
The formula looks like this: [Record ID#]-1 - It should work. I have done this before. I'm not sure what to suggest.
- The problem was that the record ID was not the key field for this table.....
- I asked what the field was on the right side of the relationship, but not on the left!
Mark - 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 - 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 - Let me study this idea. Thanks
