5 Replies Latest reply on Apr 9, 2014 1:01 PM by JohnBrennan

    Having trouble with design



      Having trouble with design



           I have a problem that I can’t seem to get head my around.  I’m trying to build a database for processing window blinds orders.  I first looked at the possibility of using a portal in this situation but as some of the blinds will require braking down in to many options, which will require itemised pricing and measurement details etc. and this just seems out the scope of a portal.


           The ERD looks a bit like this,


                                                       Order Summary (Portal)


           Customers -> Orders Details -> Order line items <- Blind Types <-Component List                                                          |

                                                               Production lists


                                                                Invoice Ledger       


           I hope that someone has come across this situation, before.

        • 1. Re: Having trouble with design

               There are articles about database design that were useful to me, I reread them occasionally.


               It is common for database designers to become stuck with design that includes a variable list of parts and ask questions here.

               If you add "site:forums.filemaker.com" to a search it will return only results from that site.

               Needed information to be included whether on screen or on paper is defined by the viewer and what information they need.

               Customer, floor foreman, operations manager, inventory manager, etc.

          • 2. Re: Having trouble with design

                 Here is a very general description of two options that you can explore:

                 1) Use two portals on your order or invoice layout. The first portal lists the individual items such as blinds and the second "detail" portal lists the options for one item selected in the first portal. A button and/or script trigger in portal one performs a script that "synchs" the related records shown in portal two to show only the details for the current/selected item in portal one. See: Need layout solution for nested portals... for more details on this method.

                 2) Instead of the usual Form View Invoice layout with a portal to invoice Data (also called LineItems), Set up a list view layout based on InvoiceData with fields from Invoices in the header. Then a portal to the "additional details" can be placed in the body to list details as needed for each line item entry.

            • 3. Re: Having trouble with design

                   I understand that 2 portals might work, however most of my products have far too many options and variations for portals to work.

                   I'm now exploring the idea of using the tab controls as this seems to fits much better with want I hope to achieve plus due to the large number of options, variation and calculations involved for each type of blind this seems to works well at this time.  The only problem I now have is auto copy  the order ID (which is an auto enter serial number) from the "Orders" layout to my "Orderform" (line item) layout.  At this time I'm not very good at scripting this function. 


              • 4. Re: Having trouble with design

                     One method is to use the OnRecordLoad trigger on the Order form to set a global variable, $$OrderID to that value. You can then set up the matching field in line items with an auto-enter calculation that enters the value of $$OrderID.

                • 5. Re: Having trouble with design

                       Thank you so much, I forgot all about that function, I'II give that a go on my layouts.