3 Replies Latest reply on Nov 19, 2014 7:26 AM by philmodjunk

    Customer Order Profile/History



      Customer Order Profile/History


      I wish to create an Order Profile for each individual customer in my database. The intention is to add and delete line items based on what each customer is ordering, change prices for future orders, as well as automatically record date of last order and update new items to the profile when a new sales order is made. 

      The end goal is to create a script that will allow the user to make a sales order from a portal in the customer form. This is ideal for our users as I will be able to format the portal similar to their current ordering format (excel). 

      I am trying to think of how this can be accomplished relationship and table wise but am having no luck with my attempts. Originally I thought to duplicate/mirror my line items table so that LI1 and LI2 were related to allow creation and deletion of records in LI2 but I couldn't seem to get that to work.

      Does anyone have any ideas? What am I missing here? 


        • 1. Re: Customer Order Profile/History

          There are parts of what you post that may be very clear to you but not to others. "add and delete line items based on what each customer is ordering" means what exactly? Any standard system of Invoices with a related table of line items lets you add and remove line items from the current order. But you seem to want something else? Perhaps a kind of "standard order" for each customer used as a "template" for each new order placed by that customer? There are several ways to do that, from duplicating the customer's last order with included line items to setting up a set of "template" records that are either manually updated by the user or based on prior ordering statistics.

          The typical set of tables and what I recommend as a starting point for what you want to do would be:

          Customers-----<Orders(Invoices)-----<LineItems (InvoiceData)>-----Products|Services|Labor

          To show the date of a customer's most recent order, this calculation field defined in Customers could be used:

          Last ( Orders::OrderDate )

          • 2. Re: Customer Order Profile/History

            "Setting up a set of "template" records that are either manually updated by the user or based on prior ordering statistics."

            This is what I am trying to describe. We have roughly 4000 product codes so it can become tedious searching for each item if a customer only orders 12-30 different products. I am trying to limit the list to a profile including only the products a customer buys. If a customer wants to buy a new item I hope to manually add it to the 'template'/'profile' or have it automatically added from the sales order. 

            I have a working traditional sales order process but I am struggling with my approach to accomplishing the template/profile. 

            • 3. Re: Customer Order Profile/History

              Given the relationships that I described earlier, you could put a portal to Products on the customer layout and it would list once, every product ever ordered by that customer. A button placed in the portal row could be clicked to select that product and copy it's ID to a new LineItems record linked to the newest invoice.

              A conditional value list could also list these same products.

              and if you set up these relationships:


              You could put a portal to Product2 on your Invoices layout or set up a conditional value list listing product IDs and descriptions from Products 2 on your invoices layout.

              The Names ending with "2" identify a second Tutorial: What are Table Occurrences? with the same data source table as the entity with the same name and no number.

              Caulkins Consulting, Home of Adventures In FileMaking