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

  • 0
  • 1
  • Question
  • Updated 7 months ago
  • Answered
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,
Photo of Nate St. Elme

Nate St. Elme

  • 202 Points 100 badge 2x thumb

Posted 9 months ago

  • 0
  • 1
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
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]
Photo of Nate St. Elme

Nate St. Elme

  • 202 Points 100 badge 2x thumb
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 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,456 Points 50k badge 2x thumb
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.
Photo of Nate St. Elme

Nate St. Elme

  • 202 Points 100 badge 2x thumb
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.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,036 Points 50k badge 2x thumb
Please do this step from above

Lookup that field down to offers 

Then the field you need to compare to will  be in Offers
Photo of Nate St. Elme

Nate St. Elme

  • 202 Points 100 badge 2x thumb
Genius!  Worked like a charm.  Thanks!