Conditional Lookup with global field
I’m currently creating a database in FMP13 for our staff to input time records that essentially store the employee, date, client, project, and the amount of hours worked. For each Time Record entry, I want the employee to select the client first, then the list of projects available under that client. So my tables are currently that Client is one-to-many to Projects, which is one-to-many to Time Records. The only way I have been able to allow selection of Client first is to create global field in the Time Record table, then relate a new instance of Time Record to the field “ClientIDforeignkey” in the Project table. This allows me to select from all available clients listed in the Project table (in the global field from Time Records 2). Then when I select the appropriate project, the client information is filled into the rest of the fields. It seems that there should be a better way of doing this, but I cannot find one. All the forum suggestions I’ve seen either don’t seem to apply or have an example file that I cannot download. Please suggest better ways!
If there is not a better way, I am running into layout trouble in a different layout. I have a layout listing all the time records associated with an employee over a specified time period. I want this to be the main interface for the employees to add time records. But as you can imagine, putting the global field in here has the possibility to goof up the look and functionability of entering data because all of the project fields are based on one global field. Any suggestions here? Thanks and let me know if you need clarification!