Conditional Value List between 3 files

[Matt Palmgren]

I have 3 databases: Staff, Items, and Transactions. The Staff file contains information about staff members (obviously) such as Staff Name, and a serial number that is used for relationships. The Items database contains information about things that can be assigned to staff members, such as a mailslot, a nametag, and a phone number. Each record in the Items database contains a serial number, the item name, and item type. The Transactions database is used to link the one-to-manys between the Staff and Items database. I created the usual relationships between the files so I am able to go to a staff record and assign items to the person using drop down menus.

The only thing I can't get to work related to the phone numbers. In the Items DB I have 3 records, each with a different phone number. What I want to have happen is when you go to a staff record and assign a phone number record to the staff member, I want a second value list to contain a list of phone numbers that have not yet been assigned. This would be simple if there were only 1 or 2 DB files, but since the only way the phone number record in the Items database knows it is assigned is through a relationship with the Transactions database, none of the fields I create to use as the value list can be indexed.

I know this is a bit confusing, the files are very small since they are just a test, so I can email them if it would make more sense. Thanks!

[djgogi]

Check the thread Automatically indexing non-indexable fields

Dj

[CobaltSky]

Hi Matt,

An alternative that you might like to consider is a synamic value lists technique which can be used to calculate value arrays based on data sourced from more than one file.

There is an unlocked demo of the technique available for free download from my web site. If you are interested, the URL is:

http://www.nightwing.com.au/FileMaker/demos/DynamicValues.zip

The procedure requires a script which is invoked when you access a pop-up field, and the calculations to achieve the list dynamism take place within the file the list is being used in, but use values from elsewhere, as you'll see in the demo.