Discussions

Expand all | Collapse all

Conditional Lookup

  • 1.  Conditional Lookup

    Posted 08-14-2021 11:03
    Hi All, 

    I am working with 3 tables and I have a form with 3 drop downs to connect the record to all 3 tables. 
    I am trying to find a way to get 2 of the 3 fields to auto populate. 

    The field in the Form that is filled out is the child to the table where the other 2 fields are pulled from. I'm hoping to get these 2 fields to auto populate. 

    The Image below shows the relationships and the fields they share as well as how the form is set up. 
    Any help is greatly appreciated :)



    ------------------------------
    Amber Anderson
    ------------------------------


  • 2.  RE: Conditional Lookup

    Posted 08-16-2021 09:49
    Amber,
    What you are describing is a typical and proper setup to assign Employees to Projects.

    The Employee ID would be a lookup field from the Employees table along with any other lookup fields you like.
    The drop down list of employees can be filtered to only show active Employees, but I'm not understanding your "ask" to have it be filtered for "Active Assignments".

    I'm not understanding how the drop down list for Projects could auto populate based on the employee selection since the setup you have looks to be the place where employees are assigned to projects.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Conditional Lookup

    Posted 08-16-2021 10:05
    Hi Mark, 

    I can get the drop downs to reflect what I need ( Active assignments and such) 
    What I am stuck with is when I select the drop down for the appropriate active assignment, I want to have the fields Employee ID (pulled from the Employee Table)  and Project to auto populate. 

    Essentially I need the record I am creating to also link to those tables  and not just the Assignment table. 

    I hope that makes more sense :)



    ------------------------------
    Amber Anderson
    ------------------------------



  • 4.  RE: Conditional Lookup

    Posted 08-16-2021 11:19
    The way to do that is to add more lookup fields to the relationships, to lookup those values down to the Assignment record.

    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Conditional Lookup

    Posted 08-16-2021 12:13
    Hey Mark, 

    I'm not sure I am following. 

    Currently the Table I have built (Development Table) has a relationship to the Assignment Table, Project Table and Employee Table which is creating 3 drop down fields in order to connect the Development records to the Project Table, Employee Table and the Assignment table. 

    the first drop down that is filled out is the Assignment Drop Down where the employee name and assignment are selected from the single field. 

    Once that field is filled out - I am trying to get the field associated with the Project and Employee to auto populate with the project and employee  related to the assignment selected. 

    I can pull that data from the assignment drop down with added lookups from that table, but it doesn't link it to the Project/Employee Tables. Unless I am missing something. 


    Thank you :)





    ------------------------------
    Amber Anderson
    ------------------------------



  • 6.  RE: Conditional Lookup

    Posted 08-16-2021 12:45
    Ah, so we have a 4th table.  I was not aware of that.

    One way to get the fields on the development record to populate is to use a form rule that says when the 
    [Related Project] is not equal to the value in the field [lookup of Related Project from Assignments]
    Action
    Change [Related Project] to the value in the field [lookup of Related Project from Assignments]. That that rule to fire all the time (uncheck the checkbox at the bottom of the form rule)

    You would have a similar form rule to populate Related employee.




    ------------------------------
    Mark Shnier (YQC)
    Quick Base Solution Provider
    Your Quick Base Coach
    http://QuickBaseCoach.com
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Conditional Lookup

    Posted 08-16-2021 15:07
    Edited by Michael Tamoush 08-16-2021 15:10
    Amber,

    When you say 'autopopulate', do you mean you want the Related Fields to literally select themselves automatically, or when you click to see the list of options to select, that list is limited based on other conditions (I'm thinking the latter, hence the title 'Conditional Lookup')??

    ------------------------------
    Mike Tamoush
    ------------------------------



  • 8.  RE: Conditional Lookup

    Posted 08-16-2021 15:18
    Hi Mike :)

    Auto populate is so they automatically fill vs having to select a limited drop down list. 
    I was able to get that to work using Marks suggestions about 10 min ago. 

    The reason for it - is to ensure that all the items that are related to that employees assignment gets logged to the Employee File. So an employee could have had 5 assignments - I want to be able to view all items associated with those assignments under the Employee file, vs having to go to each assignment to view them. 

    Having them linked to the project, then also lets me know what the project was incase it was a particularly difficult project, and they did poorly on that one project or excelled on another, we can take those things into consideration when reviewing the employee file as a whole. 

    The other thing I have set up is assigning a supervisor to each employee Assignment. This way it limits who has access to that employees details.  In addition to a Project Supervisor as well. 

    Having these split, allows access to review trends for the particular project across all employees that worked that project with out having to drill down into the assignments themselves. And it allows the Assignment supervisor to review their active employees and develop them easier. 

    I hope this helps :)

    For the pipeline trigger, that sounds like a feasible option as well. I will have to look more into that. The QB App that I am developing is all focused on employee development, metrics etc. so linking the various tables to the employee file through the assignments table is going to be a very common item. 


    ------------------------------
    Amber Anderson
    ------------------------------



  • 9.  RE: Conditional Lookup

    Posted 08-16-2021 15:50
    Gotcha. Glad Mark got you in the right direction! He taught me so much, if you have further questions I highly recommend him for his consulting services.

    If you think people will update that field in a grid edit, then you'll definitely want to look into Pipelines to search and auto select.

    ------------------------------
    Mike Tamoush
    ------------------------------