Show Field from Separate Table based on Corresponding Field

  • 0
  • 1
  • Question
  • Updated 1 year ago
  • Answered
I have seen a few conversations about this, but am having a hard time relating the information back to my own app.

I am looking for a way to pull a specific text field from a different table, based on corresponding text fields.

We use these apps in a production environment and have separate tables for each stage of the process. I want the current process table to show whether the job was indicated as completed at the previous process table.

Simply - I have 2 tables: Process 1 and Process 2. Both have a Job # field. Both have a Job Completed text field (Yes or No). If the Job # matches, Process 2 should automatically show a "Process 1 Job Completed" field.

Also - all data in both process tables is inputted by imports, so users are not manually typing in the Job # field for a lookup.
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb

Posted 1 year ago

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

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
Is the Job# field unique in Table 1  ie can there be multiple table 1 records for a single job?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
So a suggestion is to change the key field of table 1 to be the Job#.  Then make a relationship where 1 Table 1 has many Table 2 (even if there will only ever be 1 child).

Then you can look up any information from Table 1 down to Table 2, including its completion status. 
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb
Is there a formula I could use or a way for Quickbase to recognize automatically a Job# match?
I created the relationship, but I want to avoid a user-look up to pull in the Table #1 information. The Job# in Table 2 will be imported.
(Edited)
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
Sorry, I left out an important step.  You will need to change the Key field of your table 1 to the Job # field.  I would suggest doing that and then deleting the existing relationship and building the relationship again.

Then the records will link up automatically if the reference field on the right side of the relationship is the job #.
(Edited)
Photo of Brittany

Brittany

  • 210 Points 100 badge 2x thumb
This worked between two tables, but will this work if there are multiple relationships between the tables? I am going to be making a lot of relationships (see below). Each column is a stage in our production process and each Machine Box is a separate table in our app. Different machines feed the next machine.
Ideally, Machine 5 will have fields saying "Machine 1 - Job Complete", "Machine 2 - Job Complete", "Machine 3 - Job Complete" and "Machine 4 - Job Complete".

Do you think this kind of set up is possible?
Photo of QuickBaseCoach App Dev./Training

QuickBaseCoach App Dev./Training, Champion

  • 50,850 Points 50k badge 2x thumb
Yes I think its possible.

Machine 5 has many Machine 4As.

Machine 4A has many Machine 3s.

Machine 4B has many machine 2As
Machine 4B has many machine 2Bs
Machine 4B has many machine 2Cs

Machine 3 has many machine 2As
Machine 3 has many machine 2Bs
Machine 3 has many machine 2Cs

etc