1 Reply Latest reply on Aug 28, 2014 5:54 AM by philmodjunk

    Filemakering me crazy

    DSolomon

      Title

      Filemakering me crazy

      Post

           Hi,

           I am new to Filemaker and i have watched hours of video and read hundreds of very helpful forum posts but I just cannot seem to get a table relationship to work out.

           I understand the basics of table pk/fk and the one to many etc but rather confused with to's.

           As part of a bigger solution I am trying to create a quoting solution.

           "Companies" have many "Contacts"

           "Companies" have many "Quotes"

           "Quotes" have many "Quote Line Items"

            

           I am wanting two drop down lists at the beginning of a new quote, one to display companies and then the second to display active contacts within the company that was selected above.  Any help achieving this will be greatly appreciated.

            

           Dave

            

        • 1. Re: Filemakering me crazy
          philmodjunk

               What You describe are these relationships and this assumes that a given contact may only be linked to a single company (which is not always the case in every real world situation.)

               Contacts>----Companies-----<Quotes------<LineItems

               Companies::__pkCompanyID = Contacts::_fkCompanyID
               Companies::__pkCompanyID = Quotes::_fkCompanyID
               Quotes::__pkQuoteID = LIneItems::_fkQuoteID

               What you ask for would be a condtional value list that is "filtered" by two items of data: 1) the currently selected company on your current Quote records and 2) the Contact is somehow marked as "active" in the Contacts table. I'm going to outline how to set this up here, but include references to my new "Adventures In FileMaking #1" file that illustrates the techniques that I am referring to in detail. You can use the link at the end of this message to access my FaceBook page and download a free copy if you are interested in it.

               I will assume that you have a field in contacts named "active", that stores the number 1 if the contact is active and which is empty if not. If you use a different value, the change you'll need to make here is trivial.

               Use Manage | Database | Fields to add a new calculation field, cActiveClientName to Contacts defined as: IF ( active = 1 ; ContactFullName ). Make sure that text is the result type and that this is a stored, indexed calculation field.

               Open Manage | value Lists and define a "use values from field" type value list. Select Contacts as your "display values from" table occurrence and select __pkClientID for field 1 and cActiveClientName for Field 2. Select the option to show only values from field 2. Click the "include only related values starting from..." option and select Quotes as your "starting from" table occurrence in the drop down.

               This will produce a value list of active contacts for the company you select in Quotes::_fkCompanyID. (Use a standard use values from field value list for this field.)

               This produces a value list that is both a basic relationship based value list and a "hardwired" value list as described in my new "Adventure" file. You can find more detailed explanations of how this "hardwired" technique works in that file.

               Once you have selected a contact, you will likely want to show data about that contact on your Quotes layout. To do that, you need to add a second occurrence of Contacts and link it to quotes by the _fkClientID field defined in quotes. You can then either add fields from this new occurrence of Contacts to your layout or define fields in Quotes that auto-enter data from this new occurrence. (Many businesses prefer the latter approach so that future changes in the contact's info don't modify old Quote records.)

          Caulkins Consulting, Home of Adventures In FileMaking