How do you create a list box of filtered values from another table?

  • 0
  • 1
  • Question
  • Updated 7 years ago
  • Answered
How do you create a list box of filtered values from another table? For instance I have an employee table that has a manager checkbox. I want to create a list in another app that allows the user to select users from the user table only if that box is checked. I cant key the name field becuase there is is a first name, mid, last, etc and it possible that two emplyees can have the same name (this is so now)

This is going to be an important part of our system we are developing here because there are many global tables that are used in many applications. Employees, Departments, Customers, Contacts, etc....
Photo of Randy

Randy

  • 0 Points

Posted 7 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
If you make a relationship between selecting the one manager that many employees have, the on the FORM pi\roperties for that drop down box field it will be defaulted to the "record picker". That essentially means its going to offer up all records and prompt the user with the default record picker fields for that Table's properties.

But the FORM will also let you specify any report for prompting the user - so just create a report (I suggest hide from all users) and call it perhaps "drop down list of managers only", with the appropriate filter and point your form at that report for that selection field.
Photo of Randy

Randy

  • 0 Points
I created the report, cant seem to point the list box to that report. Sorry, first day as a user
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
Edit the FORM properties for that selection field.  FORM, not field properties.
Photo of Randy

Randy

  • 0 Points
Again, first day, I will have to call tech support, maybe I have been working on this too long
Photo of Leah/Dave

Leah/Dave

  • 0 Points
Hi Mark, I'm having basically the same trouble.  I don't understand how to set up the "appropriate filter" you mention.  I am able to make a report and if I set <ask user> and run the report manually, I get the list I'm looking for.  Great!  But when I call that report from the form, the list is blank.  To what do I change the filter to get this working?
Photo of Leah/Dave

Leah/Dave

  • 0 Points
Actually, I might have just stumbled blindly onto the solution! On the field properties, I checked hte box for "The values in this field depend on a selection in another field" and then (happily) the fields I need to match on from the two tables magically appeared in the two match field lists. Woohoo!

This conversation is no longer open for comments or replies.