Help needed with multiple product categories and cost calculation
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!!