Filtering Value Lists

[mrfurious]

I have no idea if this can be done?

I'll try and explain it.

I want to develop a value list based on a relationship with another file. Upon making my selection from that list, I want that information to filter another value list from a separate file/relationship.

ie. To create an invoice, i need to select a customer and then the project. After selecting the customer, I don't want it to show all the projects. I only want it to show the projects relating to that customer. I have to create multiple invoices (ie. milestone payments) relating to the project.

Any help would be appreciated. Cheers.

[signal]

This is called a conditional value list. Search this forum because the question has been answered many times. It might even be in the FAQ topic section.

[richshone]

You can only do a conditional value list in FMP 5 and it won't work in earlier versions. I just tried one the other day and it worked great - to be honest it makes more sense than you would imagine at first, even for someone relatively new to relational theory like myself. You will need to create two relationships, one for the value list itself and one of them basically acting the same way that a break field would in a sub summary, only within the context of the value list instead. If you have FMP 5 you should look at the define value list dialog and you will see where you can do this. Good luck...

[Kay]

This is a similar question. I have an Employee file that is related to many other files. Each employee has an Employee Code and a Status Code (Active or Inactive). In other files, I wish to use a value list that is only the Active Employee Codes. I think this kind of filter should be easy but my brain hurts. Thanks for help. Kay.

[LiveOak]

I'll try to answer both questions. The key is builing a relationship that only relates to the records you wish to see and then defining the value list based upon this relationship.

First, to show projects by customer. You don't mention what your files are. I'll assume that they are "Customers" and "Projects". This is an easy one. If your Project file has a Customer# for each project, relate the "Invoice" file to the "Projects" file using a relationship matching Customer# in "Invoice" to Customer# in "Projects". If you don't have a Customer# for each project, you'll have to use the Clairvoyance() function to obtain one ( a little FM humor). Base you value list upon the Project file and the Customer# relationship.

Second question, to show only active projects. Create a field in the other files defined as:

EmpStatusKey (calculation, indexed) = "Active"

Define a relationship from which ever file to the Employee file with matches EmpStatusKey <---> Status. Create a value list based upon the Employee file using this relationship. Only the records with the value "Active" in the status field will be displayed.

-bd

[Kay]

You are better than Aspirin! Well explained. It works. Merci beaucoup. Kay.

[Alfonso Sainz]

I assume that most of you are FMP5 users and probably are not concerned with this note. But it is not true that conditional value lists are possible only in FMP5.

There are several ways to do conditional value lists with FMP3 and FMP4 (using fields, relationships and scripts). The difference is that with earlier versions you can't do it as directly as FMP5 does.

I think there could be FMP4 (and even FMP3) users that would have a wrong impression about the FMP capabilities or features, and is a fact that you can do a lot of things with earlier versions. And conditional value lists are one of those, without any doubt.

[Vaughan]

Alfonso

Yes you are correct, but the process for older versions is far from simple and (to me) is rather inelegant. For more information, take a look at the Database Pros web site in their hints and tips section for a working download-able example.

http://www.databasepros.com