2 Replies Latest reply on Jun 25, 2010 6:00 AM by Antony

    Showing filtered data from two table



      Showing filtered data from two table


      Filemaker Pro 7, Windows XP SP3

      Experience level : low





      I begun working with FM7 about a month ago so I do not have much experience with this program. I have a background as programmer and worked with SQL database in the past. I was absolutely amazed on how FM is not instinctive! It works really well for simple queries but scripting and more complex queries are really hard to achieve. Unfortunately, the DB is already built and I do not have the choice to work with it. Ok, enough with the ranting, here's my question:


      I work for an university. The database I use contain two table : One for the students and one for the "stages" (internship?). A student can have multiple "stages" but one "stage" can only have one student.


      The student table contain basic info  :


      -date they entered the university program



      The "stage" table contain:

      -the name of the place of the stage,

      -the beginning and ending dates of the stage

      -the "sigle" of the stage. The sigle depends on if its the first stage (ENV7550 Stage 1) or the second stage (ENV8500 Stage 2).

      -"stage_over" if the stage is completely over (all documents and reports were received)



      What I have to do is write a script that will make a list of the students that begun the program this year and whose stages are not over.


      I made a new list layout where I specified that I wanted to see records from "Students" but then, the fields I added for the "stage" table are completely random. It only list the first from the stage list that every student have.


      I tried with a list that gets the records from "stage" but then, when I change layout to scroll through the students generated by the script, it only show me one. The records are good but I need to be able to scroll through the student list! How can I do that? It should be really simple but I can't find how! Note that I searched a lot on the internet but I must not have the right keywords because I found nothing!


      Second question : I'm having trouble with the "specify calulation" windows. I can't find how to make a basic comparison between a text value and something else. I want to do : table::field NOT EQUAL to "o". I tried the crossed =, <>, !=...nothing worked?


      Thanks a lot,

      PS : English is not my first language, I hope you understand what I mean!


      Antony Z.



        • 1. Re: Showing filtered data from two table

          Do you know how to set up a portal? A portal on your students layout can list all the related stage records for that student and this may meet your needs here. There are ways to set up the portal (Filemaker 11) or its relationship (older versions) to "filter" the stage records to show just those that are not complete. There's more you can learn about portals by looking that word up in the Filemaker Help system.


          Another option is what you have already attempted. Create a layout based on Stages for listing the data. It sounds like all you need to do is set up your layout as a List View (or possibly a Table View) so that you can see many stage records on your screen at once. You can do this simply by selecting the correct view from the view menu. (For list view, you also need to arrange your fields in a tabular (table like) format for better viewing.)


          You can place fields from the related student table in the header of this report (If you do a find to see only records for one student at a time) or in a SubSummary part if you also sort your records in an order consistant with what you selected when creating the subsummary layout part.


          This is called a summary report.


          If that looks like what you need see this thread for a tutorial on the subject:
          Creating Filemaker Pro summary reports--Tutorial

          • 2. Re: Showing filtered data from two table

            Thanks a lot Phil ! That's what I was looking for! Sorry for the delayed reply!