1 2 Previous Next 27 Replies Latest reply on Jan 11, 2011 11:16 AM by philmodjunk

    Template for Price List

    sunwelding

      Title

      Template for Price List

      Post

      Hello,

      Does anyone know if there is a temple for a price list?  Or where I can get one?  Or how to create one? I would like to be able to enter material names, costs, dealer mark ups, retail mark ups.

      Thanks!

        • 1. Re: Template for Price List
          FentonJones

          In the FileMaker installation, in English Extras, Templates, Business - General, there is an Invoices.fp7 file. It has a Products table, with the usual fields. It does not have "dealer" or "retail" mark-ups. You'd have to create those.

          It does not have a Suppliers table (dealer). So you'd have to create that also, in order to do "dealer" mark-ups. But it's a start.

          • 2. Re: Template for Price List
            sunwelding

            I did see that.  My problem is for item I have 100 different part costs that are associated with that part.  Is there a way to modify the products list to have more parts?  I would need that to sum in the part cost.

            • 3. Re: Template for Price List
              philmodjunk

              Sounds like you need a related table where a single record in Products relates to many records in this "details" table so that you can document your "100 different part costs" for a given product. Exactly how you'd relate the two tables and how you'd structure this new table depends on info you haven't yet shared.

              • 4. Re: Template for Price List
                sunwelding

                I have 4 different layouts 1. Price List 2. Steel Cost 3. Material Cost 4. Misc Cost 5. Mark Up (which I think these fields should go on price list)

                I would like to have fields within the "price list" that will calculate retail cost, dealer cost, etc.  I think part of my problem is that the tables do not show they are relational.  Under tables the "occurrences in graph" show copies of some of the other tables.  Can that be messing it up.

                I am really not sure where to start so any suggestions would be helpful.  I attached a picture of the relationship chart to the first post.

                • 5. Re: Template for Price List
                  philmodjunk

                  How does that translate into "100 different part costs"?

                  What you describe sounds like a much smaller number of cost factors for any given product. Thus, you may be able to simply add more fields to the products table. Either approach, a related table or adding more fields can be made to work. Which is optimum requires knowing more about how you need to track and compute costs.

                  Example: I have product 1234, electric Widgit

                  If I need to track the costs you describe for Parts A, B and C that are assembled to produce product 1234, then a table of parts with cost fields makes sense.

                  If I don't care about the indivual part costs and only need to track the costs for the assembled item, then indiviudal cost fields can be added to the products table for a simpler design that will work just as well.

                  • 6. Re: Template for Price List
                    sunwelding

                    I have a product # 1234, widget to make the widget we need steel, lock, handle, paint, etc.  I want to make a price list that I can enter the current price of the steel, lock, handle, paint, etc. then take the total cost of the widget, have a price column for dealer and a price column for retail.  

                    In your example I need "to track the costs you describe for Parts A, B and C that are assembled to produce product 1234, then a table of parts with cost fields makes sense."  Each part that goes into product 1234 only has one price.  

                    • 7. Re: Template for Price List
                      philmodjunk

                      OK, then you'll likely need this structure to compute a total cost from the component costs:

                      Parts---<Part_Product>-----Product

                      Parts::PartID = Part_Product::PartID
                      Product::ProductID = Part_PRoduct::ProductID

                      You'd use a join table, Part_Product as a given part might be used in more than one product, but the costs at the part level will be the same.

                      Costs specific to a given part are stored in fields in the parts table.

                      The number of a given part needed to make a given product is stored in the Part_Product table.

                      Costs specific to the finished product, mark up and price are recorded in product table.

                      To compute a total part cost, you add calculation fields in Part_Product that multiply the cost in Parts by the part quantity. Then you define a calculation field in Product that uses Sum ( Part_Product::CostFieldCalc ) to compute the total for a given Product.

                      • 8. Re: Template for Price List
                        sunwelding

                        Parts---<Part_Product>-----Product

                        Parts::PartID = Part_Product::PartID
                        Product::ProductID = Part_PRoduct::ProductID

                        Are these all fields?  I am assuming parts and products are tables.  Can you clarify the definition of join table?  I have never used one before.  Do you just mean relationships?

                        • 9. Re: Template for Price List
                          philmodjunk

                          I'm describing three tables, Parts, Part_Product, Product

                          THe only fields are in the following two lines to the right of the ::

                          You can, of course, use your own field and table names.

                          A join table allows you link many different records in one table to many different records in another by serving as a "bridge" between the two tables. I'm assuming that any one part might be used in more than one product and that the cost data for one such part will be the same for all the products of which it is a part. (The reverse, any given product is made up of any number of parts, makes this a "many to many" relationship.)

                          Often, a portal to the join table is used to list the matching records from the third table. In your case, a portal to Part_Product can be placed on a Product layout to list all the parts records that link to it. A portal to Part_product on the Parts layout would then list all the Products to which that part record is linked. In your case, you can use these relationships to combine values from individual part records to compute a total cost in the Products table for that Product.

                          A join table may or may not be needed in your system. It depends on how you work with your part's cost data. If the cost data for any given part is different for every product it is part of, or none of your products have parts in common, you don't need the join table and you can link the records in parts by productID.

                          • 10. Re: Template for Price List
                            sunwelding

                            Ok, I made three tables as described above.  This might be a silly questions but do I can create a join table?  You are correct any one part might be used in more than one product and that the cost data for one such part will be the same for all the products of which it is a part.

                            In my products they basically have all the same parts just different quantities.  Some parts may be different throughout the models.  

                            I have never made a portal. I see how to make one.  It creates a box on that layout.  Let's say in the Product layout I create a portal.  I select PartID and ProductID.  It doesn't let me ad information into the portal.  Or is this just a box to see information as it is entered into fields?

                            • 11. Re: Template for Price List
                              philmodjunk

                              Please read up on Portals in FileMaker help. It's an extremely useful tool that can be used in many different ways in Filemaker.

                              When working with Manage | database, a join table is created/defined like you would any other table in FileMaker.

                              I've kept things fairly general, because a lot of details depend on what you need.

                              Let's assume you defined these three fields in Part_Product:

                              ProductID
                              PartID
                              Qty (set an auto-enter value of 1 so that this field always is at least 1)

                              You can add others later to compute cost times quantity for each part.

                              In manage | database | Relationships, double click the line from Products to Part_Product so that you can select "Allow creation of records via this relationship" for the Part_Product table. This enables you to add new Part_Product records in the portal by simply entering data in the bottom blank row of the portal.

                              I suggest that you also enable the delete option for Parts, but do not under any circumstances select the delete option for Products in this relationship or deleting a part_Product record will also delete the related Product record.

                              On your products layout, you'd add a portal to Part_Product and place the PartID and Qty fields from Part_Product in it. You can then add a description field and any other fields from Parts that you want to this same portal. Once you've entered a part ID, these fields will display data from the specified part record and you can even edit data in these fields if you want to. (Such edits will affect all Products that list that part, so be careful.) You do not need to add the ProductID field as Filemaker will fill in that field for you each time you add a record in the portal.

                              The PartID field can be formatted with a drop down list or pop up menu that lists the PartIDs from Parts in column 1 and the part description or name in column 2 to make it easier to select the desired part.

                              Note: this method assumes that all needed part records have already been created. From where we are at this moment, that means that new parts would first be added via a layout to the part table before you'd use this portal to link that record to a given product. Once you get this all working, you can investigate options for making it possible to add new parts from that same products layou so that you don't have to change layouts each time you need to add a part.

                              • 12. Re: Template for Price List
                                sunwelding

                                Ok, I read about the portals.  I have a better understand of them.  I used the purchase order template and didn't know that was a portal.

                                 I have some questions.  On my part layout should I not also add the description and costs?  I then assume I can make that relationship to the Part_Product layout.  I understand that the parts must be entered before I can use the drop down list. Is there a way to have the cost and description filled in automatically when I enter the partID?  I think this is what you are describing in this sentence "The PartID field can be formatted with a drop down list or pop up menu that lists the PartIDs from Parts in column 1 and the part description or name in column 2 to make it easier to select the desired part."  I do not understand where the columns come from.  

                                How can I add fields into the portal after the portal has been created?  

                                • 13. Re: Template for Price List
                                  philmodjunk

                                  Part based costs and the description should be entered in fields defined as part of the parts table. And yes that means that such fields should be visible on the parts layout so that you can edit them.

                                  What I am referring to with "column 1" and "column2" is a value list that displays two columns of data when you drop down the list. When you use the specify field option to define a value list, you'll find you have two boxes for field 1 and field 2 where you can select fields from which to draw the values used in the value list. These correspond to the column 1 and column 2 that I mentioned. Column 1 should list the values entered into the field when a value is selected. Column 2 should be a field that helps the user make the correct selection.

                                  How can I add fields into the portal after the portal has been created?

                                  You can add fields to a portal much like you add fields to any other part of your layout. You just have to increase the size of your portal row to make room for the field first.

                                  1. Click on the portal (not a field in the portal) while in layout mode.
                                  2. Drag one of the black selection boxes that appears to re-size the portal row. You can make a portal row taller or wider or both.
                                  3. Use the field tool to then add a new field to the portal row.
                                  4. The specify field dialog will pop up when you release the mouse button. Make sure you select the field from the correct table occurrence (controlled by a drop down at the top of the portal) or you won't get the results you want. In this case you want to select a field from the table occurrence for your related parts table.
                                  5. The new field must be completely within the borders of the portal. You can use the arrow keys to "nudge" a selected layout object around one pixel at a time if you need to.
                                  • 14. Re: Template for Price List
                                    sunwelding

                                    I must have missed something.  In my layout labeled parts I have fields PartID, Part_Cost, and Part_Description.  In my Part_Product layout I have fields ProductID, PartID, Qty, Part_Cost, and Description.  I have entered all of my parts into my Parts layout.  When I go to the Part_Product layout and enter my model ABC into ProductID field and then enter my PartID, is there a way for it to fill in the Part_cost and Description from the Part layout? and also into the Product layout for that ProductID?

                                    1 2 Previous Next