Conditional value list wont work from external file
I have a Projects file with two tables; Projects and Jobs. Each project may have several jobs so the Jobs table is related to the Projects table via a Project ID number. Each job has an ID number in addition to the project. All job ID numbers are unique. Each project has a Status field that has the value Active, Dormant or Closed.
I also have a separate file for recording billable time. It has a single table for recording start and end time spent on individual jobs. Each record in this table has to be assigned a Project ID and a Job ID so it has a field for each of these IDs.
The tables are in separate files for historical reasons. They were originall created in Filemaker 2.2 but have been updated to current version 11 along the way.
The Projects file is designated as an external data source in the Billable Time file and an occurrence of the Projects table in the Billable Time file is related to the Billable Time table via the Project ID. An occurrence of the Jobs Table in the Billable Time file is related to the occurrence of the Projects table via the Project ID.
When I make a new record in the Billable Time table, I use a conditional value list from the Projects file to enter a Project ID from a drop down list of active projects.
The projects file contains a value list for active projects. It is built from a calculation field in the Projects table that looks like If(Status_Project="Active",ID_Project,""). This list works fine in the Projects file and in the Billable Time file.
The Projects file also contains a conditional value list to show only jobs that are active in any given Project. I is built from a calculation field in the Jobs table that looks like; If(Status_Job="Active",ID_Job,"") and uses only related values starting with the projects table. This list works fine in the Projects file, but will not work in the Billable Time file.
I can make a portal (containing the Job ID field) for the occurrence of the Jobs table in the Billable Time file and it shows only the job IDs related to the Project in each time record, but I cannot get a drop down list to do the same thing with a conditional value list from the original Jobs table.