Discussions

Expand all | Collapse all

Getting the most recent field data, using a summary field, of a non-date field.

  • 1.  Getting the most recent field data, using a summary field, of a non-date field.

    Posted 02-09-2018 22:08
    Greetings Quickbase Community!

    The title of this post may sound a bit confusing so I will provide my goal followed by an example:

    Goal: I have two tables related to each other, Submissions and Offers, where Submission records are the parents and Offer records are the children(1 Submission has many Offers.)  What I need to do, is retrieve the integer field(or value) of the "Offer" that is most recent of all the "Offers" related to that Submissions.

    Example:    Offers            Offer Amount               Offer Date
     
                        Offer 1            10,000                          1/12/17
                        Offer 2            30,000                          1/18/17
                        Offer 3            20,000                          2/28/17

    In the above table, my goal is to have the Submission record retrieve the "20,000" from its related offers.

    Any help would be much appreciated!

    Cheers,


  • 2.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 02-10-2018 03:21
    No problem
    Make a Summary field of the Maximum Offer Date, and call it [date of latest offer]

    Lookup that field down to offers

    Make a summary if the maximum offer amount subjust to the filter where Offer Date equals [date of latest offer]


  • 3.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 04-20-2018 16:29
    Hi!

    Thanks again for your help 2 months ago.

    Do you think this would be possible for a non numeric field(i.e. a text field)?

    Thanks in advance.

    Nate 


  • 4.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 04-20-2018 16:39
    Yes, you will need to do a reverse relationship.  

    First make a summary of the minimum record ID# subject to the filter that the date = maximum offer date.
    call it [Record ID# of Most Recent Offer]


    Then make a new relationship with is backwards - ie reverse.

    One Offer has many Submissions, but when you go to the right side use the field [Record ID# of Most Recent Offer].  Then look up anything that you want form the Offer to the Submission.


  • 5.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 02-14-2018 20:13
    Thanks for the reply!

    I've tried this as well but unfortunately you can only use filters on fields in Offer records.  IE:


      -Only summarize records where the following is true in Offer:

     
    -all of these conditions are true:
          - [Some Date Field In Offers]  'is equal to'  'the date in the field'  [Some Other Date Field In Offers]


    In other words, I cannot use fields in the Submissions table fields as a reference when creating filters.


  • 6.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 02-14-2018 21:16
    Please do this step from above

    Lookup that field down to offers 

    Then the field you need to compare to will  be in Offers


  • 7.  RE: Getting the most recent field data, using a summary field, of a non-date field.

    Posted 02-14-2018 22:27
    Genius!  Worked like a charm.  Thanks!