How can you auto populate a field based on a single field and related tables

  • 0
  • 1
  • Question
  • Updated 3 years ago
  • Answered
I have an installation table that pulls a Division code.

I have another table that has Positions, Divisions, Names, Email, and Phone Number.

On my installation table I have six fields for one positions (1-6) and another six fields (1-6). The first set of fields is Real Estate Directors and the other six are Construction Managers.

My goal is to auto populate these fields based on the Division. The issue is that all divisions have the same number of positions. My Division Contact table has everyone listed by postition and division.

I am at a loss on how to do this without using a drop down. I am thinking formulas hard coded for the Names and then do a lookup for each of the Email and Phone fields.

Has anyone done anything like this
Photo of Joey Zint

Joey Zint

  • 1,870 Points 1k badge 2x thumb

Posted 3 years ago

  • 0
  • 1
Could you elaborate your question with an example?  I'd like to help, but I'm not understanding the question.
Photo of Joey Zint

Joey Zint

  • 1,870 Points 1k badge 2x thumb
I have gone a different direct but am still stuck on how to get the email fields into my notification. I built an embedded report to put the contact information of the people related to the Division of the project.

This report is setting in the Project page with the email field as a column in the report. How can I extract these into a single email field. I am using SoftTech Smart Email Reminder so that I can sent email attachments to customers outside of QuickBase. I have the ability to select a field as an email address and add this to a CC field. I know that you can join fields (example [FIELD]&","&[FIELD]) but not sure how to get the email address into a single field from an embedded report.
I dint have time right now to post a detailed answer, but I have posted the generic solution many times before.

You will need to coax those email addresses up to the parent record using a series of reverse relationships. I will try to find time later today tomloacte those previous posts.
To make a reverse relations here are the steps

Summary field on the Minimum Record ID if the children. Call it Minimum Record ID1

Then a new reverse relationship where 1 child has many parents. Use that Minimum record ID 1 field as the reference field on the right side of the relationship.

Lookup the contact emails the parent. That's 1 done.

Go back to the original relationship

Lookup that Minimum Record ID 1 down to the child.

Duplicate the Minimum Record ID 1  field except make it subject to the condition that the Record ID is greater that the A Minimum Record iD 1. Si this will be the second most Minimum.

 Duplicating that summary field weld have also conveniently duplicated in reverse relationship.
 Once again look up the email address to the parent.

Continue repeating these reverse relationships until you have enough reverse relationships to pick up as many children as you practically need. In your case it sounds like you need six reverse relationships.