Gathering data from multiple records into a single record field

[TedM]

Hi

I've been given some data that I need to modify. Basically its a list of clothing in the following simplified record format:

TS001, T-shirt, Black, Small, $5.00

TS001, T-shirt, Black, Medium, $5.00

TS001, T-shirt, Black, Large, $5.00

TS001, T-shirt, Black, XL, $5.00

TS001, T-shirt, Red, Small, $5.00

TS001, T-shirt, Red, Medium, $5.00

TS001, T-shirt, Red, Large, $5.00

TS001, T-shirt, Red, XL, $5.00

TS001, T-shirt, Blue, Small, $5.00

TS001, T-shirt, Blue, Medium, $5.00

TS001, T-shirt, Blue, Large, $5.00

TS001, T-shirt, Blue, XL, $5.00

TS002, Hoodie, Black, Small, $9.00

TS002, Hoodie, Black, Medium, $9.00

TS002, Hoodie, Black, Large, $9.00

TS002, Hoodie, Black, XL, $9.00

TS002, Hoodie, Red, Small, $9.00

TS002, Hoodie, Red, Medium, $9.00

TS002, Hoodie, Red, Large, $9.00

TS002, Hoodie, Red, XL, $9.00

TS002, Hoodie, Blue, Small, $9.00

TS002, Hoodie, Blue, Medium, $9.00

TS002, Hoodie, Blue, Large, $9.00

TS002, Hoodie, Blue, XL, $9.00

etc for 50,000 records

I need to get this data into the following format:

TS001, T-shirt, Black Red Blue, Small Medium Large XL, $5.00

TS002, Hoodie, Black Red Blue, Small Medium Large XL, $9.00

etc.

i'm sure this should be possible but I can't figure out how to go about it. Can anybody help?

[bcooney]

Create a self join relationship by the first field, TS00#, whatever that is (PartNum?). Create a three calc fields,

Size_All = Substitute ( List (items_Items::Size) ; "pilcrow" ; ", " )

dsp_Colors = Substitute ( List (items_Items::Colors) ; "pilcrow" ; ", " )

dsp_Item = PartNum & ", " & Desc & ", " & dsp_Colors & ", " & dsp_Sizes & ", " & Price

You chould use the Let ( ) and just create one calc.

Is Price a number field, or does it actually contain the $?

[comment]

1. Are you really using version 7?

2. What is the purpose of reducing the list the way you describe? I would be especially wary of listing the colors and sizes as words in a field, instead of individual records or at least return-separated values.

[TedM]

Hi BC,

Yep TS00# is a product number type thing, and the price is just a number with no $.

Comment:

1) No, that's old info v10 now.

2) The purpose of reducing the listing is in order to bulk populate an eCommerce system (Zen Cart).  I can upload data as a simple CSV but I need all the colour and size options for each product to be in a single record for that product - with the data as it is now each product appears multiple times (once for each colour/size option).

[comment]

2) The purpose of reducing the listing is in order to bulk populate an eCommerce system (Zen Cart).

Is this a one time thing, or do you need to do this regularly?

BTW, you should have a table of Products where the TS00# thing is unique anyway - especially if the price is always the same, regardless of color and size,

[TedM]

Although it should be a one-off I can foresee it being done a few times.

[comment]

My policy is to avoid schema elements whose only purpose is export. However, as I said, I believe you should have a table of unique products for your own purposes anyway.

I would start by creating a new Products table with fields for {SKU, Description, Price} where SKU is validated as 'Unique, Validate always'. Then import the data from your current table (ProductVariations) into the Products table. Next, define a relationship between the two tables, matching on SKU.

The rest should be pretty obvious - if not, let us know.

[TedM]

OK,

So now I can gather all the size and colour information into two fields (All_sizes & All_colours) so that's great

Now, because there are multiple colours/size and multiple sizes/colour I'm getting duplicates of the sizes and colours in each field (i.e. Black Black Black Black Black Red Red Red Red Red Red ) - is there any way to remove these duplicates?

[comment]

One way would be to define a value list of Colors, using values from ProductVariations::Color, include only related values starting from Products. Then use the ValueListItems() function to populate the Colors field in Products.

Note that having a product available in colors {Black, Red} and sizes {Small, Medium, Large} does not necessarily mean that the product is available in a combination of Red and Medium.