5 Replies Latest reply on Aug 2, 2010 4:08 PM by FentonJones

    Help me Understand Filemaker Relationships



      Help me Understand Filemaker Relationships


      I am coming from the world of programming a Drupal website using PHP and MYSQL and thought what I was attempting to do with Filemaker would be simple.  I have purchased the book, "Filemaker Pro 11, the missing manual" and still cannot figure out how to setup my database.

      I am trying to setup an order system for a new startup.  I am going to create custom pictures and have several criteria that affect the price.

      So far I have setup a pricing table that has different prices for various options based upon the size of the picture.

      For example:

      Table Header -> price_id | size | name | price

      Entry 1 -> 1 | 8 | mat | 10

      Entry 2 -> 2 | 10 | mat | 20

      Entry 3 -> 3 | 8 | foam | 15

      Entry 4 -> 4 | 10 | foam | 25

      Entry 5 -> 5 | N | color | 5

      Entry 6 -> 6 | N | bw |10

      I have setup some relationship tables already:

      Customers ----< Picture >---- Invoices

      My problem is that I want to update prices in my Picture table entry based upon the options selected.  So, using my example entries above, if someone places an order for a size 8 with a mat mount, the price gets updated to $10.  If they select size 10, with foam, it gets $25, etc.  Compound on top of that, they can also select (not size dependent), color or b&w and those costs get added, as well.

      Now my example is only a subset of my problem.  I actually probably have about 25 options that affect price with some of them being size dependent and some not.  With PHP and MYSQL, I could do a simple query to lookup a value based upon the selected criteria, but I see no way to do this with Filemaker.  And the relationship issue seems very complicated.

      Do I need to setup a separate table for each of my sizes with the options listed in each of them?  Seems really complicated when I can consolidate them into one simple table.  How do I retrieve the price for an option selected from that table without being able to do a simple query search?

      Some kind sole, please help me understand.  I like what Filemaker allows me to do from an interface standpoint, but the backend is killing me.  

      Thank you so much for any assistance or nudges to examples!

        • 1. Re: Help me Understand Filemaker Relationships

          I was able to somewhat get the issue resolved using Table Occurrences.  However, I am still having some issues with the relationships and fields automatically getting updated with the values.  

          I have setup a relationship to the TO of the price list and have it match the size and the name for the table.  But for some reason, the price does not get updated unless I use a particular size and then only when another option is selected that has nothing to do with the price list.  

          Looks like I need to rethink how I approach this and start over again. 

          • 2. Re: Help me Understand Filemaker Relationships

            Each "price addition" should have its own field, at least to start with. (It would be possible to combine them, using 2 Lookup() functions.)

            If the relationship for the name/size has only the name & size fields in it, then only they should trigger a new Lookup. The Color relationship would be based on only the color.

            Are you use the Auto-enter Lookup option directly, or using the Calculation option, with a Lookup() function?

            Yes, you'd have to choose specified sizes, unless you used the "next higher" option.

            • 3. Re: Help me Understand Filemaker Relationships

              Some find this useful   White      Paper for FMP Novices


              There are open free templates that help some.




              You can sample parts of FMP howtos on this site


              • 4. Re: Help me Understand Filemaker Relationships

                Well, I have since realized that my approach to my database was flawed.  I have gone back to make it based upon the INvoice sample file. 

                In the process, I think I have come to understand the relationship issue a bit better.  In mysql terms, the relationship is basically the "WHERE" part of a query. 

                On the auto-fill issue, your question, Fenton Jones, prompted me to get my head around the auto-entry feature of Filemaker.  I was using the lookup feature and not the Calculation option with a lookup script.  I think I now understand the issue with that.  If I use the Lookup directly, it only works as a "defaulyt value" type entry.  If I use the Calculation part, it does that every time.  Am I correct in my understanding here?

                Davidanders, thanks for the links!

                I'm off to restructure my database! I appreciate your help!

                • 5. Re: Help me Understand Filemaker Relationships

                  Well, not really. The auto-enter by Lookup directly is (much) older option (FileMaker 2?). The auto-enter by Calculation, using the Lookup() function within the calculation, is much newer.

                  But they have the same options. All "plain" Lookups require a match as is. But in the direct Lookup, you can use the options for:

                  If no exact match,

                  (•) Copy next lower value, or
                  (•) Copy next higher value

                  This corresponds with the Lookup() function, by using its sister function instead:

                  LookupNext ( sourceField ; lower/higherFlag )

                  The main difference between the direct Lookup and the calculation function is that you can "chain" the functions; so it could do 2 or more in one calculation, either doing arithmetic with 2+, or doing a try/fail/try chain (the 1st successful one wins).

                  Lookup (some relationship::a field; (Lookup (other relationship::field) )

                  Or, in your case, adding two (yours should not be a try/fail situation; especially if you use the "higher" flag):

                  Lookup (size relationship::Price; higher) + Lookup (color relationship::Price; higher)

                  But I wouldn't that, for two reasons. 1. It's more complex (but not much), and 2. You likely want to see and show the two separately.