3 Replies Latest reply on Apr 29, 2012 7:47 PM by rounakjain

    invoice project for customer wise prices

    rounakjain

      Title

      invoice project for customer wise prices

      Post

      I have to create invoices using FileMaker Pro.

      The invoice will have the following fields for the user to enter:

      1. Invoice No

      2. Date

      3. Customer Name

      4. Item Name

      5. Item Qty

      6. Item Rate

      Then there will be calculation based fields for aggregating Item Qty* Item Rate.

      I want FileMaker Pro to auto-retrieve the latest(date-wise) price at which the current Item is being sold to the current Customer. If the current item has not been sold before to the current Customer, then it must not show anything.

       

       

      How many tables do I need and with what fields? What relationships must I establish?

      (I know about Table Occurrences and such other basic stuff. I need the structure of Tables and Relationships)

      Thanks

       

        • 1. Re: invoice project for customer wise prices
          PatrickSmit

          Hi Rounak Jain,

           

          From what you are describing I come up with 4 different tables.

          a. Invoice.......................one to many relationship to LineItems

          .... 1. Invoice No

          .... 2. Date (Invoice)

          .... 3. Customer Name (Number)

          b. LineItems

          .... 4. Item Name (Number)

          .... 5. Item Qty

          .... 6. Item Rate (By using a lookup)

          .... 7. Extended Price (Qty * Rate)

          c. Product.........................one to many relationship to LineItems

          .... 8. Item Number

          .... 9. Item Name

          .... 10. Item Rate

          d. Customer.....................one to many relationship to Invoice

          .... 11. Customer Number

          .... 12. Customer Name

          .... 13. Customer Address

           

          How many fields you really need is dificult to tell. Think of every relevant information.

          For instance if your customer has a billing address which divers from the delivery address, then that is relevant to track.

          If the current item has not been sold before to the current Customer, then it must not show anything.

          I can not figure out what you mean with this. To me it seems you are not selling new item to customers.

          An other thing which you can do is purchase yourself "The Missing Manual" and/or the "Filemaker Training Series" if you haven't done this already. Please don't be offended. Not my intensions. But these are really nice peaces of work. The part about ERD  in the FTS you should definitely pay attention to.

          • 2. Re: invoice project for customer wise prices
            rounakjain

            I have spent my Sunday learning. I have reached close (atleast I think).

            I have started with the Invoices Starter Solution. I have added a Table Occurence for Products "Products 2". I have created a join table "CustomerProduct". It has four fields CustomerID, Product Name, CustomerProduct ID and Price. In the Customer Table, I have added a Portal for CustomerProduct with the fields Product Name and Price. I entered some products and prices in the portal. Then I checked CustomerProduct table and I could see the desired result. Now, I have a customer-wise distinct price for every distinct product in the CustomerProduct Table.
            In the Invoices Details Table, there is a "Unit Price" field. I want it to retrieve the Price from the CustomerProductTable. What Formula should I enter in the Calculate Field or how should I use the LookUp field? Kindly help me. Here are some images:
            http://imageshack.us/photo/my-images...tiontable.png/
            http://imageshack.us/photo/my-images...ttablevie.png/

            (crossPost:http://www.filemakertoday.com/com/showthread.php/30300-help-with-many-to-many?p=123148&highlight=#post123148)

            • 3. Re: invoice project for customer wise prices
              rounakjain

               I am now able to retrieve the prices as I wanted in the post above. I added a field "Customer ID" in the table "Invoice Data". I specified its calculated value to $$CURRENT_CUSTOMER_ID.

              I added a Table Occurrence of Invoice Data "Invoice Data 2". I set up two relationships;

              InvoiceData2::CustomerID-CustomerProduct::CustomerID

              and

              InvoiceData2::Item-CustomerProduct::ProductID (ProductID actually contains item name. Sorry!)

              Then, I set up lookup for unit price as:

              Starting with Table: Invoice Data 2

              Lookup from Related Table: CustomerProduct

              Copy value from field: Price