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?

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?".

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

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?

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.

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.

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!

on step 4. Import only the style field, correct?

Correct.

Thanks again!

Almost worked.

The list shows all occurrences.

Can this be controlled with multiple relationship criteria?

Or, refining the List calc?

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.)

Sweet. Thanks for the education.