Filtering contents of dropdown menu using values from another field

[Joe_Schmo]

I have a field on a layout that I have configured to display the Last names of personnel in my "Personnel" table. I'd like to find a way to make the dropdown menu only display personnel in a certain department. The Personnel table has a Department field already. I'd like to have it actually filter it before it's displayed, rather than validate the entry and give an error if the personnel is in the wrong department.

Tagouts Copy 1.fp7.zip

[doughemi]

Make a new Table Occurrence of your Personnel table, and name it "Department Personnel". Create a new global field named g.department. Make a relationship Personnel::g.department = Department Personnel::department. Create a value list consisting of only related values from Department Personnel::LastName. The value list will show the personnel who are in the department named in g.department.

[Joe_Schmo]

I get what you are saying, and it seems like it would work but I'm going to need this for about 10 departments and maybe more later. Isn't there some way to filter the contents of the dropdown, either from the Table>Field settings for that field in the Manage Database window, or the Field Control>Setup for that field on the layout?

Maybe use a script to perform a find of personnel in that department and make the find results the dropdown would work. I don't know exactly how to do that, but it seems like a more simple solution if possible.

[Vaughan]

I get what you are saying, and it seems like it would work but I'm going to need this for about 10 departments and maybe more later. Isn't there some way to filter the contents of the dropdown, either from the Table>Field settings for that field in the Manage Database window, or the Field Control>Setup for that field on the layout?

doughemi has hit the nail on the head. Have you tried it yet?

[Joe_Schmo]

Not yet, working on another issue with the database right now. It's a separate thread and I think I've about got it so I'll try Doughemi's suggestion in a minute.

[Joe_Schmo]

Make a new Table Occurrence of your Personnel table, and name it "Department Personnel". Create a new global field named g.department. Make a relationship Personnel::g.department = Department Personnel::department. Create a value list consisting of only related values from Department Personnel::LastName. The value list will show the personnel who are in the department named in g.department.

I made the new TO and global field then added the relationship like you suggested. I'm not sure where to go from here. I created the value list but I don't see how to set up each dropdown to filter the non-aplicable departments.

The first one will be a member of Department A so I would want that dropdown to only show personnel from there. The second dropdown should only show members of Department B and so on. Does this mean that in place of "g.department" I need to make a global field for each department, adding 10 global fields to the personnel table, 10 value lists and 10 TOs and relationships?

[doughemi]

Do you need to show all departments simultaneously, or do you want a "show me department A" filtered type arrangement?

[Joe_Schmo]

Filtered- but a different filter for different fields. One dropdown would be to choose someone from department A. The next dropdown on the layout would be a separate field that also allows the user to select a name from the personnel table, but this time it should filter to show only department B.

[doughemi]

Perhaps the attached sample will help.

personnel.fp7.zip

[Joe_Schmo]

OK, it looks like whatever you did there will work. I'm trying to reverse engineer it and figure out how to apply it to my schema now. Thanks :)

[Joe_Schmo]

I don't see how you linked the departments to g.departments and name to g.FoundName. Is that a nomenclature you just chose to use to stand for global or does naming it with a "g." in the front tell FMP to reference the field named after the "g."? I think this will work, I just don't understand how it's working or how you built it.

[Joe_Schmo]

Perhaps the attached sample will help.

In your example you choose a department from the first dropdown and that triggers the filtering of the personnel showing up in the second dropdown. That won't work with the layout that I have unless I redesign it. I only have dropdowns for the personnel and want it to be filtered for the user already. The user won't need to choose what department the dropdown should show. I want the dropdown to already be filtered to only show the personnel in the applicable department for that dropdown.

[doughemi]

g.<Whatever> is just a naming convention I use to denote global fields. When you want to define a global field, click Options, and then the Storage tab and select Use global storage.

g.Departments is not linked to departments. Right-click the field (in layout mode) and choose Field/Control->Setup. You will see that it is defined as a pop-up list using the value list Departments. Choose File->Manage->Value Lists... and you will see that the departments value list uses all records from the field department. Value lists automatically eliminate duplicates, so you will see only the unique values of the field department in all records.

I want the dropdown to already be filtered to only show the personnel in the applicable department for that dropdown.

How do you know the applicable department? Please list the steps in the workflow.

[Joe_Schmo]

g.<Whatever> is just a naming convention I use to denote global fields. When you want to define a global field, click Options, and then the Storage tab and select Use global storage.

g.Departments is not linked to departments. Right-click the field (in layout mode) and choose Field/Control->Setup. You will see that it is defined as a pop-up list using the value list Departments. Choose File->Manage->Value Lists... and you will see that the departments value list uses all records from the field department. Value lists automatically eliminate duplicates, so you will see only the unique values of the field department in all records.

How do you know the applicable department? Please list the steps in the workflow.

The user won't have to know. It's a property of that field on the layout.

There are several dropdowns that are already set up to display ALL personnel records. However, certain dropdowns are for supervisors or members of specific departments. It will still work if they aren't filtered, I'd just like to clean up the results of the dropdown and get rid of names that don't apply. I don't want the user to have to decide what department he wants to filter. I'm trying to make that a fixed property of each dropdown.

[doughemi]

It still isn't clear how that layout is tied to a department. Apparently the portal showing the different equipment found out, so it has to have been determined somewhere.

[Joe_Schmo]

It still isn't clear how that layout is tied to a department. Apparently the portal showing the different equipment found out, so it has to have been determined somewhere.

Sorry to make this so confusing. I thought the pics would help. If you look at the field labels on the image in post #14

[Field Label] contents

[Operations / Facilities] both are department names, and thus are values listed in the department field of the personnel table for applicable personnel. This dropdown should show only personnel who's department is "Operations" or "Facilities"

[Hybrid Operator] should only show personnel from department "Hybrid Ops"

The Primary Authorized employee can be anyone so that field is not filtered. The Issuing Authority could be from any department but must be qualified to sign for it so I have a field in the personnel table that I can use to filter it using whichever method I can gain from this thread. The green boxes next to the white fields are time stamps that autofill, not a part of choosing personnel or department.

The portal is unrelated to personnel or department tables.

Again, sorry about the confusion. Thanks for your patience and assistance. Does that make sense now?

[doughemi]

For Operations/Facilities:

Create a global field g.OpsFacil . Enter Operations<return>Facilities in that field (<return> means the return key)

Create a new Table Occurrence of your personnel table. Call it OpsFacil.

Create a relationship personnel::g.OpsFacil = OpsFacil::Department

Create a value list named OpsFacilVL . Set it to display only related records from OpsFacil::Name

Set the Operations/Facilities field as a dropdown list using values from OpsFacilVL

For Hybrid Operator:

Create a global field g.HybOps . Enter Hybrid Ops in that field

Create a new Table Occurrence of your personnel table. Call it HybridOps.

Create a relationship personnel::g.HybOps = HybridOps::Department

Create a value list named HybOpsVL . Set it to display only related records from HybridOps::Name

Set the Hybrid Operator field as a dropdown list using values from HybOPsVL

[Joe_Schmo]

WHEW! This is going to take more work than I thought! It's about quiting time for me but I might give it a shot when I get home. I'll let you know tomorrow how it works out.

[bcooney]

Automatic message

This topic has been moved from "Database Schema & Business Logic → Calculation Engine (Define Fields)" to "Database Schema & Business Logic → Value Lists".

btw, this technique is referred to as a "conditional value list."

[Joe_Schmo]

For Operations/Facilities:

Create a global field g.OpsFacil . Enter Operations<return>Facilities in that field (<return> means the return key)

Create a new Table Occurrence of your personnel table. Call it OpsFacil.

Create a relationship personnel::g.OpsFacil = OpsFacil::Department

Create a value list named OpsFacilVL . Set it to display only related records from OpsFacil::Name

Set the Operations/Facilities field as a dropdown list using values from OpsFacilVL

For Hybrid Operator:

Create a global field g.HybOps . Enter Hybrid Ops in that field

Create a new Table Occurrence of your personnel table. Call it HybridOps.

Create a relationship personnel::g.HybOps = HybridOps::Department

Create a value list named HybOpsVL . Set it to display only related records from HybridOps::Name

Set the Hybrid Operator field as a dropdown list using values from HybOPsVL

I'm working on setting up the first conditional value list for OpsFac. I think I did everything you said in the first set of instructions except "Create a global field g.OpsFacil . Enter Operations<return>Facilities in that field". Where do I enter the values "Operations" and "Facilities"? I created the global field. Do those values go in the properties of the global field or in the personnel table for each record or what?

Please explain exactly what I would click or navigate to after creating the field named g.OpsFacil and setting it as a global field.

Thanks again!

[doughemi]

View->Layout mode

Layouts->New Layout/Report

Show Records From Personnel

Layout Name Workbench

Layout type Blank Layout

Click Finish

Insert->Field->g.OpsFacil

View->Browse mode

Select g.OpsFacil field

Type Operations<return>Facilities

[Joe_Schmo]

Ok, that makes more sense. I followed it exactly until the last step. Hitting return after typing "Operations" doesn't commit the record, it moves the cursor down a line making the field two rows tall. I tried it as you suggested, as well as only entering Operations, committing the record and then repeating it with Facilities.

Either way, it doesn't matter because when I go to the Safety Clearance layout in browse mode and test filling out the form:

1- Initially, the form contains the value stored in the g.OpsFacil field

2- clicking the dropdown DOES give me the appropriate list of names!

3- when I select a name, it changes the value of the g.OpsFacil field

4- the drop down list no longer works and the name selected now appears on all new records because that person's name is the new g.OpsFacil global value

so close!

I wish there was some way to save a find and have the results be the options in the drop down. That would be so much easier!

[doughemi]

Ok, that makes more sense. I followed it exactly until the last step. Hitting return after typing "Operations" doesn't commit the record, it moves the cursor down a line making the field two rows tall. I tried it as you suggested, as well as only entering Operations, committing the record and then repeating it with Facilities.

No, you WANT the g.OpsFacil field to contain literally Operations<newline>Facilities. That forces the relationship to look for both values in your Department field.

Either way, it doesn't matter because when I go to the Safety Clearance layout in browse mode and test filling out the form:

1- Initially, the form contains the value stored in the g.OpsFacil field

2- clicking the dropdown DOES give me the appropriate list of names!

3- when I select a name, it changes the value of the g.OpsFacil field

The field on the form should NOT be the g.OpsFacil field. It should be the field you ORIGINALLY HAD THERE formatted as a dropdown list getting values from the OpsFacilVL value list.

[Joe_Schmo]

The field on the form should NOT be the g.OpsFacil field. It should be the field you ORIGINALLY HAD THERE formatted as a dropdown list getting values from the OpsFacilVL value list.

I corrected the Operations<newline>Facilities part. After that restored the original settings for the field.

But when you said "getting values from the OpsFacilVL" -I selected OpsFacilVL as the "Get Values From" in the Field/Control Setup window. Then on the right side of that window, what do I choose? I've tried several fields and I either get all names or none still.

I uploaded what I have so far in case you are interested. I'm about out of time today but I'll be back at it in the morning! Thanks for all your help!

Tagouts Copy 1.fp7 2.zip

[doughemi]

Username and password?

[Joe_Schmo]

OOPS! Forgot. Check your messages.

[doughemi]

You were almost there. The main error still left was in the definition of the value lists. See the sample for details. The other error is the one I spotted earlier, where you incorrectly changed the field on the form to be g.OpsFac.

Tagouts Copy Fixed.fp7.zip

[Joe_Schmo]

SWEET! It works! Thanks a million

I'll be updating the original tomorrow in all the applicable areas them moving on to the next problem LOL!

Here's a real tough question for you though: Why does it say "newbie" over your pic and "Advanced Member" over mine when you're the one answering all the questions?! I think they got our labels mixed up :)

[extreme]

Hey All is there a possibility you have a template for this action as I am having the same problem.

It would be much appreciated.

Thanks