Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
11 years ago

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

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.

Formulas and functions
Relationships
reports and charts
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    11 years ago
    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.







    • ChristopheVerho's avatar
      ChristopheVerho
      Qrew Trainee
      7 years ago
      This seems like the solution i am looking for, but it's still a bit too confusing to put into practice. Can you try to explain it a bit more detailed for someone who is not very familiar with the terms and with relationships.
    • QuickBaseCoachD's avatar
      QuickBaseCoachD
      Qrew Captain
      7 years ago
      Chrisophe,
      If you would like one on one assistance you can contact me off line via the information on my website.
      QuickBaseCoach.com

      Or else I suggest that you just start working though the steps I explained and let me know where you get stuck.

      I'm also  not sure if exactly what you need is exactly what I answered in this question here as you have not described your situation.
  • ArchiveUser's avatar
    ArchiveUser
    Qrew Captain
    11 years ago
    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.
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    11 years ago
    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".