OutSystems Pattern: Quickly Create a Modal Dialog to Manage N:N Relationships for Tags in OutSystems

Before my vacation one Friday afternoon in July, I got an idea for an app. When exploring the lake in my kayak, the points of interest aren’t registered in Google Maps. If they are by some chance, it’s hard to know where you were precisely you were, so I wanted to tag them on the map with a single tap quickly. With OutSystems, I was able to build an app in about 90 minutes to tag and save various locations and attach photos.

Thinking more about the idea, I realized that I could easily extend this app to other activities like sea kayaking, hiking, or just exploring my neighborhood. I added a table of collections (like tags for blog posts), and a location can belong to multiple collections. Here’s what my data model looks like:

I wanted to add a dialog like the one below to update the collections to which a location belongs. My idea was to have a dialog with a header and then checkboxes for all the collections, indicating if the location is currently included or not.

It’s easy to get the collections that a location belongs to with an aggregate. We can define it like this with the filter LocationCollection.LocationId=LocationId to only get the current collections for the location we’re editing.

It starts to get tricky when we want to fetch all of the collections and indicate if the location is a member of that collection. If you were going to write it out using SQL, you’d probably write a statement like this:

SELECT    collections.id, 
          collections.NAME, 
          sum( 
          CASE 
             WHEN collectionlsocations.locationid=@loc_id THEN 1 
             ELSE 0 
          END) 
FROM      collections 
LEFT JOIN collectionslocations 
ON        collections.id = collectionlsocations.collectionid 
GROUP BY  collections.id

To keep things simple and I made four updates to my aggregate:

1. Removed the filter
2. Added an attribute called “count” and set it to If(LocationCollection.LocationId=LocationId, 1, 0)
3. Added group by to the collection.id (Id) and collection.name (Name).
4. Added sum of “count” (Exists)

Here’s how the final modal looks:

Using OutSystems, it took me less time to implement this feature than to summarize how to pull the data because I didn’t need to write that gnarly SQL statement up above and could leverage standard components.