6 Replies Latest reply on Jun 2, 2016 1:58 AM by erolst

    One item with different price & cost ????

    Angelus81

      I want to create an inventory database that contains the following fields:

       

      item name, item description, cost, and price.

       

      However, I am kinda a stuck because my price maybe varied by different customers. In this case, how should I create my database?

       

      Can someone please help?

        • 1. Re: One item with different price & cost ????
          taylorsharpe

          Create a related table that identifies the cost by customer.  That way the price field will be based on the customer.  You'll have to have some base price for items not assigned to a customer.  But in Inventory, Price will be a related field of a calculation based on the relationship. 

           

          This is basically what relational databases is about.  It is the same thing as having a person who has multiple phone numbers.  You have a person (or contacts) table and a related phones table where you can give a person as many phone numbers as they have.  Hope that helps. 

          1 of 1 people found this helpful
          • 2. Re: One item with different price & cost ????
            erolst

            Angelus81 wrote:

            I want to create an inventory database that contains the following fields:

            item name, item description, cost, and price.

            Do not forget to define a serial key that serves as the primary key (unique identifier).

             

            Create such a field in every table.

            1 of 1 people found this helpful
            • 3. Re: One item with different price & cost ????
              Angelus81

              I am kind of new in FileMaker and only understand the basic relationship.

               

              Could you please give me an example?

              • 4. Re: One item with different price & cost ????
                erolst

                Assuming that you have the standard structure of:

                 

                Customer --< Order --< LineItem >-- Product

                 

                you will now need

                 

                Customer --< Order --< LineItem >-- Product --< CustomerPrice

                 

                Create a new table CustomerPrice (call it whatever you want). It will need these fields:

                 

                • its own primary key, auto-entered serial

                • a foreign key for product (same data type as your Product primary key)

                • a foreign key for customer (same data type as your Customer primary key)

                • a price (that's what it's all about …)

                 

                Relate it to the Product table by Product::primaryKey = CustomerPrice::productForeignKey

                 

                For now, make sure that the CustomerPrice side allows creation of related records.

                 

                Create a value list for Customers, using Fields from table, 1. field: Customer::primaryKey, 2. field: Customer::name (or some other human readable datum), show only second field

                 

                Display a portal on the product layout, with fields for customerForeignKey and price. Format the foreign key field as popup and attach the new value.

                 

                Now you can create a custom price per customers, simply by selecting a customer from the popup and entering a price. To apply those prices:

                 

                • create a calculation field in your LineItems table – say, cCustomerID – defined as Order::customerForeignKey

                • create a new table occurrence of your CustomerPrice table; call it CustomerPrice__forLineItem

                • relate it to the line items table via

                  LineItems::productForeignKey = CustomerPrice__forLineItem::productForeignKey

                  LineItems::cCustomerID = CustomerPrice__forLineItem::customerForeignKey

                 

                For your LineItems::price field, create an auto-enter calculation (or modify the one you should already have):

                 

                Case (

                  not IsEmpty ( CustomerPrice__forLineItem::price ) ; // if this product/customer combo exists AND has a price

                  CustomerPrice__forLineItem::price ; // use that price

                  Product::price // else take the standard price from the Product table

                )

                 

                This may look daunting, but if you implement it step by step, it shouldn't be that difficult.

                1 of 1 people found this helpful
                • 5. Re: One item with different price & cost ????
                  Angelus81

                  Hello erolst,

                   

                  Thank you for your detailed guideline for my question. However, after I finished every steps that you instructed and entered a item on the OrderLineItem portal, but it won't show the customer's price. Instead, it shows the item's standard price.

                   

                  Below are the images for my settings. could you please check where I did wrong?

                   

                  1. This is the relationship chart

                  Screenshot 2016-06-02 14.02.21.png

                   

                  2. Here are the fields for the OrderLineitem

                  Screenshot 2016-06-02 14.04.08.png

                   

                  3. This is the relationship between CustomerPrice_for OrderLineItem & OrderLineItem

                  Screenshot 2016-06-02 14.02.37.png

                  4. Here are the browser view for both Order and Inventory (Product) tables

                   

                  Screenshot 2016-06-02 14.22.05.png

                   

                   

                  Thank you and sorry for the late response as I just came back from a business trip!!!!

                  • 6. Re: One item with different price & cost ????
                    erolst

                    Your cCustomerID calc field in LineItems is wrong; the field reference to Customer is enclosed in quotes, so it won't be resolved (because it is regarded by FM as just a dumb piece of text).

                     

                    Remove the quotes, and try again.