1 Reply Latest reply on Dec 7, 2010 10:21 AM by philmodjunk

    Queries and How to In Filemaker Pro

    MikeHaddon

      Title

      Queries and How to In Filemaker Pro

      Post

      I am a longtime MS Access user, but to get a database app working on my iPad and iPhone, I am beginning work in FileMaker Pro 11.0.  I worked my way through understanding how to add tables and forms (layouts), but I am very stuck in figuring out how FMP works with data selection.

      Let me provide an example of what I am trying to accomplish.  I work with the Utah Legislature during their session.  I am developing a database that provides me information (at my fingertips) regarding pertinent items.  My primary table (Main) includes names, party affiliation, email, phone, photograph, and committee membership.  I created a Committee table that simply lists each of the legislative committees, as well as a short unique identifier associated with each Committee (for example, House Education Committee may have an identifier of 1H.

      So, back in my Main table, if a legislator is a member of the House Education committee, in the field Standing1 (Standing Committee 1), I would have 1H (rather than redundantly adding the committee name for each legislator).  This becomes a little complicated, as legislators may belong to several standing committees.

      With this background, I was trying to create a layout where there would be a dropdown listing of all committees.  Based on the committee I select, the layout would populate the layout with all legislators who are members of that committee.  I have gone into the table relationships and established a relationship between the committee ID (in the Committees table) and the Standing1 field (in the Main table).  I have no problem creating a layout that includes fields from both tables, but nothing populates.

      Anyway, back in MS Access, I simply would have written an SQL query that would select name, email, and phone number where the standing1 (in Main) equals committeeID (in Committees).  From there, I could actually create a form based on the query itself.  In FileMaker, I am really struggling to understand how to duplicate something like I did in MS Access.

      Anyone with time and compassion - please help!

        • 1. Re: Queries and How to In Filemaker Pro
          philmodjunk

          Yeah, Access and FileMaker do things differently here. Instead of record sets, think "found sets" and instead of queries, research how to perform finds both by hand and via script.

          Here's a rough outline of a script and layout design that will do what you describe:

          Define a global field to format with a drop down of your comittees value list. I'll call it gCommittees. This is roughly the same idea as a text box on an Access form set up with a combo box.

          Write this script:
          Enter Find Mode[]
          Set Field [YourTable::CommitteeName ; YourTable::gCommittees ]
          Set Error Capture [on]
          Perform Find[]
          Sort [no dialog; Restore]

          You can set up a script trigger on your global field to perform this script each time a value is selected from the drop down.

          PS. I'd consider using a join table to track committee membership