Creating a view using INNER JOIN principle

[GerbenB]

Hello here!

I am new to Common Data Service and coming from - as many - an SQL-based background, this matter can be quite hard to understand at times.

In the current datamodel I got several entities, please refer to the attached image.

The design declares that on the screen for "this tasks used articles" we show a list of Categories under under which the used articles are shown. Normally I would use some INNER JOIN magic, but this doesn't seem to be possible within CDS, or am I missing something?

Edit:

I'm using Canvas as primary UI

End Edit

Kind regards,

Gerben.

[DavidJennaway]

If you're trying to create a view in a model-driven App, then the UI allows you to display fields from the entity on which the view is defined, and fields from any entity with which it has an N:1 relationship, but that's as far as it goes. However, views can use filter criteria based on multiple levels of relationship (and N:N relationships).

If you need to display data from more levels of relationships, you can create additional derived fields using calculated fields, rollup fields, or workflows to set derived fields. None of these feel ideal from a data modelling perspective, but it is sometimes necessary

[GarethPrisk]

@GerbenB, a few clarifying questions

1. Your ERD shows Categories and Articles as a N:N relationship - is this an OOB N:N?
2. In a simple example
   • Categories [A,B,C]
   • Articles [1,2,3]
   • N:N (Category:Articles) [A:1,A:2,B:2,C:1,C:3]
     • Are you expecting that Category A would show Articles 1, 2
     • Are you expecting that Category B would show Article 2
     • etc.
3. How are you showing/selecting the Category?
   • There's a few other entities in your ERD but it's not clear how they're involved
   • Can you walk through the ERD as it pertains to your expected behavior and filtering?

[GerbenB]

Hello @GarethPrisk,

1. Not really sure what OOB means. The relationship is not required on either side. An article can have no categories and fall under the "general" category, which is a default not-defined category. Hope this helps clarifying.
2. The screen is filtered per category, including articles without any category attached. 
3. See image above. The selected category is put into the gallery, using the following formula:

Table(Gallery1.Selected, {aa123_name: "General"})​

So Gallery2 is filled with the selected category AND the general category.

• There are quite a few entities not mentioned, since they are not of interest for this question in my opinion, I could be wrong. Hopefully the above gave you a better understanding of what we want.
• Of course:
  • We got our task-screen where we select a category
  • Under the category-screen (see above) we see all selected articles including its quantity (The ArticleUsage entity) grouped by the category, either selected or general.
  • The general category is a fall-back-category in case an article has no categories assigned.
    • I find the design quite odd, but "I am just executing the job."

I am playing around with possibilities and I am currently using the following snippet to filter the article per category (The filter is not complete since it does not test whether the article is related to "this task"

Filter([@Articles], ThisItem.Category in Categories)

However, due to the fact that I cannot use Categories (name of the relation between Article and Category I am unable to check for any existing relations. The error that I receive is "the specified column is not accessible in this context"

I also kn that using "in" is bad-practise but I needed to test it.

[GerbenB]

Hello @GarethPrisk have you had time to review my reply?

[cchannon · Accepted solution]

OK, it sounds like you are stuck trying to figure out how to hop from your list of Tasks all the way across Article Usage, Articles, and to Categories, but not sure how to retrieve the records at a 1:N or N:N relation as opposed to an N:1. Is that about right?

So, strictly speaking you could do this in PowerApps because 1:N and N:N relationships are accessible as objects on an entity, just like attributes, so getting all Articles Usages for a Task should be as simple as Task.'Article Usages' and getting all Categories for an Article should be as simple as Article.Categories. BUT, in order build a string out of all that, you will need to do some serious concatenate-loop-concatenate-loop gymnastics, and I, personally, hate that kind of stuff.

So, let's use a better query tool than jumping one record at a time. PowerAutomate gives you way more useful record querying, looping, and concatenation abilities with a visual map that makes it really intuitive, so that's where I'd recommend you do it. You can tie a PowerAutomate flow to just about anything in canvas apps, including a Gallery datasource, and you can even run a custom fetchxml query from inside a powerautomate flow so you could just code your way right past this problem in a syntax that--while very different from SQL--should still be pretty easy for you to pick up if you don't already know it.

This blog (not mine; I just found it with a quick internet search so don't blame me if it isn't 100% accurate) covers the plumbing for how to call a PowerAutomate Flow from your Canvas app and return a response object. It is focused on a button scenario, but the same concepts would apply for returning something to use as a data source for your gallery. Give it a try and see if this makes it easier for you to get the query results you want.

[GerbenB]

We're going to test it out with Power Automate. Thanks for the tip.