Indirect Search in Straight Table

[joey_lutes]

I'm attempting something I'm not sure is possible, but would like to toss it out there to the wizarding community

Currently, I have a list of elements which are primarily displayed in tables within a dashboard.  

Essentially, we have sites and services.  Site.id, Site.code, Service.id, Service.code for purposes of this discussion.

Site POL18888 (Code: 12345) is indirectly related to Site VEN17777 (Code: 23456) through Service Y.

User selects a site.code . ie. 12345

Currently, I have a table with:

Col1:  Site.id

Col2:  Concat(distinct {<[Site.code] = , Service.id = p(Service.id) [Item.type] = {'Type1'}, } [Site.code], ', ')

This produces 2 the Site.id and Site.code of related services that have 'Type1' items for the user-selected site.

Site.id                    Site Code

VEN17777             23456

The requirement is to create a table like this:

Site.id          Site Code          Related Site.id          Related Site.code       

(Site.id)        (Site.code)        (Concat statement)    (Concat statement)

POL1888      12345                 VEN17777               23456

Perhaps 'Concat' isn't the correct way to go?  Trying to get the selected data AND the indirect data in the same table has been a challenge thus far.

Thoughts?  Thank you!

[sunny_talwar]

May be it would be helpful to see how the two are related? Would you be able to share few rows of data to see how it looks like?