3 Replies Latest reply on Jun 11, 2015 12:36 AM by starstuff

    Customer price list

    davrap

      Title

      Customer price list

      Post

           New to FileMaker and creating databases. Have looked at most of training  material but still not 100% sure how to proceed. My database consists of three tables:

             
      •           customers
      •      
      •           Products
      •      
      •           Prices by customer for the same product. Each customer has a different price for the same product.

            

           I would like to be able to input a customer name in the main part of the form which triggers in the portal the products ordered by that customer and the unique prices those customers pay.

           Can someone explain to me the best way to do this please.

            

            

      image.jpg

        • 1. Re: Customer price list
          philmodjunk

               Products|Customer----<Pricing|Customer>-----Customer-----Invoices-----<LineItems>----Pricing|Invoice
                                                                                                                                                             |--------->Products

               Match fields:

               Products|Customer::__pkProductID = Pricing|Customer::_fkProductID
               Customer::__pkCustomerID = Pricing|Customer::_fkCustomerID
               Customer::__pkCustomerID = Invoices::_fkCustomerID
               Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
               Products::__pkProductID = LineItems::_fkProductID

               LineItems::gCustomerID = Pricing|Invoice::_fkCustomerID AND
               LineItems::_fkProductID = Pricing|Invoice::_fkProductID

               Products|Customer and Products are two Tutorial: What are Table Occurrences? with the same data source table. Pricing|Customer and Pricing|Customer are also two table occurrences with the same data source table.

               The key match uses gCustomerID--a global field that needs to be updated by script for the current invoice. A script would use Set field to copy Invoices::_fkCustomerID into the global field via the OnRecordLoad trigger on the invoices layout and via the OnObjectSave trigger on the Invoices::_fkCustomerID field.

               For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

          • 2. Re: Customer price list
            davrap

                 Thanks, I think I am going to need a few days to decode this into something I understand!

                  

            • 3. Re: Customer price list
              starstuff

              Hi PhilModJunk!

              just found out about this post today, and like to understand it better.

              i understand the first part up to

              Products|Customer::__pkProductID = Pricing|Customer::_fkProductID
              Customer::__pkCustomerID = Pricing|Customer::_fkCustomerID
              Customer::__pkCustomerID = Invoices::_fkCustomerID
              Invoices::__pkInvoiceID = LineItems::_fkInvoiceID
              Products::__pkProductID = LineItems::_fkProductID

               

              the second part, not so much,

              LineItems::gCustomerID = Pricing|Invoice::_fkCustomerID AND
              LineItems::_fkProductID = Pricing|Invoice::_fkProductID

              could you explain why you use = Pricing|Invoice::_fkProductID instead of Product::_pkProductID ?

              is the Products::__pkProductID = LineItems::_fkProductID, not enough?

               

              A script would use Set field to copy Invoices::_fkCustomerID into the global field via the OnRecordLoad trigger on the invoices layout and via the OnObjectSave trigger on the Invoices::_fkCustomerID field.

              does this mean i will add

              OnRecordLoad

              OnObjectSave

               

              Script:

               Set Field(Invoices::_fkCustomerID)

               

              is that correct?

               

              Thanks again!