Rainy Pass Posted July 7, 2008 Posted July 7, 2008 Is there a way to reduce record numbers using relationships? I have been using scripts to reduce UPC (universal product code) data. This method is cumbersome to manage across many databases. Data: Garment styles and associated attributes for a product cart. The original data contains one record for each style, color and size combination. I want to convert this information to one record per style. i.e., each style record contains a list of colors and a list of sizes as attributes. Example: Convert Style X, a size, a color (5555, S, Blue; 5555, M, Blue; 5555, L, Blue; etc.) To Style X , sizes, colors (5555, S M L XL, Blue Green Red) Is there a easy way to manage this kind of data without scripting?
comment Posted July 7, 2008 Posted July 7, 2008 It depends on what you need. Obviously, if you want information about available combinations, you'll need a record for each. A single record like: ProductID: 001 Colors: Blue¶Green¶Red Sizes: S¶M¶L cannot answer the question "Can I get this in blue, medium size?".
Rainy Pass Posted July 8, 2008 Author Posted July 8, 2008 So, how to I get there? How do I convert or view the data in the this format. ProductID: 001 Colors: Blue¶Green¶Red Sizes: S¶M¶L
comment Posted July 8, 2008 Posted July 8, 2008 Sorry, you'll need to be more explicit: how to get from where to where? IOW, what do you have now (in terms of tables and fields), and what do you want to have?
Rainy Pass Posted July 8, 2008 Author Posted July 8, 2008 The raw data I have is a file of 12,000 records containing attributes such as Style, Size, Color. Style X, a size, a color (5555, S, Blue; 5555, M, Blue; 5555, L, Blue; etc.) I want to view, manipulate and export the data as Style, SIZES. COLORS. Style X , sizes, colors i.e., ProductID: 001 Colors: Blue¶Green¶Red Sizes: S¶M¶L Once the data is reconfigured, the solution manages pricing and images, prints sales sheets and exports the data to a few different web shopping carts. There are only about 400 Styles in the 12,000 records. Can you suggest how I should approach viewing or converting the data from 12,000 to 400 records? Thanks.
comment Posted July 8, 2008 Posted July 8, 2008 Assuming I understand this correctly (it's always hard to tell with "such as" and "etc."): 1. Create a new table of Products with fields: • Style - Number or Text (?) • Colors - Text • Sizes - Text 2. Set the validation on Style to Unique value, validate always. 3. Go to the "old" table (let's call it Variations), and show all records. 4. Go back to the Products layout and import records from Variations, mapping Style to Style (and no other fields). You should get about 400 records - one for each style. 5. Define a relationship between the two tables, matching on Style. Now, if it were me, I would stop here. You can get all the required data in the Products table over the relationship - for example, a calculation field = List ( Variations::Colors ) will return a list of colors available for the style. But, if you really want, you can: 6. Show all records in the Products table. Click in the Colors field, and replace field contents with a calculation = List ( Variations::Colors ). Do the same for Sizes, and you're done. A backup is recommended before trying this.
Rainy Pass Posted July 8, 2008 Author Posted July 8, 2008 Sorry if I am not clear but, you got it. I lost you on step 4. Import only the style field, correct? Thanks. Much better than scripting!
comment Posted July 8, 2008 Posted July 8, 2008 on step 4. Import only the style field, correct? Correct.
Rainy Pass Posted July 8, 2008 Author Posted July 8, 2008 Almost worked. The list shows all occurrences. Can this be controlled with multiple relationship criteria? Or, refining the List calc?
comment Posted July 9, 2008 Posted July 9, 2008 Yes, of course - how silly of me. I forgot you still need to de-dupe the lists. OK, there are two ways to do this: 6a. Define a value list Colors as values from field Variations::Color, showing only related values, starting from Products. Now a calculation = ValueListItems ( Get (FileName) ; "Colors" ) will return a list of UNIQUE colors available for each style. You can put this in a calculation field (must be unstored), or use it in Replace Field Contents to populate the Colors field permanently. Repeat the same process for Sizes. OR: 6b. Define a new table Colors with one field, Color. 7b. Set the validation on Color to Unique value, validate always. 8b. Go to Variations and show all records. 9b. Go to Colors and import from Variations, importing only the Color field. 10b. Define a relationship between Variations and Colors, matching on Color. Now you can use a calculation = List ( Colors::Color ) to get a list of unique colors available for the style. (This is actually what you should have in the first place - a proper relational structure, where Variations is a star join between the three entities of Style, Size and Color.)
Recommended Posts
This topic is 6039 days old. Please don't post here. Open a new topic instead.
Create an account or sign in to comment
You need to be a member in order to leave a comment
Create an account
Sign up for a new account in our community. It's easy!
Register a new accountSign in
Already have an account? Sign in here.
Sign In Now