11 Replies Latest reply on Aug 14, 2011 2:21 AM by WilfriedSip

    Different Customer different prices. Invoice Template

    WilfriedSip

      Title

      Different Customer different prices. Invoice Template

      Post

      Hello i am new into the FM and we are thinking about the buy the FM now we are using a Demo to see how it works.

      We have a problem with the prices and the tax.

      Different customers have different prices,

      Example:

      Customer 1 has a price of 10, Customer 2 has a price of 20, etc etc etc.

      We changed the Invoice template because we want to use this one, it is the meaning that if we make an invoice that the price is calculated by customer name.

      Now i found on the form http://forums.filemaker.com/posts/fb087e9b32 but maybe i do it wrong but i read it 1000 times and i can't get it to work. This function is very important to us so i hope that you can help me.

        • 1. Re: Different Customer different prices. Invoice Template
          philmodjunk

          Doing this by customer name is not a good idea. Customers can have the same name and sometimes they change their names. This is true for both personal names and the names of companies.

          Do customer's fall into separate categories and each category specifies a price? Or will each customer get their own unique prices for each and every item that you sell. (I would guess you have sales categories like retail/wholesale, or new customer/repeat customer/preferred customer, but would llke to confirm that this is the case here.)

          • 2. Re: Different Customer different prices. Invoice Template
            WilfriedSip

            I understand what you say, then it maybe possible to connect it by customer id.

            Or if you have have something please tell me.

            We don't have category, by us it has to do by customer. who it is its payment record and how much he ordered in history.

            • 3. Re: Different Customer different prices. Invoice Template
              philmodjunk

              I can see how a discount or premium rate could be recorded in the customer table. This rate could then be used in calculations to modify the calculated cost of each item listed in the Invoice's line item portal. This applies the same specified price adjustment to all items the customer orders so this may not be what you need.

              If you have a different specified price for each customer for each item they purchase, you'll need a join table between Customer and products that functions much like the line items table where each record in it records the customerID, the ProductID and the price.

              Customer---<Customer_Product>----ProductsForCustomer

              Customer::CustomerID = Customer_Product::CustomerID

              ProductsForCustomer::ProductID = Customer_Product::ProductID

              ProductsForCustomer is a new occurrence of products produced by using the button with two plus signs to duplicate the existing table occurrence for Products.

              • 4. Re: Different Customer different prices. Invoice Template
                WilfriedSip

                Sorry to tell but i don't understand it complete.

                I know that you mean that i must make a join table between Customer and Product but the lines u don't understand must i make new fields?

                Table Customer---Name <Customer_Product>----description ProductsForCustomer

                Customer::CustomerID = Customer_Product::CustomerID

                ProductsForCustomer::ProductID = Customer_Product::ProductID

                Please explain don't know.

                • 5. Re: Different Customer different prices. Invoice Template
                  philmodjunk

                  Can you confirm that you actually need this approach?

                  What I am describing requires signficant changes to the design of this starter solution. And this is just the very first part of the needed changes.

                  It describes three table occurrences, Customer, Customer_Product, and PRoductsForCustomer. These are not new fields. They are boxes you create (or which already exist) in manage | Database | relationships. The lines identify the type of relationships (---< means one to many). The text below that spells out the specific fields to use in setting up these relationships.

                  You'll need to read this tutorial to understand better what I mean by a "table occurrence" in Filemaker databases:  Tutorial: What are Table Occurrences?

                  • 6. Re: Different Customer different prices. Invoice Template
                    WilfriedSip

                    Honest i don't know if this is what i mean.

                    I just searching for that i can fill in different prices for 1 item, and that if i make an invoice that the invoice know the price for that item.

                    So thats why i thought maybe i can connect that to customer.

                    Is there not a option that i can make a radio button by customer with the text High, Middle, Low. Example.

                    Then make in the products 3 prices, high, middle, low. 

                    And when i make a invoice i choice the customer and then the products. so maybe connect it on Customer ID or something.

                     

                    • 7. Re: Different Customer different prices. Invoice Template
                      philmodjunk

                      If all items purchased by a given customer will be "high", "Middle" or "low", then yes, you do not need the extra table and relationships I was describing. You can add three fields to Products to record the "high" , "Middle" , and "Low" price for each item. You can define matching fields in LineItems to look up all three prices in every line item record. Then you can change the cost calculation field for this record to:

                      Case ( Customer::priceCategory = "High" ; HighUnitPrice ;
                                 Customer::PriceCategory = "Middle" ; MiddleUnitPrice ;
                                Customer::PriceCategory = "Low" ; LowUnitPrice ;
                                HighUnitPrice ) * Quantity

                      The last field name inside the parenthesis is the value returned by the calculation if PriceCategory does not equal "High", "Middle" , or "Low".

                      Note: this is the simplest way I can think of to set this up. There are more sophisticated methods--including using a related table for product prices so that it is easier to add more prices for a given product if you decide that three pricing categories isn't enough and you want to add a fourth or even more.

                      • 8. Re: Different Customer different prices. Invoice Template
                        WilfriedSip

                        Thank you very much for explaining this to me.

                        But this is what i did,

                        I made 3 fields in the product table, High, Middle, Low.

                        I made 3 fields in the Line Item Table, High, Middle and low, they have a lookup value to the product table High Middle Low.

                        Inside the product page i made those 3 fields where i can put in the amount of money same as the normal one like price. 

                        In the Customer table i made a field PriceCategory.

                        Then in the Customer page i made a field that is connected to the PriceCategory, with a style of Radiobuttons. and the value is Custom with the text High,Middle,Low. Hope what i did is good.

                        But then i am stuck i don't know what you mean with the cost calculation. On this moment the invoice has Line Items::Price

                        Please explain me one more time how i must made that calculation that the invoice know the customer but also the right price. Don't know where to fill in that cost calculation.

                        Sorry that i ask you again.

                        • 9. Re: Different Customer different prices. Invoice Template
                          WilfriedSip

                          Now i put this calculation in the Item Table under prices.

                          Case ( Customers::PriceCategory = "High" ; Price High ;

                                   Customers::PriceCategory = "Middle" ; Price Middle ;

                                   Customers::PriceCategory = "Low" ; Price Low ;

                                   Price ) * Quantity

                           

                          Now in my invoices it will find a price but only the high price for every customer the same.

                          Something is not going good i suppose.

                          • 10. Re: Different Customer different prices. Invoice Template
                            WilfriedSip

                            I tried different things with the calculation but nothing works.

                            If i change the last calc. Low Price ) * Quantity then he calculate from that.

                            So i believe that he can't find the "High", "Middle" and "Low" and then he will get directly to the last. HighPrice ) * Quantity

                            I also made a relationship between the customer PriceCategory and Bill to Customer ID other wise i could not make the calculation.

                            But what i try he will not find the PriceCategory "high" "Middle" "Low"

                            Please tell me when you have time what did i forget.

                            • 11. Re: Different Customer different prices. Invoice Template
                              WilfriedSip

                              Thanks for all the help.

                              It is working now i forgot the relation between the PriceCategory.

                              The only thing now is the tax rules hope you can assist with that.

                              Thanks again.