Discussions

Expand all | Collapse all

Within the same table, can I have a formula access a field of a different record, given the key?

  • 1.  Within the same table, can I have a formula access a field of a different record, given the key?

    Posted 13 days ago
    Edited by Adam Rain 13 days ago
    I am new to quickbase and have been struggling to figure out how to make this work. Ultimately I am trying to fill in the "Stop Date" of one record with the "Start date" of a new record. I've managed to grab a snapshot of the appropriate record ID (I could just take a snapshot of the appropriate start date, but I want to reflect any possible changes. The record id will never change though).
    Is it possible to have a formula access the field of another record in the same table?
    example: [StopDate] = [Record ID]->[Start Date]

    desired data:
    Record ID       Snap ID       Start       Stop     Related Person
                   1                              10-5                                      John
                   2                     1       9-28       10-5                      John
                   3                            10-19                                       Sara
                   4                     3          8-3    10-19​                        Sara
                   5                     4        3-23        8-3                        Sara

    ------------------------------
    Adam Rain
    ------------------------------


  • 2.  RE: Within the same table, can I have a formula access a field of a different record, given the key?

    Posted 12 days ago
    Quick Base will allow you to make a Relationship between a table and itself.  So if you have a way to know what the "previous" record is, then you can flow lookup information down.

    Note also, that Quick Base does support the concept of Predecessors, sort of what I would call Microsoft Project Lite.  But it works.   ie if the Project kick off meeting is delayed, then it will flow that down to a delay in task 25 way down at project completion. In my experience it is only practical to use where you have standard templated task lists which are interdependent and you spin up a new project and push a button to Auto create the tasks from a template.

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



  • 3.  RE: Within the same table, can I have a formula access a field of a different record, given the key?

    Posted 12 days ago
    Adam, the other option to get this quickly is the create a separate reference table to use automation to generate the values in the table. See below.

    Master table-A : 
    Record ID# - Unique and primary
    snap id - numeric
    Related Person

    Reference Table-B
    Record ID# unique and primary
    Snap id - numeric

    Create automation on table-A. On new record, insert into table-B

    Create relation between Table-A (Record id) and table-B(Snap id)
    Create summary field in under this relationship to create max(record)
    now create reverse relationship : Table-B (Record id) and table-A (Max_record_id)
    Create snap id as lookup under this relationship
    On the form use this lookup value to update the snap id value on form save.

    The lookup value will always be last record id from table-A which will be saved against new record id generated.



    ------------------------------
    Babi Panjikar
    ------------------------------