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.
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!