Self-join question

[dev_synaptech]

Hi, my data modeling skills are rusty and the solution is not clear to me.

I have a table with 6000 records, it's a join table for two other tables and it contains a Product ID field and a Category field.

The products are not unique and neither are the categories - they repeat throughout the array. I have managed to show for every Product the multiple categories that the product can be in through a self join in a second table occurrence - yay for me.

However, what I'd like to do is build a calculation that writes the values in the self-join portal out to a field that looks like this:

product_id = "Category_1" & "/" & "Category_2" & "/" & "Category_n"

or the Output would be a string like

Sockguy Thermal Sock: Socks / Road Cycling Gear / MTB Gear

Hope this makes sense..

TIA

[bcooney]

Sorta makes sense. You have this setup with Products, Categories (ideally with CatIDs) and a join btw them. Through the relationship from Products to the join table, you should be able to see all the Categories for that Product. Then you could either create a portal on Products to the join table, listing the Categories or use the List() function (FM8?) --or a value list-- to create a text calc that contains all the Categories.

I'm not seeing a self-join here at all. You start at Products.

[dev_synaptech]

Hi bcooney,

Thank you for the assist - the List function is exactly what I needed. I thought I'd post my solution in hopes that it might help someone else.

So, I only needed to work in the one "join table" - the one that contained only two fields "products" and "categories". I created the self-join with products=products and displayed product ID and Category ID in a portal.

So for "product_1" I have "category_a"

"category_b"

"category_c"

and for "product_2" I have "category_d"

"category_e"

"category_f"

and for "product_3" I have "category_a"

"category_c"

"category_f"

and so on..

But for each productID I wanted to pull all of the category ID's into a single field separated by a "/" so the output would look like:

productID -- category_a / category_b /..category_n

So I used this calculation:

productID & " -- " & If ( GetValue ( List ( selfjoin::category) ; 1 ) >0; GetValue ( List ( selfjoin::category) ; 1 )&"/" ; "" ) &

If ( GetValue ( List ( selfjoin::category) ; 2 ) >0; GetValue ( List ( selfjoin::category) ; 2 )&"/" ; "" ) .... repeated as necessary.

This resulted in the output string I was hoping for.

Thanks again!

[bcooney]

I don't see why you can't just start at Products...but I'm not there to see what I'm missing.

Why can't you use:

ProductName & Substitute ( List (Products_jointable::Category); "¶"; "/")

[dev_synaptech]

It seems that I have been running in circles as I got lost in the forest. Thank you for the much simpler, more elegant solution. Your help is very much appreciated!