Newbie looking for help with a formula

  • 0
  • 1
  • Question
  • Updated 10 months ago
  • Answered

Relationship between Companies and Contacts Tables...

On the table “Contacts” there is a field called “Title” 

If one of the contacts “Title” field contains “Chief Compliance Officer” I want that contacts field “Contact Full Name” to be put in the field “Chief Compliance Officer” in the “Companies” Table.

Would really appreciate any help. 

Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb

Posted 10 months ago

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

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
You can do this with a reverse relationship.

On the current relationship make a summary field of the Minimum of the field [Record ID#] subject to the filter that the title equals Chief Compliance Officer.   Call that field [Record ID# of Compliance Officer Contact]

Then do a new reverse relationship where 1 Contact has many Companies, but during the setup do not let QuickBase create a new field for you for the reference field on the right side of the relationship.  Instead use the field [Record ID# of Compliance Officer Contact].

Then just look up any info such as the name field from the Contact record to the Company record.
Photo of Kevin O'Boyle

Kevin O'Boyle

  • 602 Points 500 badge 2x thumb
The above method is correct - but I’d ask why do you really want your table structured that way. You are duplicating info - and CRMs often aren’t set up that way.

If you just want to display the CCO on the companies form, you can embed a report in the companies table main form - and set up the report filters to only show the CCO (or whatever key employees you want to display on the company record form)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Kevin is correct, unless you need to show the Chief Compliance Officer on a Company report, then the embedded report will not work.
Photo of Mark Comish

Mark Comish

  • 620 Points 500 badge 2x thumb
I am such a newbie and the way you laid things out made it easy for me to create it and do it exactly how I needed it.  I appreciate all the comments and the fast response!
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 51,436 Points 50k badge 2x thumb
Great, thx for letting me know it worked for you.