8 Replies Latest reply on Jan 28, 2014 4:06 PM by FentonJones

    Advice on database structure



      Advice on database structure



           I need some advice on a database.

           I have customers, requests, offers, line items and products.

           The way it goes is the following:

           1.     The customer is registered

           2.     A customer’s request is registered.  CU needs a product of specific characteristics let’s say size and price is defined.

           3.     Based on the specifications I have a found set that I have to go through manually and select the green ones, because color is not an attribute of the products.

           4.     I want to select from the found set (I have too see each product) and add the selected products to an offer. Send this offer to the customer.

           5.     The CU either buys one product of the offer or asks me to send further offers in case I have new green products...


           This means a customer can have many requests, and each request can have more than one offer, and each offer can contain one or more products.


           Relationships look like this:


           Problem Nr 1:

           I can create an offer from the request page, but after that I should open all the products in a new window which are already filtered by size and price.


           Problem Nr 2:

           When I select a product to be included in the offer the products id gets copied to the new record in the line items table, but the offer id gets lost, It doesn't know where it is coming from.


           I hope you understand what I want to accomplish! :-)


           Many thanks in advance!

        • 1. Re: Advice on database structure

               Many to Many relationships require a Join Table   (Google "join table site:forums.filemaker.com")

               Many Students can have Many Classes - a join table "StudentClasses" is needed.

               The first five links in this list may be useful.

          • 2. Re: Advice on database structure

                 Thank you very much for your answer!

                 May I ask you to draw the ERD for this solution?

            • 3. Re: Advice on database structure

                   To expand on DavidAnders suggestion:

                   You need to add a table of Products that is separate and distinct from Lines. Selecting a product for a given offer should create a new record in lines that links it to both Products and Offers.

              • 4. Re: Advice on database structure

                     It is sure that I am not really experienced in database design but I have to admit honestly that I still don't get it...

                     Here is simplified database to demostrate the problem: http://cl.ly/2o0W1l0q0z2d/download/Products.fmp12


                     I appreciate any suggestion!

                • 5. Re: Advice on database structure


                       There are a few minor things, which were stopping yours from working, and a kind of overall method which would make it work, but requires some scripting, and the using of a specific type of layout. 


                       The main problem you were having is that Get(ScriptParameter) does not "keep" its value after a script is ended; it goes back to nothing. One way of getting past that is to "send" it to the next "sub script". But that is not what you are doing. You are going to a new window, then starting another script, from there. 


                       My method is to create your new window. But it is a specific layout, only visible during this scripted process. It is the ONLY layout which has the button script to add a new product to a Offer (which you've just come from).


                       That layout has a script (button) which will add the product for a Offer. It will also test that you do NOT go to any other layout. It has an OnLayoutExit Script Trigger, which will Close the window and HALT all scripts if you try to leave the layout manually. That script requires that you tell it the name of the window. There is also a button to "Close Window", if a person just want to click it to leave.


                       I know, sounds a little complex, and it kind of is. But each piece is not big. Without them you will have trouble making something that both works with 2 windows, passing data from one to the other, and is safe. I not real found of the HALT step generally, as it will stop all running scripts. But in this case it seems OK. If anyone knows a better method, please do tell :-]


                       I put the file here, for a while.



                  • 6. Re: Advice on database structure

                         Thank you Fenton!

                         It seems to work as I expected. What I should now figure out is how to display only the products that are alreday filtered by the request's criteria.

                         And yes it looks now more complex than I originally thought it would be. I will now "take it apart" and analyse it to understand it fully!

                    • 7. Re: Advice on database structure

                           I think I have managed what I wanted in a very simple way. Could please someone experienced have a look at it if there are any "traps" behind this idea?

                           Some things are still not working, which I have commented on the layout and adding a new line item or an offer has no duplicate check currently. Suggestions to these are also welcome! :-)

                           It is 4 am local time, so I better go to sleep!



                      • 8. Re: Advice on database structure


                             I took a look. The problem in the Offers layout is that the field (_kp_products_id) in the Lines portal is NOT a Lines field (it's still a Product's field). Just change the field to Lines and it will show correctly. When you see that all fields are repeating on each row of a portal, it usually means that you're using the wrong relationship for the lines.

                             The one thing I would change is the Products portal on the Requests layout. I would put it on the Offers layout. It will work the same, as each Offer can see its Request, hence can use that same portal, and in this case the same Products rows. The problem with it in Requests is that it cannot be sure what Offer you're on; you are creating a new Offer for any added Product. If you run from the Offer instead, you just use the Offer you're on; though obviously you'd need to create any needed Offer from the Request layout. Doesn't that make sense?

                             You are still using a "new window", without locking it; which works fine, if people behave themselves. But it would make me a little nervous, as someone not paying much attention could change windows, change Offer records, then click the button to add a new record to Lines, but using a previous Office ID (from the one they're now own). Yes, not likely, but possible. Hence all the extra steps in the scripts to lock it. But you can see how it goes.

                             In my version of the file I thought I'd try to do this without a new window, to avoid all that. I put the fields required to see a Product into the portal, including a smaller (1/2) size of the picture. You can just click a button there to add one. However it depends on not needed to show much more.