How to have one reference field selection auto-select one from another table?

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have an application that pulls info, via relationships, from 2 tables in another app.  When the user selects a customer name in that app, they are required to select a customer name from the dropdown for the account table in the second app.  then I'd like to automatically use the selection in that dropdown to pull further customer data about that account from the second table.  Versus making the user select the customer name a second time.  Is there a way to do this?  Thanks!  (I've tried default, but don't seem to be able to use a field name in that.) 
Photo of Carole Vandenberg

Carole Vandenberg

  • 90 Points 75 badge 2x thumb

Posted 2 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,478 Points 20k badge 2x thumb
Are the other "Two" apps connected in any way?

Are you using any key fields other than the default [Record ID#]?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
I suggest that you make the 2nd field for related customer be a formula equal to the first Related customer field.

A safe way to do this, assuming that you are dealing with a Production app and do not want to accidentally break it, would be to create the new field called something like [Related Customer mirror] with a formula of [related customer].  Then pull in a few lookup fields to confirm it it working ok.

Once you are satisfied that it will work OK, then you can go to the field for [Related Customer 2] (or whatever it is called) and change the field type to be a formula field and have the formal  be [Related customer].
Photo of Carole Vandenberg

Carole Vandenberg

  • 90 Points 75 badge 2x thumb
Sadly, I cannot find where I did this before successfully but cannot get it to work again.  Questions:  is it the reference proxy I need to change to a formula field that matches the contents or the actual numeric reference field?  If I use numeric reference, it doesn't pull any data - but i'm thinking those internal numbers don't match and that's why.  But if I change the text reference proxy to a formula, it pulls it out of the relationship.  Works but doesn't allow for any lookups.  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
The field to change to a formula field is the field, typically numeric, which is typically called [Related Parent]. Not the Proxy field.

But the field type will depend in the f ield type of the Key field of the table which is the Parent . Perhaps if you explain with your real table names and what the Key field of the Parent table is, I can help further.
Photo of Carole Vandenberg

Carole Vandenberg

  • 90 Points 75 badge 2x thumb
Thanks, Mark!  I have many, many ways I want to use this, so appreciate!  I will pick one - one app, two tables - one for Projects and one for Assessments.  So I want to display some basic details from the Assessment for a given project, even if the assessment was created separately from the project (so they aren't tied internally).  I have a relationship where Assessments are the master table, with a key field of Record ID  and the Projects are the child table, with Related Assessment as the numeric reference.  If I turn that into a formula numeric and point to the Record ID (which has also been pulled into same relationship from the project to the assessment), it doesn't display anything in that field.  

Now also what I'd really love to do is just have it pull the record in the Assessment table where the name of the customer field == name of customer field in the Project table.  So, when a user creates a project, it has a customer name.  It will duplicate that customer name in the refernece fields for other tables automatically where i need to pull data from - that's actually what I'd really most like to do.  

Sorry if this is getting super convoluted, but I could use this so many times just if I could figure it out.  And now I wonder if when I said it worked before if I thought it did but was wrong - cannot find that example anywhere now.  (I have a LOT of apps we use.)  
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 53,570 Points 50k badge 2x thumb
Here is one suggestion. But it assumes that a given customer only has one project..

You have a relationship where 1 Customer (in the other app) has Many Projects.

Make a summary field of the Maximum of the Record ID of the Projects.

Then look that up down to assessments.

Then use that field as the formula value for Related Project.