3 Replies Latest reply on Sep 29, 2013 10:41 AM by philmodjunk

    Relationship Help Needed (SQL Database)

    bvondeylen

      Title

      Relationship Help Needed (SQL Database)

      Post

           In our SIS (Infinite Campus), I am trying to create a report in FileMaker Pro 12.

           Want to get the address of parents

           The following tables are giving me difficulty.

           dbo.identity <--> personID  <--> dbo.householdmember

           dbo.householdmember <--> householdID  <--> dbo.household

           dbo.household <--> householdID  <--> dbo.householdlocation

           dbo.householdlocation <-->  addressID <--> dbo.address

            

           The relationships are listed above with the field in the middle. Now the problem.

           there is a field in dbo.householdlocation the contains an endDate 

           There are some people that have multiple entries in dbo.householdlocation and the 'old' entries have an endDate

           The active entry has a null value in the endDate field. 

           How can I create a relationship between dbo.household and dbo.householdlocation to only get the correct dbo.address record?

           I cannot match anything to endDate since there is no endDate field in dbo.household

           Is this something that FileMaker cannot do?

        • 1. Re: Relationship Help Needed (SQL Database)
          philmodjunk

               Are you trying to set up an SQL query to extract this info or are you trying to use Manage | Database | Relationships to define a relationship between two tables populated with data from the external data source?

          • 2. Re: Relationship Help Needed (SQL Database)
            bvondeylen

                 I am trying to use Manage | Database | Relationships to define a relationship between two tables populated with data from the external data source.

                 I know I can create a portal which would show all the addresses for people who have moved, but I only want the most current displayed (for a school directory).

                 Using FileMaker is great for reporting (more like a desktop publishing package). 

                 I have not delved into the SQL Query aspect of this. If that would work, I could try it, but I would need a little explanation on how to do that.

            • 3. Re: Relationship Help Needed (SQL Database)
              philmodjunk

                   How can I create a relationship between dbo.household and dbo.householdlocation to only get the correct dbo.address record?

                   I cannot match anything to endDate since there is no endDate field in dbo.household

                   You should be able, in Manage | Database | Relationships to specify a sort order that sorts the related records in ascending order by EndDate. That should make the current address record the first related record.

                   And your other option is an SQL query with a WHERE clause that specifies that the EndDate field be null.