Jump to content
Sign in to follow this  
dev_synaptech

Self-join question

Recommended Posts

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

Share this post


Link to post
Share on other sites

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.

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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); "¶"; "/")

Share this post


Link to post
Share on other sites

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!

Share this post


Link to post
Share on other sites

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
Sign in to follow this  

×

Important Information

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