1 2 Previous Next 25 Replies Latest reply on Nov 8, 2013 11:15 AM by philmodjunk

    Help needed with multiple product categories and cost calculation

    JoRo

      Title

      Help needed with multiple product categories and cost calculation

      Post

           I'm trying to update an old database that uses 465 fields in one table to record orders and calculate cost. It is a mess to say the least. I tried to replicate two examples in the forum of multiple product categories using conditional value lists. I haven't been able to get either of them to work. I'm getting completely lost in the multiple table occurrences and value list settings.

           I'm using the FM12 Invoice Starter Solution as my starting point.

           Here is my scenario:

           • We have training manuals available in 5 topics

           • Each topic is available in at least 2 languages but as many as 10.

           • All manuals in the various languages are available in three formats: print, PDF on CD, PDF download.

           The different combinations available end up creating a list of around 66 individual item IDs. Each item ID has a different cost and price.

            

           I'd like to be able to simplify data entry by providing drop down lists for the three categories. I need to also  calculate total cost of goods sold by time period, count of items by topic, language, and format behind the scenes.

            

           I've tried to replicate a couple examples in the forum of conditional value lists using categories, sub-categories, and sub-sub categories. However, I haven't been able to get it to work.  1) I'm getting completely lost in the multiple table occurrences required and figuring out which fields refer to which table and are included in which value list. 2) In the examples I've seen, the sub categories are unique to each category and the sub-sub categories are unique to each sub-category. For example the trail goes something like this:

           Category --> Sub-category --> Sub-sub category  --> choose specific item

           electronics --> computers --> mac  -->  Mac PowerBook Pro

           clothing --> sweaters --> kashmir --> Kashmir sweater 1

           My "line" is not that clear. Does that make sense? For example, three different items look like this:

           business --> Russian --> CD   -->  Russian business manual on CD  (cost: 2.00 price: 10.00)

           business --> Russian --> Print  -->  Russian business manual in Print  (cost: 6.00 price: 10.00)

           design --> English --> Print  -->  English design manual in Print  (cost: 12.00 price: 20.00)

            

           I'm just not sure what the best way is go about this would be. Any help offered will be much appreciated!!

            

            

            

        • 1. Re: Help needed with multiple product categories and cost calculation
          philmodjunk
               

                    I'd like to be able to simplify data entry by providing drop down lists for the three categories. I need to also  calculate total cost of goods sold by time period, count of items by topic, language, and format behind the scenes.

               That's enough for two very different threads here! Let's get your conditional value lists working first, and then look at your cost accounting issues--which you may want to post as a question in a new thread, but maybe this tutorial on summary reports will help you in that part of your project: Creating Filemaker Pro summary reports--Tutorial

               For those following along at home, I'd assume that one of the threads that you read was this one:

               Hierarchical Conditional Value lists: Conditional Value List Question

               The fact that each category is not unique does complicate the issue a bit, but with multiple match fields, it should be manageable.

               The starter solution has these tables/relationships:

               Invoices----<Invoice Data>-----Products

               This is still the case in your project?

               And how did you assign each product to the categories and sub categories? Did you add 3 fields to Projects for the category, Sub Category and Sub Sub Category?

               Do you have thee added tables of categories, sub categories and sub sub categories?

          • 2. Re: Help needed with multiple product categories and cost calculation
            JoRo

                 Thank you for responding!

                 Yes, I have the three Starter Solution basic tables set up: Invoices, Invoice Data, and Products.

                 Originally, I had only the format and language set up as categories. I was hoping to simplify the process and eliminate the sub-sub category. However, I went ahead and changed/added "topic" as "category", language as "sub-category" and format and "sub-sub category."

                 Yes, I have three fields in the Product table for category (topic), sub-category (language), and sub-sub category (format) and I have set up the three tables for category, sub category and sub-sub category.

                 The relationship graph I have posted includes the work I did so far to set up my value lists. I haven't gone through the process of setting up the extra tables that would be needed for a subsub category yet. I want to make sure that is needed before I go on, as this was the part that was confusing me the most originally. I'd be happy to delete it all (pink and purple tables) if they are not needed.

                  

            • 3. Re: Help needed with multiple product categories and cost calculation
              JoRo

                   So I have my five topics set up as the categories. The table I have set up currently, wants me to assign the sub-category to a particular category. Is that the right way to proceed?

                   In other words, if the subcategory is language, category 1 would have two sub categories: English and Russian. Category 2 would have 11: English, Bulgarian, Chinese 1, Chinese 2, Hindi, Korean, Malayalam, Romanian, Russian, Spanish, and Multi-language. Category 3 would have 11 and so on. I would end up with 35 subcategories with unique ID numbers. Is that what I want to do? Or do I want each subcategory to represent 1 language option (which would be only 14 subcategories)?

              • 4. Re: Help needed with multiple product categories and cost calculation
                philmodjunk

                     So the user selects a value in Invoice data::item category ID match field to get a list of SubCategories in Invoice Data::item sub_category ID match field. Selecting a value in item sub_category ID match field then produces a list in item Subsub_category ID match field. Selecting the SubSub category then produces a list of ProductID's.

                     Correct? I will assume your answer is yes to set up the following relationships and conditional value lists. My answer will not explicitly refer to actual table occurrences in your relationship graph but will use names that match as closely as possible to their data source names except for one specified for products. You'll need to modify the names I used as needed when following these instructions.

                     Invoice Data::item category ID match field is the easiest. You just need a value list that lists all values from the category table. Product CategoryID would be listed in field 1 and Product Category would be listed for field 2.

                     But now we can use this relationship:

                Invoice Data::item category ID match field = Product Sub_Category::Product Category ID Match field

                     for our first conditional value list which will be used with the Invoice Data::item sub_category ID match field. Set up this value list to list values from Product Sub_Category, include only related values starting from Invoice Data. Text in blue here are names for the exact same table occurrence box in your relationship graph. If the structure of your graph requires a different name than Invoice Data for the table occurrence, be sure to select this exact same name in the "starting from" drop down.

                     Now you can add a relationship for the next conditional value list in the hierarchy:

                     Invoice Data::item sub_category ID match field = Product Sub_category::Product Sub_category ID

                     And the next conditional value list will have the same "starting from" designation but list values from Product Sub_category

                     Repeat this process for subsub_Category ID match field.

                     But since some of these values are not specific to the category that is "one level up" in the hierarchy, lets specify a different relationship for the final conditional value list:

                     Invoice Data::category ID match field = Products|Conditional::Category ID Match Field AND
                     Invoice Data::Sub_category ID match field = Products|Conditional::SubCategory ID Match Field AND
                     Invoice Data::Supsub_category ID match field = Products|Conditional::SubSubCategory ID Match Field

                     And now your final conditional value list can list product ID's from Products|Conditional and yet again specify Invoice Data as the "starting from" Tutorial: What are Table Occurrences?.

                      

                • 5. Re: Help needed with multiple product categories and cost calculation
                  JoRo

                       So if I am reading your explanation correctly, I want only the 14 possible unique sub category (language) options in the Product subcategory table, correct?

                  • 6. Re: Help needed with multiple product categories and cost calculation
                    philmodjunk

                         That's an interesting question. The ID's need to be unique, but it's quite possible to have two records with the same text in a name field , but different ID's.

                         As an example, One product might be Category CD, Sub Category "Russian" (Sub cat ID = 234 ) Another product might be Category Manual, Sub Category "Russian" (sub Cat ID = 543 ).

                         This would allow you to have a "Russian" sub category for CD's and a different "Russian" sub category for Manuals.

                    • 7. Re: Help needed with multiple product categories and cost calculation
                      JoRo

                           The only way to use

                      "Invoice Data::item category ID match field = Product Sub_Category::Product Category ID Match field"

                           is by setting up a second table occurrence for Invoice Data, correct? I tried setting up this relationship using a second occurrence of Product Sub_Category table directly to Invoice Data, but FM doesn't accept this relationship. I can't select Ok. No explanation is given.

                           I can't link the original Invoice Data table to the original Product Sub-Category table because of the Product table.

                            

                            

                      • 8. Re: Help needed with multiple product categories and cost calculation
                        JoRo

                             Back to the sub category IDs. When I run a summary report, will it run the summary based on sub category 234 and sub category 543 separately? I need to know how many Russian resources total were sold.

                        • 9. Re: Help needed with multiple product categories and cost calculation
                          philmodjunk
                               

                                    The only way to use

                               That should not be the case and a new occurrence of Product Sub_Category should work. What exactly happened when "FM doesn't accept this relationship"?

                               

                                    When I run a summary report, will it run the summary based on sub category 234 and sub category 543 separately?

                               You can do it either way or both. You can sort on the name field from the related table to get all "Russian" items in a group. Sort on the ID field to get two groups.

                          • 10. Re: Help needed with multiple product categories and cost calculation
                            JoRo

                                 Would this whole scenario work better if I set it up without the Category, Sub category, and Subsub Category tables? What if category, sub category and subsub category were just fields in the product table and I use a simple drop down value list to fill those in those fields on the Product table. Would the Hierarchical Conditional Value lists example then work for me?

                            • 11. Re: Help needed with multiple product categories and cost calculation
                              philmodjunk

                                   Another interesting question I like the fact that you aren't just trying to blindly follow my suggestions but instead are thinking through the different possible ramifications.

                                   It could be done, but then you don't have tables for generating your unique ID's for each category value, you'd have to go strictly by name for each value list and relationship. That may work for you but look things over and maybe do some testing with a copy of your file before you commit to that change.

                                   You would lose the ability to rename a category name without having to do a batch update of all the records with the original category name.

                              • 12. Re: Help needed with multiple product categories and cost calculation
                                JoRo

                                     Everything seems to be working up to the last value list. I'm assuming Product|Conditional is a second occurrence of the Products table. Here is my newest relationship graph. Does it look correct? I'll include a screen shot of the Product Conditional value list.

                                     Thank you for your help, by the way. This has been quite the ordeal. :)

                                • 13. Re: Help needed with multiple product categories and cost calculation
                                  JoRo

                                       Actually, this is the relationship between Products|Conditional table and Invoice Data. My mistake.

                                  • 14. Re: Help needed with multiple product categories and cost calculation
                                    philmodjunk

                                         The relationship looks correct.

                                         If you are getting an empty value list, try simplifying the relationship down to just the bottom pair of match fields. I suggested the additional pairs as a way to handle the possibility that some sub categories may be part of more than one category and this relationship should produce the smallest possible value list for what has been selected in the preceding value list formatted fields.

                                         If you are getting a list of all products, that would suggest that your value list isn't set up correctly.

                                    1 2 Previous Next