6 Replies Latest reply on Aug 10, 2011 9:14 AM by WilfriedSip

    using invoice , how do I select from multiple prices?

    unicorn

      Title

      using invoice , how do I select from multiple prices?

      Post

      Using filemaker pro11 and an absolute beginner.

      Basically I am trying to set up an invoicing system using the standard template and then modifying it.

      I have 4 types of customers and 4 different  prices for each product. I have inherited all the customers details in the form of a spreadsheet so I am able to import them into the customers section. The question is how do I first differentiate the customers into their various groups (radio buttons?) and then when making an invoice enable it to select the correct price to be used for the customer i.e retail price or trade price depending on the type of customer. Any help would be appreciated:smileyhappy:

        • 1. Re: using invoice , how do I select from multiple prices?
          philmodjunk

          I haven't played around with the invoicing template so you'll need to check my assumptions as we go...

           

          "how do I first differentiate the customers into their various groups"?

          You'll need a field in your customer table where you can assign a value that identifies the type of customer. You add this field first to the table in Manage | Database | Fields and then add it to any layouts where you need this. You can format the field to use a value list such as radio buttons if you want and this is a good way to reduce data-entry errors by limiting possible inputs to just a few choices.

           

          "...and then when making an invoice enable it to select the correct price..."

          I'm assuming that the customer category determines the specific unit price to be used for all the products listed in one invoice. You'll need a products table that lists ProductID, CustomerCategory and UnitPrice. Your template may have such a table where you can add a CustomerCategory field or you may need to define this table.

          Add this same field to your LineItems table. Now find your two tables in the Relationships Graph, (Manage | Database | Relationships) and set up a relationship like this:

           

          LineItems:: ProductID = Products:: ProductID AND

          LineItems::CustomerCategory = Products::CustomerCategory

           

          To do this, you can double click the relationship line if the tables already exist and are related. You can also drag from a field in one table's box to another to add a pair of fields to the relationship.

           

          Now you define LineItems::CustomerCategory to auto-enter the CustomerCategory field from your Customers table using the "looked up value" option.

           

          This can be a lt to take on for a beginner. Look up all the terms you can in filemaker help as you go and feel free to post back here with questions if you get stuck or can't get it to work.

          • 2. Re: using invoice , how do I select from multiple prices?
            comment_1

             


            unicorn wrote:

            I have 4 types of customers and 4 different  prices for each product.


            You should have a separate table for Prices, related to the Products table by ProductID. Then lookup the correct price from the Prices table, matching on ProductID and customer type.

             


            • 3. Re: using invoice , how do I select from multiple prices?
              philmodjunk

              comment wrote:

               

              You should have a separate table for Prices, related to the Products table by ProductID. Then lookup the correct price from the Prices table, matching on ProductID and customer type.

               


              Which is exactly what this species:

               

              LineItems:: ProductID = Products:: ProductID AND

              LineItems::CustomerCategory = Products::CustomerCategory

               


              • 4. Re: using invoice , how do I select from multiple prices?
                comment_1

                 


                PhilModJunk wrote:

                Which is exactly what this species:

                 

                LineItems:: ProductID = Products:: ProductID AND

                LineItems::CustomerCategory = Products::CustomerCategory

                 


                No - you are pointing to the Products table. The Products table should continue having ONE record for each product - with no price field.

                 

                • 5. Re: using invoice , how do I select from multiple prices?
                  philmodjunk

                  OK, Got it. Thanks for clarifying.

                  • 6. Re: using invoice , how do I select from multiple prices?
                    WilfriedSip

                    Please explain again, i am trying everything what i can with the description above but i only get 1 price.

                    For me the same i want to connect prices to customers how can i do that.

                    With the description above i can make it, i don't know much from FM i am doing for the first time.

                    For now everything is working accept the prices for costumers. I hope that you can help me with this.

                    Hope to hear from you soon.