Skip to main content
Question

compare dates to find latest and lookup another field in that record

  • June 20, 2014
  • 0 replies
  • 38 views

I am having a hard time figuring this one out. Our CRM app has a table called Opportunities, with a relationship to a sub table called Last Actions. Each opportunity has many last actions. In the pending opportunities report (Which shows Pending opportunities as a table with each opportunity record on a line) , i want to have a column that shows the Latest Last action for each opportunity. Currently the last actions column shows a link to the last actions table for that opportunity. So i want the column to lookup the most recent last action and display the LastAction text field result.

Fields in the Last Actions table are: Date Created, Date Modified, Last Modified By, LastAction, Related Opportunity, Opportunity - Title, Opportunity - Type, Record ID#, Record Owner.

So i need to lookup the Date Modified Field in the Last Actions table for the appropriate Related Opportunity, compare it to today's date to find the latest, and display the text from the LastAction field.

I hope that this made sense. I understand what i want to do, but am having trouble figuring out how to make it happen.

This topic has been closed for replies.

Ah a classic reverse relationship quiz.

1. Summary field on relationships of the maximum of the [date modified] - call it [date/time of last action]


2. Lookup [date/time of last action] down to Last Actions.

3. Summary field of max [record id#] where [date modified] = [date/time of last action]. Call it [Record ID# of last Action]

4. New "reverse relationship" where One Last action has many Opportunities. and for the reference field on the right side use the field [Record ID# of last Action].

5. "Lookup" the last action text from the backwards parent last action record down to the Opportunities record.







  • ArchiveUser
  • Author
  • Quickbase Alumni
  • June 23, 2014
Wow, i would not have been able to figure that one out myself.  No wonder my brain was twisted in knots.  Thank you very much.  I am putting this one in my kit bag for future use.
yep, its what you gotta to to "float up" a text field from a child back up to a parent.  Very useful, as long as you have a way to know which record is the one that has the data you need to "float up".
Terms & ConditionsAccessibility statement