List with keywords from another table

[Maryan]

Hello,

I have database as attached image.

data are as follow : 
2 records in table position :
positionId=1, positionName="Eric"
positionId=2, positionName="Jimmy"

3 records in table skills :
skillId=1 , skillName = "Business"
skillId=2 , skillName = "Economy"
skillId=3 , skillName = "Finance"

 

4 records in linkPositionSkill
positionId=1 , skillId=1
positionId=1 , skillId=2
positionId=2 , skillId=2
positionId=2 , skillId=3

Thanks to views and datapages, I can easly have a report/list as follow : 
Eric - Business
Eric - Economy
Jimmy - Economy
Jimmy - Finance

I look for having a report/list with data shown as follow : 
Eric : Business, Economy
Jimmy : Economy, Finance

I couldn't find solution at the moment with views or datapages.
Is it possible to build such dataPage, with my current free access ? if yes, how is this possible ?
Thanks in advance.

 

[cheonsa]

Hello @Maryan,

I tried recreating the Tables on my end to help you achieve your desired result.

You may follow these steps:

1. Create a View and join the three Tables.

 

2. Create a Tabular Report, use LinkPositionSkill Table as Data Source, select PositionID field only, and enable the Show Distinct option

3. Add Calculated Field and paste this Formula:

STUFF((SELECT ', ' + CAST(Skills_SkillName AS nvarchar) FROM _v_PositionSkills WHERE Positions_PositionID= target.[@field:PositionID] FOR XML PATH ('')), 1, 1, '')

Please make sure to change the highlighted texts to the correct field and table names.

 

Here's the result:

Hope this helps!

 

[cheonsa]

Just to add in my previous response, here's another post I used as a reference:

You can also review these howto articles:

https://howto.caspio.com/datapages/reports/advanced-reporting/calculations-in-forms-and-reports/

https://howto.caspio.com/function-reference/

 

 

[Maryan]

This helps a lot !

Thanks you very much for the tip.