Conditional list based on repetitions.

[MacSig]

Hello everyone. 
I'm coming back to FM after few years I haven't used it so I'm a bit rusty.
 

I have the 3 tables below:

System
Department
Owner

 

Department and Owner have a relation on the name so I have a conditional value list that allows me to filter the possible owners of a system based on the selected department. Now a system can be used in more departments so the System table I have updated the DepartmentName field to accept multiple repetitions (3) and I have updated the layout accordingly to show repetition 1 through 3.

Unfortunately now the conditional value list doesn't work. I would like to display in the list all the owners related to the selected departments.  For instance, supposing I have the following relationships

Departement1 ==> OwnerA 

Departement1 ==> OwnerB

Departement2 ==> OwnerC 

Departement3 ==> OwnerD

 

When I select as department Department1 and Department3, the list should contain OwnerA OwnerB OwnerD

 

 

How can I achieve so?

Thanks and have a nice day.

[Lee Smith]

Hi MacSig,

 

I moved your topic from "FileMaker Legacy Versions 7 – 9" to "Value Lists” because, the General Topics are reserved for the New Tools, Functions and Features introduced with that version of FileMaker, and not for asking how-to questions.

 

This topic area seems to match your question.

[comment]

I see two issues here: the first one is that your structure is wrong. If a system can be owned by several owners (and assuming an owner can own more than one system), you need another table of Deployments (or Installations?) to come between Systems and Owners.

 

The other issue is the conditional value list, that AFAICS should keep working even with the current flawed structure - so there's something missing in your description.

[MacSig]

Hi Comment and thanks for your reply.
Probably I wasn't clear enough (my bad) but a system can have ONLY 1 owner (an owner can own multiple systems) and to select the owner I wish to filter all the owners by the selected department(s).

The relation I have between Department and Owner is quite simple as shown below


And here my layout. If I select only 1 department the filter works fine.

However, If I select more than 1 department instead of displaying all the possible owners I see only the ones related to the latest selection as below.





Hope now the situation is more clear.

Thanks and have a nice day.

[comment]

I am afraid this is a bit confusing. If a system can have only one owner, and each owner belongs to only one department, then why do you need to select up to 3 departments before selecting the (one) owner?

[MacSig]

Because the system may be used by 3 departments so in that case I want to select the 3 departments where it is in use and later on, when I select the Owner I want to make sure that the owner belongs to 1 of the selected departments.

 

For instance let us say I have the following data

DEPARTMENT
Production
Quality Control
Warehouse

OWNER
Mark - Production
John - Production
Alice - Quality Control
Bob - Warehouse

And I have the Autoclave X used by Production and Quality Control so when I create a record for Autoclave X I select Production and Quality Control as departments. Now I want to make sure (and also providing a good user experience) that the Owners menu shows only Mark, John and Alice and does not include Bob.

 

Is this clear enough now because otherwise I'm not sure I'm able to explain it further.

Thanks

[comment]

With regard to the value list, using your current structure, see if the attached file works for you.

 

BadStructureCondVL.fp7.zip

 

 

With regard to the structure:

 

If one system is used by many departments, and - presumably - one department can use many systems, then strictly speaking you should have a join table to resolve the many-to-many relationship, e.g.:

 

Department -< Owners -< Systems -< Usage >- Departments

 

Without it, you have no room to enter any details that concern the usage itself (e.g. start date) and your reporting capabilities are severely limited - for example, you cannot produce a report of systems by department.

[MacSig]

That works. 

Regarding the structure, currently I don't need to keep track of specific data for each department, e.g. date since the system is used by a specific department so, for now (to keep things simple) I don't see the need to introduce a join table.

 

In the future perhaps this requirement will emerge and I will address it.  Thanks by the way to point it out.

Since you have been so helpful, is there a (simple) way to remove a value from the list once selected? In this way I can make sure the same department is not selected twice.


Have a great day.

[comment]

is there a (simple) way to remove a value from the list once selected?

 

Simple is in the eyes of the beholder. See:

http://fmforums.com/forum/topic/51074-tough-one-excluding-value-list/?p=266667