Grouping by referenced virtual column (list)

[Bibaboy10]

Hi there!

I have used this logic by @MultiTech to create an app that holds many-to-many relationships. I have a question about grouping.

In my app, a capability (“order”) can be supported by many applications (“products”). Vice versa, an application (“product”) can support multiple capabilities (“orders”).

In my Application View where all applications are listed, I want to be able to group by (related) Capability. 

I want my Applications View to show something like this:

Capability A:

Application 1

Application 2

Capability B:

Application 2

Application 4

Capability C:

Application 3

As you can see, one application can be grouped under multiple capabilities (example application 2).

However, the app shows the count of related capabilities (a number between brackets is the number of capabilities the application supports):

Related Capability (2):

Application 1

Related Capability (3):

Application 2

How do I make it work?

All the best,

Kay

[TeeSee1]

Could you share the definition of the view?

[Bibaboy10]

Of course, but before I can do that I need to know - what do you mean with "definition of the view"?

[TeeSee1]

I created a mock app according to the video and it seems to work fine.

The only parameter that seems to affect the number of rows displayed is "Max nested rows" in your application detail view which defaults to 5. Unless you have changed it to 1, I am not sure what is going on with your app..

[Bibaboy10]

Thanks for your reply! Yes, in the Applications Detail views everything works fine in my app too, the problem is in the "generic" Applications View. Here's a screenshot (below) that hopefully better explains my problem. I want to have the actual "capabilities" there as groups. Not the count of related capabilities per application. I also included a screenshot of the Table "Capabilities" where you will see how the Google Sheets holds the data. Like I said, I modeled the app after the one in the link I provided. If you need more info, let me know 😃 !

Applications View:

Capabilities Table:

Thanks in advance!

[WillowMobileSys]

Hi @Bibaboy10 !

A couple of things:

1) "Related" columns are typically a List of reference values.  You cannot Group by these columns.  It looks like the [Related_Capabilities] might be a column you created?  Even so the name still implies a list of values  and while you can try to Group by it, it doesn't make sense to do so.

2)  The view, where you are attempting to apply the Group, is not correct.  It seems that it is the Parent or Host view.  You want to apply the group in a view designed to display the rows in the Capabilities table itself.  Normally, when you have a "Related" column that is automatically created by AppSheet, there will also be a view named something like "Capabilities_Inline" which you can set as a Table view (if not already) and then apply your desired grouping.  The result is that in your Parent or Host Detail view, there will be an embedded table that is grouped, showing all of your [Related_Capabilites] rows. 

CAUTION:  If you have created your own [Related_Capabilities] column  you may have some difficulty getting the system views to be found correctly.  It depends on the rest of your app definition.  If you still have some trouble, let us know.

[Bibaboy10]

Hi Willow, I don't have the Capabilities_Inline View (I do see the Inline Views for applications, processes, teams, and data). The related_capabilities column was created when I followed the steps presented in the link in my first post. I just replied to the other helpful Appsheet guru below, maybe that gives you some more info. Everything works perfectly, I'm just not able to group data/process/application/teams by Capability in the designated Views. This is weird to me because all the relationships have been established. Appreciate all the help! 

[WillowMobileSys]

Ok,  I think I just wasn't grasping what you wanted as an end result.  I think I understand the problem now.

In order to perform Grouping in the Capabilities table, each assigned/related application must make up its own row.  Your table should look like the one below.  I'm not sure what your current app design is like, so I can't offer a suggestion on what changes to make this happen.  Should not be difficult. 

EDITED:  Should have shown capability ID's rather than name

Capability ID	Capability	Application
a	Capability A 9690f25d	437edf05
b	Capability A 9690f25d	8099d189
c	Capability A 9690f25d	b2ad9895
d	Capability B c762bd5e	437edf05
e	Capability B c762bd5e	8099d189
f	Capability B c762bd5e	b2ad9895
g	Capability C 21f73990	437edf05
h	Capability D 834697eb	8099d189
i	Capability D 834697eb	b2ad9895
...

[Bibaboy10]

Thanks! This is unfortunately not ideal because then I get different versions of Capability "B" in my app where I only want to have 1 that is supported by various applications.  And if I go to the Application View for a certain application, I want to see 1 related Capability B (and possibly a Capability C and Capability D). That's how my current app is set up and how the app in the link in my first post works 🙂 . Could also be that I don't fully grasp your approach. Did I miss something?

[WillowMobileSys]

Yes it does need a little more explaining.  The example by @MultiTech you used for implementation gets you there IF all you want is to be able view the related rows - list of applications for each Capability or a list of capabilities for each Application.  You can get by with just the 2 tables to accomplish this in AppSheet.  The lists are a short-cut avoiding actual table representation of the many-to-many relationship.

However, once you need to perform some sort of viewable aggregation, Grouping in this case,  the value you wish to Group by must be represented as a single column value - not as part of a list.  This means you'll need to implement the traditional approach to a many-to-many relationship.  It requires at least 3 tables - Capabilities, Applications and then a joining table - maybe named "Application Capabilities".  

What I showed above was this joining table and I should have included the capability ID rather than the name - I have updated that post to reflect this.

With this construct you can implement the virtual columns to pull the relationship list from this joining table allowing you to create views on this table that are grouped by either Capability or Application.  

[Bibaboy10]

Ok so I tried it with the bridge table  😃 ! Made an Application table, Capabilities table, and CapabilityApplications table. Funny thing is that I ended up with the exact same problem! The app works fine, but I still can't group by capabilities in the application view. Still only by "related capabilities" (which just gives a number). So there's something I am doing wrong still. I know I'm asking a lot, but could you maybe create a simple sample app for me that I can copy so I can look 'under the hood', with only the 3 tables that we discussed (capabilities, applications, capabilityapplications). I think this way I will finally understand how this should work. If I still don't get it then, I will move on with my current approach haha! Thanks so much for helping me out, really appreciate it, great community!

[WillowMobileSys]

I think it would be better to jump on a Zoom call then I can see exactly what you are trying and it would likely save time.  You up for that?  If so, direct message me at john@willowmobilesystems.com

[TeeSee1]

The current Applications table structure doesn't give you what you want.

When you choose "Related Capabilities" as your grouping key, the system looks for applications having identical lists of related capabilities - think about it, it is obvious.

Anyway, if you want capabilities as grouping key, why don't you just use Capability view and group by capability(by itself)? Is not this what you want to see?

[Bibaboy10]

I would love to, but I can't seem to make that work (this is my first Appsheet app btw).

It's not possible in the app to make a Capability grouping in the Capability View that separately shows all supporting Applications like in your screenshot.

I think it's because Applications are connected to Capabilities as related records (the same way Capabilities show when I open the Application Detail View):

Can you maybe share with me your sample app so I can see how you organized it?

In my mind, it should be possible because the relationships show perfectly in the Application Detail screen. The app knows which Applications belong to which Capabilities. It's just not possible to group them like that for me.

Overall, I just want to be able to connect Capabilities to Applications, Data, Team, and Process components in a many-to-many relationship (eg; one application can support multiple capabilities and one capability can be supported by multiple applications). The sample app that I linked to in my first post works perfectly. Now what I would love is just to be able to group my applications / processes / teams / data components by capability.

[TeeSee1]

Sure be my guest

https://www.appsheet.com/Template/AppDef?appName=Generic-3401559&appId=Generic-3401559&quickStart=Fa...

[Bibaboy10]

Much appreciated! Will definitely take this option into consideration. I will also have a look at the "many-to-many via bridge table" concept as presented above. Thanks so much for helping me @TeeSee1 ! 

[Réin]

Do you happen to have or maybe be able to provide a brief description of how this works. I think this is what i have been trying to figure out for hours but Im note for sure. 

Any help is appreciated sir!