AnsweredAssumed Answered

Help me Understand Filemaker Relationships

Question asked by LawrenceWatkins on Jul 31, 2010
Latest reply on Aug 2, 2010 by FentonJones


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!