How to show the last "note" record in a report

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered

I have two tables, a project record table and a notes table.  The notes table has a table to table relationship to the project record table.  What I'm attempting to accomplish is to have a field in the project record table which captures the last note in the note table associated with that project.  That way when I run reports I don't have to rely on only showing a link to the notes.  I can actually show the last note in the report.  I hope this makes sense what I'm trying to accomplish.  I'd love any thoughts/recommendations from the community.

Photo of Michael Sargent

Michael Sargent

  • 150 Points 100 badge 2x thumb

Posted 1 year ago

  • 0
  • 1
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
I think you may need to create a summary field of the Maximum Record ID of the notes table.
Photo of Michael Sargent

Michael Sargent

  • 150 Points 100 badge 2x thumb
Forgive the newbie ignorance here, but how do I properly code for a summary field of the Max([Record ID#])?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,198 Points 50k badge 2x thumb
No problem. Go to the relationship where 1 Project has many Notes and there will be a button on the left side of the Relationship to Add Summary field.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
right, and then make a reverse relationship where 1 Note has many Projects and use that Maximum Record ID# field on the right side of the relationship and lookup the note field up to the Project.
Photo of Kingsly SD Samuel

Kingsly SD Samuel

  • 776 Points 500 badge 2x thumb
I think you can also use the option of showing "embedded report" instead of a "link" in your project record table. To do that, you need to go to the form in Project Record Table and customize the form in the "Notes" section to display a report ("Display the related development documents directly on the form") instead of ("Display just a link to the related development documents"). That way, you can see all the notes related to that record. Hope that helps.
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
This link may help. 
http://help.quickbase.com/user-assistance/many_to_many_relationship.html

Personally I like to just use the IOL technique and then use API_DoQuery otherwise I would have a ton of many-to-many relationships by now. Good luck! Can also implement it within an hour and screenshare the process if you want as well.
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
I think that for a new user a simple reverse relationship is all they need.  I don't see the need for a many to many relationship for this use case.
Photo of Chuck Grigsby

Chuck Grigsby

  • 1,978 Points 1k badge 2x thumb
Oh sorry I don't really use them, thought they were the same thing.
Photo of Matthew Neil

Matthew Neil

  • 31,438 Points 20k badge 2x thumb
This is a great use case for a webhook.  It keeps the app performance up without the reverse relationships 
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,166 Points 50k badge 2x thumb
Good point. I know that you folks are Cirrus ally try to avoid reverse relationships for performance reasons.

Any even better suggestion is to use an Action as that is less technical to setup. The trick though is to make the Report Link field which defines the record to be edited. It will need to be built on he child Notes table and edit the Parent.

So the report link field on the child will need to have the field [Related Parent] on the left and [Record ID#] of the Parent Table on the right.