Jump to content
Claris Engage 2025 - March 25-26 Austin Texas ×
The Claris Museum: The Vault of FileMaker Antiquities at Claris Engage 2025! ×

This topic is 6039 days old. Please don't post here. Open a new topic instead.

Recommended Posts

Posted

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?

Posted

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

Posted

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?

Posted

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.

Posted

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.

Posted

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

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 account

Sign in

Already have an account? Sign in here.

Sign In Now
×
×
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.