4 Replies Latest reply on Oct 14, 2016 12:45 PM by philmodjunk

    Suggestions for avoiding a Relationship Loop


      I'm fairly new to filemaker and working on a pretty complex app and have ran into my first big problem. Looking for advice on how to solve it in the best way using filemakers strengths.


      I have a "Lead Capture" screen where a tenant leads contact information and housing criteria will be entered. On that layout I'd like to have a portal that filters all of our current rental listings by the leads criteria (# of beds, # of baths, price, school district... etc).


      The Lead's Criteria fields are all in the Lead table. But the information to match it are not all in one table. Bedroom and Bathroom information is in the Unit table, Current listing price is in the Listing table (which relates to a single unit via a foreign key), and school district is in the property table ( which relates to one or more units via foreign keys).


      An obvious solution would be to put all the criteria I'd like to match against into the listing table, but that creates redundancy in data entry. I'd rather our employees not have to re enter all the unit information everytime they create a listing when that information is already inside the database.


      THANK YOU for any advice or help! If my problem isn't clear please let me know and I'll try to upload a visual of the problem!

        • 1. Re: Suggestions for avoiding a Relationship Loop

          You would appear to have these relationships:



          Unit::__pkUnitID = Listing::_fkUnitID
          Unit::__pkUnitID = Property::_fkUnitID


          Is this correct?


          If so, a portal to Unit can be set up with a filter expression that filters for values in Unit, Listing and Property.

          • 2. Re: Suggestions for avoiding a Relationship Loop

            Table Occurences on Lead Capture Screen.png

            Here's a screenshot of the TO's for that layout so far (Layout is working from the TO at the bottom Left, the Portal is for the TO named "Listings that Match Lead Criteria")


            You can see I'm evaluating against beds criteria and baths criteria now, so as a person changes their criteria the listings on the portal view change. I'd like to also be able to evaluate against "Lisiting :: listPrice" vs "Lead :: priceMax" and probably even "Lead :: schoolDistrict" vs "Property :: schoolDistrict".


            Is a filter on the portal the only way to do this? If so do you have any idea what this would need to look like in order to make the filter dynamic to the leads criteria entries?

            • 3. Re: Suggestions for avoiding a Relationship Loop

              A filtered portal is not the only option, but it's the easiest to implement. It may be slow to update however, you'll have to try it and see.


              First, two changes to your data model:

              Make the relationship between Unit criteria for Eva... and Listings that Match.... a sorted relationship that sorts by listing date in ascending order so that the first related listings record is the most recent. Then add a TO of Property to your graph linked to Unit Criteria for Eva... by PropertyID.


              Then your portal filter might look like this

              Lead Criteria::Max Price <= Listings that match lead criteria::List Price AND

              Lead Criteria::School District = New PropertyTO::SchoolDistrict


              A more complex calculation that allows for empty Max Price or School District criteria may be needed.


              Another method that might be used is to use ExecuteSQL to query on all of these criteria, returning the ID's of all matching units as a matching list--which then can be the match field to a TO of Units to list the actual unit records.

              1 of 1 people found this helpful
              • 4. Re: Suggestions for avoiding a Relationship Loop

                Correction. that sorted relationship should sort by date in Descending order...