Forum Discussion

StuLamden1's avatar
StuLamden1
Qrew Member
9 months ago

Filtered Reference Field Question

Hello everyone!

I'm hoping that someone can offer me some guidance on how to accomplish my objective.  What I'm attempting to do is filter out records available in a Reference Field after they've been associated with records in a second table.

I have two tables.

1 – Computers Table

The Computers Table currently includes the text fields Model, Hardware # and In Use which can be set to either Yes or No.

There are three entries currently.  The Model is set to Latitude 1234 in each case.  The Hardware # is unique (100, 200, and 300).  In Use is set to No.

2 – Employees Table

There is only one field currently, the Text field Employee.

I have created a table-to-table relationship where Computers is the Parent Table and Employees is the child Table.

As a result of creating this relationship the Computers – Model field has been added to the Employees Table/Form, which would be correct.

My objective is once I associate Latitude 1234 with Hardware # 100 with an Employee, I don't want it to be listed as a choice in the Related Computer dropdown.  Is that possible?

As an example, John Smith is associated with Hardware 100.

And when I add another employee, Latitude 1234 with Hardware #100 is available to be selected.  How can I prevent that from happening?

Thank you!

Please see my PDF attachments for screenshots.



------------------------------
Stu Lamden
------------------------------

4 Replies

Sort By
  • MarkShnier__You's avatar
    MarkShnier__You
    Icon for Qrew Legend rankQrew Legend
    9 months ago

    No Problem

    just make a summary field on that relationship and use the second radio button to make a Checkbox Field if there are any employees associated with that computer.  You might wanna call that field. [Computer is Assigned?]


    Then make a report on the computers table where the computer is not assigned.

    then make a report on the computers table where the computer is not assigned.

    Then edit the form for that drop-down field where you are selecting a computer and rather than using the standard record picture codes use that report that you just created which is filtered for computers that are not assigned.



    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------
    • StuLamden1's avatar
      StuLamden1
      Qrew Member
      9 months ago

      Mark,

       

      I really appreciate your help.  Thank you for your time.

       

      I have followed everything exactly, but for the last step I'm a little confused.

       

      just make a summary field on that relationship and use the second radio button to make a Checkbox Field if there are any employees associated with that computer.  You might wanna call that field. [Computer is Assigned?] – Done!

      Then make a report on the computers table where the computer is not assigned. – Done!

      then make a report on the computers table where the computer is not assigned. – Done!

      Then edit the form for that drop-down field where you are selecting a computer and rather than using the standard record picture codes use that report that you just created which is filtered for computers that are not assigned. – This is where I'm a bit confused.  Two dropdowns are automatically created and added to the Employees form.

      1 – Related Computer and 2 – Computer – Model.

      Neither gives me the option to choose the report 'Computer IS NOT assigned' that I created, which filters as Computer is Assigned is not equal to checked.

       



      • MarkShnier__You's avatar
        MarkShnier__You
        Icon for Qrew Legend rankQrew Legend
        9 months ago

        If you edit the form properties for the drop down field to select related computer, there will be an option in he choices below "use record picker" to select a report to use. 



        ------------------------------
        Mark Shnier (Your Quickbase Coach)
        mark.shnier@gmail.com
        ------------------------------