5 Replies Latest reply on May 17, 2013 9:42 PM by philmodjunk

    Help populating purchase form line items

    RebeccaWilson

      Title

      Help populating purchase form line items

      Post

           Okay, thanks in advance for the help!

           Here's what I'm working with and trying to wrap my head around. First off, I have quite a few years of database design experience but am a Filemaker newbie. We have a company that I am trying to streamline our data entry system using FileMaker Pro 12. The way it works is somewhat like this: We have Clients who will carry only certain items we sell. They might order items every week, but we only bill them once a month. Those "purchase orders" we call "Service Forms". So a CUSTOMER may have many SERVICE FORMS, and an INVOICE may be based on the info in many SERVICE FORMS.

           Where I'm confused:

           1. How do I go about displaying in a layout ONLY the items each customer can order, with the information? I have a join table for Customer Products, it's just that the logic of this is eluding me. Please see the attached ERD sample for my tables and relationships.

           2. Also, will I need more than one instance of a particular table? If someone sees a better way to structure this, I'm open to suggestions!

           Thanks everyone for any suggestions you might have to get this working!

      Sample_ERD2.jpg

        • 1. Re: Help populating purchase form line items
          philmodjunk

               how do you want that display to look?

               You could display them in a portal on a Customers layout.

               You can perform a find on CustomerPRoducts, specifying the customer ID in your find criteria and list them on that layout in a list or table view.

               A conditonal value list can list all the products for that customer in a drop down list or other value list based field format.

          • 2. Re: Help populating purchase form line items
            RebeccaWilson

                 Hi Phil,

                 I assumed it would be a portal on Customers, it's just pulling the data the right way that is eluding me. I need it to display all the Products the Customer carries, and also be able to add in quantities and take a count of what they have (basically create a new Service Form, showing the pars for each product, quatities they've received, what they had before and being able to add on as needed). So it would be pulling from the Customers table, the Customer Products table and the most recent (last) Service Form Line Items table that are relevant to that customer.

                 If I were to put it into columns, it would go something like this:

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 
                                Par                     Prev Count                     Current Count                     No. Replaced                     Product Name                     Final Count
                                10                     10                     5                     5                     Product 1                     10
                                12                     8                     0                     12                     Product 2                     12
                                25                     32                     30                     0                     Product 3                     30
                                18                     10                     8                     10                     Product 4                     18

                 Par is pulled from the Customer Products table. Previous Count needs to pull values from the last (most recent) Final Count (Service Form Line Items). Current Count needs to be user optional (we don't always do a formal count mid-month); it could also pull values from the Previous Count (so again referencing the most recent Final Count). Final Count will be a calculation of Current Count + Number Replaced.

                 Thanks!

            • 3. Re: Help populating purchase form line items
              philmodjunk

                   Start by putting a portal to Products on your Customer layout. See if that correctly displays just the porducts you've assigned to that customer record. If not, check your data and relationships as this should list all the products.

                   But you will not be able to enter order information into this portal as it is displaying records from Products, not a Table Occurence of ServiceFormLineItems.

                   There are several approaches possible here and one key design issue that I see is how to generate the needed records for a new customer order given your weekly orders-->monthly billing business model.

                   I am assuming that each customer order generates a new ServiceForm Record with a new set of related line items records.

                   The basic setup that I am imagining here is that each time a "new order" button is clicked on the Customers layout, the previous order disappears from the portal. From there, one of two things can happen, the script can loop through the list of Products records linked to that customer and generate one new line item record for each product, or you can use that first portal I described as a click list where clicking a button in the portal row adds a line item just for that product. Either method works and they aren't mutually exclusive--for customers that usually place "sparse" orders you can use the second method and customers that usually order nearly every product, you can use the first method.

                   The key is in the relationships needed to pull this off:

                   Ord_Customers------Ord_ServiceForms-----<Ord_ServiceFormLineItems>------Ord_Products

                   Ord_Customers::_fk_CurrentServiceFormID = Ord_ServiceForms::__pk_ServiceFormID
                   Ord_ServiceForms::__pk_ServiceFormID = Ord_ServiceFormLineItems::_fk_ServiceFormID
                   Ord_ServiceFormLineItems::_fk_ProductID = Ord_Products::__pk_ProductID

                   For an explanation of the notation that I am using, see the first post of: Common Forum Relationship and Field Notations Explained

                   The Data Source table of each of these Tutorial: What are Table Occurrences? is shown as the name to the right of Ord_ in this example. These would be in addition to the Join table based relationship needed to get the group of products assigned to a given customer.

                   And if you are not already familiar with it, look up The Complete Go To Related Record in FileMaker help as it can be used to pull up a found set of the Products records assigned to a given customer. That can then be used in a script that loops through them and creates a line items record for each such product.

              • 4. Re: Help populating purchase form line items
                RebeccaWilson

                     Phil,

                     Thanks for your detailed response, and the links. I've been playing with it and haven't gotten it totally right just yet, but I think your suggestions will really help, and I took a few days off from the project due to frustration. I've started from scratch so many times, building it up bit by bit until I break it. sad

                     To clarify things a bit:

                     

                          I am assuming that each customer order generates a new ServiceForm Record with a new set of related line items records.

                     Absolutely correct. I assume this will work, considering it is just an extension of Invoices & Invoice Line Items, sort of.... Since I tend to think in terms of sentences (explaining things to people who have no clue how the nuts and bolts of databases work), I think of it like this: One customer may have many Service Forms (with many items in the Service Form Line Items table), and an invoice can contain information from many Service Forms (pulling from the related SFLI table).

                     

                          The basic setup that I am imagining here is that each time a "new order" button is clicked on the Customers layout, the previous order disappears from the portal. From there, one of two things can happen, the script can loop through the list of Products records linked to that customer and generate one new line item record for each product....

                     This is what I need it to do (although that button will be "New Service Form"). We need every item listed, regardless if the Number Replaced is zero. (I'd also like the Number Replaced value to replace any null entries with a zero for those that are lazy with inputting, I assume there is a very simple way to do that, I just haven't gotten far enough yet to even think about looking that up.)

                     Thank you again for your response, you've clarified things well enough for me to start tinkering again!

                • 5. Re: Help populating purchase form line items
                  philmodjunk

                       In Field Options, there's a box named "Data" where you can enter a 0 and then that field will automatically store a zero in the field each time a new record is created.