4 Replies Latest reply on May 17, 2011 8:04 AM by philmodjunk

    Relationships, Portals and Lookups



      Relationships, Portals and Lookups


      Hi all,

      Apologies if this has been answered in a previous post. Im relatively new to Filemaker and have searched but I think a lack of knowledge is preventing me from understanding whether the scenarios described should also apply in my  specific situation.

      I have a single database with two tables: The tables are "Projects" and "Timesheets".  The Projects table lists out the current projects being undertaken and all supplementary information relating to each project. The Timesheets table is a form in which staff will input their activities and time against specific projects.

      Each timesheet has a discrete "Call ID" field. Likewise each project has a specific "Project ID" which is why I've stored them in separate tables. When recording time against a given Project, the Timesheet form has a field called "Project Name" which is used by the staff to decide which Project they should be recording their time against. I need the "Project Name" field on the Timesheet form to Lookup the list of projects from the Projects table and return those records as a drop down list. Ideally, I would like that drop down list to filter against an additional field in the Projects table and only return those records that have a "Project Status" of "Running".

      Whilst initially I thought a Lookup might be the right way to do this, I need to ensure that if the name of the project changes for any reason that the Timesheet forms associated with that Project are also modified with the new project name.

      Im not really sure how to achieve this as initially I related the tables so that each table had a "Project Name" and a "Project Status" field which were matched (=). But I found that wasn't returning the data I needed. I've tried global storage and that doesn't achieve what I wanted and also I gave Portals a try but that only seemed to work if I was also doing a Lookup and the form wasn't working the way I wanted. Really Im not sure where to go from here as I seem to be going round in circles and unfortunately whilst my knowlege of relational databases is probably insufficient, at the end of the day I need to get this working.

      Really appreciate any help that could be provided.


      Gavin Camilleri



        • 1. Re: Relationships, Portals and Lookups

          I can give you part of the an answer as to how to make the value list filtered the way you want

          First create a new table Called Global Value Filters.

          Add a field to that table called Project Status Filter and set it to global storage (it needs to be the same field type as your project status)

          Go to the relationships tab and establish a relationship between Project Status Filter in this new table an Project Status in your Projects table

          Now go to manage value lists.

          Add a new value list called running projects. Select  "Use values from field" and click  the "Specify field" button

          Select your projects table Select the Project Name field th

          "Select include only related values starting from:" and select the table Global Value Filters.

          Now you will need to populate the Global Value Filters table with the status code for running projects You can do this via a simple statup script so you wont have to worry about it ever again.

          You need to create a new layout with the Global Value Filters fields on it (you can make this layout hidden)

           I would create a new script that basically does the following

          Go To layout Global Value Filters

          Select all Records

          Delete all Records

          New Record

          Set field [Project Status Filter ,"Running]

          Go to Layout (previous)

          I would name it something like Set intial Value Filters and you can call it from a startup script.

          You can now use this table to create a number of relationships and filters just by adding fields and setting their values (you can manipulate the value of the field from anywhere but you must have at least 1 record. (You could easily just add the record manually then simply populate the value every time you start up the database or go to a layout where the value is needed) The global field doesnt need to be on the layout to populate it.


          You can solve the project name change issue by adding a Project ID field to the Timesheets and populating that via your drop down instead of the name field (You can use the same value list but edit it by checking the boxes also show values from second field box and selecting the name and selecting the box that says show only values from second field.

          This will populate the id even though they select the name. You can then show the name as a related field as you are doing with status.


          • 2. Re: Relationships, Portals and Lookups

            First you need the right relationship link your two tables. If you use Project Name for this, you'll have trouble when the need to change a project's name arises as you've already indicated may happen.

            Define this relationship to link your two tables. You'll need to add ProjectID field to your TimeSheets table in order to get this to work:

            Projects::ProjectID = TimeSheets::ProjectID

            Now, when creating a new TimeSheet record, you can enter the project ID number into TimeSheets::ProjectID to link it to a record in Projects. With that value entered, any fields from Projects, such as the Project Name field that you need can be placed on this layout and will display the correct project name or other info from that record in projects.

            Since you don't want your people to have to remember projectID numbers, you can format this field with a drop down list or pop up menu that lists ID numbers and project names from Projects. Show ProjectID in column 1 and Project Name in column 2. When you select a project from this list, you'll see the project name so you know which one to select, but the value list will enter the proejct ID number for you into the current TimeSheets record to link it to a project record.

            There are two ways to make this list a conditional value list that lists only ProjectIDs and Names that have a status of "Running".

            Option 1: Define this calculation field, cRunningIDs as IF ( Status = "Running" ; ProjectID ), then refer to cRunningIDs instead of ProjectID in your value list setup.

            Option 2: Define a separate relationship that matches to a new table occurrence of Projects like this:

            TimeSheets::constRunning = ProjectsbyStatus::Status

            constRunning can be a text or calculation field with the value "Running" in it. Then refer to ProjectID and Project Name fields from ProjectsByStatus instead of Projects and select the Show related values starting from TimeSheets at the bottom of your value list set up.

            • 3. Re: Relationships, Portals and Lookups

              Thanks to all who responded. I found PhilModJunk's answer was best able resolve my issues. I used Option 2 btw because I had problems with Option 1 in that it reported that the cRunningIDs field was unstored and therefore couldnt be indexed and consequently couldn't be used for a value list.

              Thanks alot.


              • 4. Re: Relationships, Portals and Lookups

                I'd tend to use option 2 myself, but I see no reason why cRunninIDs would be an unstored field if defined in Projects like it should be in order for this to work.