3 Replies Latest reply on Nov 7, 2013 9:41 AM by philmodjunk

    apparel inventory price history help

    sveta

      Title

      apparel inventory price history help

      Post

           Hi all,

           I have an Inventory DB for various apparel (mostly shirts and sweaters) a business makes. The file had been set-up prior to my employment, but now a "Price History" component is necessary for the business. Basically, we need to keep track of how much different vendors charge for blank &embellished shirts. The variables include: apparel size (ranging from XS-3XL), apparel type (blank or embellished), vendor name, price for every individual size, and the date the price was given. For embellishment vendors, we need to keep track of how much each embellishment type will cost (there is already a field for embellishment type). In addition, vendors may vary for per size for blank apparel.

           I'm not sure how exactly to go about this. At first a portal came to mind, but I'm not sure if I should create a new table or add all these fields to the current table I have. I started creating the fields and a red flag went off once I realized how many variations of "BlankCost_sm_date," etc. fields I'd have.

           I'd be really grateful if someone could explain what my options are and/or how I should proceed. I'd also appreciate just a link to another post on these forums that may be relevant and helpful. I'm sure I've missed some! I'm still a bit new to Filemaker and am still somewhat of a database novice.

            

           Thank you in advance

        • 1. Re: apparel inventory price history help
          philmodjunk

               You'll need to describe the current design of your table first. What does one record in the main table represent? Do you have a table of Vendors?

          • 2. Re: apparel inventory price history help
            sveta

                 Of course! Apologies.

                 There is only one table that consists of info such as the style numbers (our style # and the blank style #), material content, embellishment techniques, # of colors used, division (men's, jrs, etc), and license. There is no table for Vendors, although there was already a field for Blank Vendor and for Embellishment Vendor. At the time the DB was first developed, the business was dealing with only one vendor for blank apparel and one vendor for embellished apparel; however, as the business has grown and changed, so has the amount of vendors it deals with. Now, for example, one t-shirt may have one vendor for a blank small t-shirt, another vendor for blank medium- extra large t-shirts, and yet another vendor for blank 2XL and 3XL t-shirts. There is also a seperate vendor that does embellishment, but there really may be SEVERAL different vendors that each do one type of embellishment.

                  I'd like to include something like a portal or tab perhaps added to the layout where the user may input price and date of price per size (for blank AND embellished apparel) and the vendor name. 

                 Did that properly answer your question?

            • 3. Re: apparel inventory price history help
              philmodjunk

                   It sounds like you need to give significant thought to how you might restructure your database as part of gaining the add features that you want.

                   To start, a table for vendors would seem to be in order and another table for prices would make sense.

                   But even your current table sounds like something that should be divided into at least two, if not more tables. A Shirts table use a self join relationship to specify the "blank" shirts used and a related table might list all other details used to embellish that specific shirt.

                   Your relationships might look like:

              Vendors|Blanks----<PricingHistory|Blanks>------Shirts-----<PricingHistory|embellishments>------Vendors|Embellishments

                   Entities with the same color might be Tutorial: What are Table Occurrences? with the same data source table.

                   The pricing history records can include a date field and the relationship can sort by date so that the most recent price for a blank of a specific size supplied by a specific vendor is listed as the first related record.