Discussions

Expand all | Collapse all

Show Field from Separate Table based on Corresponding Field

  • 1.  Show Field from Separate Table based on Corresponding Field

    Posted 07-31-2017 20:23
    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.


  • 2.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 07-31-2017 21:35
    Is the Job# field unique in Table 1  ie can there be multiple table 1 records for a single job?


  • 3.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-01-2017 13:14
    Yes, the Job# is unique. Only 1 entry per Job # in a table, but the same Job # appears once in each table.


  • 4.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-01-2017 13:30
    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. 


  • 5.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-02-2017 20:40
    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.


  • 6.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-02-2017 20:46
    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 #.


  • 7.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-03-2017 21:02
    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?


  • 8.  RE: Show Field from Separate Table based on Corresponding Field

    Posted 08-03-2017 21:53
    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