3 Replies Latest reply on Dec 28, 2013 3:25 PM by philmodjunk

    Orders database design?

    JamesWatt

      Title

      Orders database design?

      Post

           I'm using FileMaker Pro v13.0v1 (Mac).

           I'd like to create an orders database.  The database should contain customer information, order date, etc.,etc., but also a variable number of "lines" for what's being ordered.  By "lines" I mean that's what it would look like if it were printed.

           The customer info, pickup info, etc. is easy enough but it's the order items part I can't figure out.  I'm not even sure what to call what I want.  Portals are limited to a fixed number of visible fields so that's not good.

                      
                     Preferences
      §
      1
      2
      3
      4
      5
      6
      7
      8
      9
      0
      -
      =
      Backspace
                           
      Tab
      q
      w
      e
      r
      t
      y
      u
      i
      o
      p
      [
      ]
                                
      Return
                                
                           
      capslock
      a
      s
      d
      f
      g
      h
      j
      k
      l
      ;
      '
      \
                           
      shift
      `
      z
      x
      c
      v
      b
      n
      m
      ,
      .
      /
      shift
                           
                                
                               English
                                     
                                     
      alt
      alt
                           
                      
                     Preferences

           Any thoughts on how to do this or an example orders database?

           Thanks for any help..

           James.

        • 1. Re: Orders database design?
          philmodjunk

               I would suggest that you use "new from starter solution... to open up a copy of the Invoices starter solution to see an example of what you describe.

               

                    Portals are limited to a fixed number of visible fields so that's not good.

               If you really mean "fields", then that is as it should be since a variable number of fields would make no sense for this. If you mean a fixed number of Line Items, then is not really a major problem. You use a portal with a scroll bar for convenient data entry but print from a different layout that is set up as a list view layout based on the line items table where you can print out invoices with any number of line items and get them all printed out. And this is how this starter solution is set up so you can open it and find a working example of how to do this.

          • 2. Re: Orders database design?
            JamesWatt

                 Thanks very much for the response.  I'm having a look at the invoices database.  I suppose I just don't quite understand how the portals are supposed to work.  They're not quite another table but somewhat related to it.  The fields in the portal are from another table though.

                            
                           Preferences
            §
            1
            2
            3
            4
            5
            6
            7
            8
            9
            0
            -
            =
            Backspace
                                 
            Tab
            q
            w
            e
            r
            t
            y
            u
            i
            o
            p
            [
            ]
                                      
            Return
                                      
                                 
            capslock
            a
            s
            d
            f
            g
            h
            j
            k
            l
            ;
            '
            \
                                 
            shift
            `
            z
            x
            c
            v
            b
            n
            m
            ,
            .
            /
            shift
                                 
                                      
                                     English
                                           
                                           
            alt
            alt
                                 
                            
                           Preferences

                  

            • 3. Re: Orders database design?
              philmodjunk

                   Other than a few very simple demo files, I can't think of a single FileMaker database that I've created that didn't have at least one portal. And most have had multiple portals. It's an extremely useful interface design tool and one that is critical for you to know how to create in order to get good layout designs in place in your solutions.

                   A portal is a standard method for dealing with multiple related records (your line items or "invoice data" records) from the context of the parent record (The invoice record). If you open up Manage | Database | relationships, you'll find that a relationship between invoices and Invoice data (or Invoices and LineItems in FileMaker 11) has been defined and the portal you see would not work without that relationship. Related records appear in the portal when their match field has exactly the same value as the match field of the current layout record.

                   When "allow creation of records via this relationship" is enabled, this allows you to create new records in the child table (line items) simply by entering data into a blank row shown in the portal. In some cases, this option is not allowed and the developer supplies a button for adding related portal records. In the first case, FileMaker automatically copies over the needed match field value from Invoices into the new portal record. In the case of the button, the script that is performed when the button is clicked/tapped does the same thing--usually by copying the value into a variable on the Invoice layout, switching to a layout based on the portal's table, creates the new record, copies the variable's value into the new record's match field and then returns to the original layout.

                   If you examine auto-enter field options for the price field defined in  Invoice data or LineItems, you'll find an auto-enter field option that copies the price from a matching record in the related Products table. Since this stores a copy of the price in LineItems, a subsequent change to the price in Products will not change the price of any previously created LineItems records. Any new lineitems, however, will copy in the new price.