Displaying multiple values in a table field with multiple joins

[webmaster]

Hi all,

I am trying to create a table where a field value is displayed multiple times, with each value separated by a comma in the same record, instead of having multiple records.

For example: I have 3 tables: specialist, locations, and specialistLocations table for the many-to-many relationship. I want to create a table in Wappler that displays all the clients and the multiple locations that each client can be present in. like this:

I have tried making the following database query, but I cant map multiple locations in a single cell under the locations column in the table.

Can someone provide any suggestions on how the database query should be, or is this something that should be configured from app connect side.

[sbecks]

Have a look here:

Filtering Nested server connect

So I’ve solved this, I’d taken myself down a bit of a blind alley and underestimated how much I could get from the main query builder in server connect… I’ve changed the main query and ditched the custom query: [image] this means I can now toggle the conditions all from within the query rather than trying to toggle the query based on the conditions. It works perfectly for the search and just needs expanding for the other dropdowns. [image] . So I can now run repeat on a single query output. …

Create a distinct database query to get the specialists, then run a repeat on that query to get the locations. In your table you can use the first query to list specialists and a repeat in each row for the locations as text, badges etc.

If you want to filter the specialists by location you’ll need to left join your specialistlocation and locations table (these would replace staff_permissions and permissions in the link).

If you just want a list of all you can just get the specialists then repeat.

I’ve literally been through the same process you’re going through now across the last week or 2!!

[webmaster]

Hey @sbecks,

Thank you for providing help, much appreciated.

I tried doing what you said, and it worked, but what is not working is getting the location data to repeat in the table, next to each other. I can see from dev tools that server connect is returning the right data but the data binding is not accurate.

Some rows in the table have the same specialist, and the location column is not pulling the right info. for the location cell I created a row and gave it repeat attribute after it I created column and textarea. I binded the row with the reapeated locations query and binded the text area with the location name.

Blockquote
Create a distinct database query to get the specialists, then run a repeat on that query to get the locations. In your table you can use the first query to list specialists and a repeat in each row for the locations as text, badges etc.

[sbecks]

Make sure your first query is selecting from the specialist table , not the specialist-location table.

If you want all specialists you only need this as the query (no joins). If you may want to filter by location make sure the query is distinct, then left join specialist-location by specialist-id, and then left join location name by location-id. You can the use these fields in the query conditions too.

This means your first query will return all valid specialists only once (as it’s set to distinct).The repeat then runs on this query to select specialist-location by specialist-id. This should give the outcome you want in your table.

[webmaster]

Perfect thank you very much.