1 Reply Latest reply on Jan 25, 2011 1:03 PM by philmodjunk

    New to Filemaker



      New to Filemaker


      I am new to Filamaker and am stuck creating a report. I have 2 tables, one called BUYERS and the other one called EQT (EQUIPMENT). Both tables are related by a BUYERID which links the buyers table to the equipment table. One buyer could have multiple entries in the equipment table.

      The equipment table is a list of equipment owned by each buyer. I have a one-to-many relationship between the BUYER and EQT tables. I want to find buyers that own at least one equipment of a certain brand or type.

      I want a field where I can type in a brand or equipment type, and get a list of buyers. In the equipment table I have a brand field as well as an equipment type field.

      Thanks for the help!

        • 1. Re: New to Filemaker

          You currently have this relationship:


          Buyers::BuyerID = EQT::BuyerID

          I'm guessing here that you are using FileMaker 11. That gives us a fairly simple option for this.

          Add this relationship to what you already have:

          Buyers::BuyerID X AllBuyers::BuyerID

          In Manage | Database | Relationships, select Buyers, then click the button with two green plus signs to make a new table occurrence of Buyers and name it AllBuyers. Drag from BuyerID in one box to BuyerID in the other, then double click the new relationship line to pop up a dialog where you can change = to X. The X operater means that any record in Buyers will match to every record in AllBuyers.

          In the same fashion, add a new table occurrence of EQT, AllBuyers_EQT and link it to AllBuyers by EquipmentID.

          AllBuyers::BuyerID = AllBuyers_EQT::BuyerID

          Define two text fields in Buyers as Brand and EQTtype.

          In layout mode, add a portal to AllBuyers on your Buyers layout. In the portal setup... dialog that pops up, click the Portal filter check box and enter this filter expression:

          ( AllBuyers_EQT::EquipmentType = Buyers::EQTtype ) or ( AllBuyers_EQT::Brand = Buyers::Brand )

          Almost done, now write a one line script to refresh the portal every time you select or enter a different Brand or Equipment Type: Refresh Window [Flush Cached join results]

          Format your Buyers::Brand and Buyers::EQTtype fields with value lists of Brand and equipment types from your EQT table and use either an OnObjectModify (pop up menu, check box, radio button) or OnObjectExit (Drop down list) script trigger to run the Above one line script.

          This is a fairly simple approach just to get you started. More sophisticated portal filters that match on different rules are also possible.