3 Replies Latest reply on Feb 6, 2013 3:11 PM by philmodjunk

    New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients

    PharmerJohn

      Title

      New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients

      Post

            

           FM Pro Adv 12    on Mac  OS 10.6.8   New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients

            

           Application of consultant visiting different companies and testing employees then recording & reporting results.

            

           Tables:    COMPANY lists company name, address, contacts, etc

                             CLIENT  lists employees being tested, name, Company Name, ID, dept, 

            

                             Relationship has been set for Company:: Company Name = CLIENT:: Company Name

            

           Desired assistance

            

           Have created a visit initiation form that lists current company name (from drop list), name of tester, start date for reporting window, end date for reporting window on a main menu screen. Wish to have a button that will launch (open) a client screen that lists ONLY the clients employed by the current  company.  The tester should not have access to clients of other companies from this screen (this prevents the use of the find/constrain records for this field)

            

           How can I create a script that will limit the clients to the current company for data entry? (have done this successfully using MS Access, but cannot get my head around FileMaker filtering)

            

            

        • 1. Re: New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients
          philmodjunk

               Remember subforms in MS Access?

               In fileMaker, we call them "portals".

               If you base your layout on Company, a portal to Clients will only list the clients related to the current Company record on your layout.

               But I think you need more tables than you currently have here. Seems like you need another table for your test results for each client if they are to be tested more than once and you might also need a table for each visit made to a given company.

               And I strongly recommend that you NOT link tables in relationships by their names. (This is also not a good idea in MS Access BTW.)

               You should define an auto-entered serial number field in Company that links to a number field in Clients and use it for your relationship. This avoids issues that can occur when a company changes it's name, you find that a company's name has been entered incorrectly or you sign up a second company with the same name as another company that you service.

          • 2. Re: New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients
            PharmerJohn

                 I understand portals and subforms. I do NOT want a portal. I want a "layout" (form) that displays 1 page of "client" information (so that the record count at the top of the page is the total number of clients at that company. NOT all the clients in the entire database. This "client" layout may then have portals for the testing information.

                 Again, a company is selected on a "visit initiation form". I want to then open a "Layout" of clients (limited to that particular company).

                 I did not mention test or results tables because they are not relevent to the issue I am having difficulty with. 

            • 3. Re: New user of FileMaker wishing to filter (constrain) records BEFORE viewing clients
              philmodjunk

                   As you have discovered, FIleMaker doesn't do things exactly the same way as Access. For one, you can't define a query to use as the record source for your layout like you can for a report or form in Access.

                   But you can perform a find or use Go To Related Records to pull up all client records for a given company.

                   But how do you determine which Company you want to specify? There are a number of different ways that your layout can do that.