Filtered Reference Field Question

Question

Hello everyone!
I'm hoping that someone can offer me some guidance on how to accomplish my objective.&nbsp; 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.&nbsp; The Model is set to Latitude 1234 in each case.&nbsp; The Hardware # is unique (100, 200, and 300).&nbsp; 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.&nbsp; 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.&nbsp; How can I prevent that from happening?

Thank you!
Please see my PDF attachments for screenshots.------------------------------Stu Lamden------------------------------

markshnier__you · Answer

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. &nbsp;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 · Answer

Mark, &nbsp; I really appreciate your help.&nbsp; Thank you for your time. &nbsp; I have followed everything exactly, but for the last step I'm a little confused. &nbsp; 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. &nbsp;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.&nbsp; 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. &nbsp;

markshnier__you · Answer

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
------------------------------

stulamden1 · Answer

Mark, &nbsp; Thank you for your help!&nbsp; This accomplished exactly what I needed. &nbsp; Stu

Forum Discussion

Filtered Reference Field Question