Count records in another table based on customer id

(0) Share

Report

Posted on by Dubbel_Lars

Hi,

After many hours searching i hope someone over here can point me in the right direction.

I have two tables. One with projects in it, and one with support tickets in it. An example of my data:

Projects

Project ID	Customer	Date finished
123456	55522	9-2-2024

Support tickets

Ticket ID	Customer	Date created
654321	55522	10-2-2024

My goal is to create a column in the projects table that sums the amount of support tickets created within one month after a project is finished. As you can see the only matching value is the customer ID.

I thought i can achieve this with creating a rollup column and create a relationship between the 2 tables, but as far as i understand dataverse uses table keys to create succesfull relationships and that is where thing get difficult. I think i need to create a key on the customer, but because a customer can have more then one project and more then one support question values are not unique and i am unable to create a key on it.

In PowerBI i do this with a many to many relationship which sounds the most logical to me to also use in Dataverse, but with a many to many relationship i cannot select the related table in the rollup column. I also tried a calculated column and formula, but i didn't get that working either.

I am pretty new to this kind of functionallity in Dataverse so i hope someone can tell me what the best way to achieve my goal is.

Thank you in advance!

Kind regards,

Lars

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (2)

Answers (1)

Verified answer

Jonathan Manrique 2,687 on at

Like (0)

Report

Hi @Dubbel_Lars

You can have several alternatives.
One could be to create the N:N relationship yourself by creating an intermediate table and do the cumulative there, but you will have to fill in the intermediate table with code.

Then another alternative, and perhaps easier, is to use Power Automate, where you can count the tickets belonging to a particular customer and a project. To achieve this point you must pass both GUIDs (id) to Power Automate so that it is able to return the filtered records and add them to a column you create.

The important issue at this point is what the trigger should be, when you need this information to be displayed.

Another aspect to keep in mind is that Power Automate is an asynchronous process so the result will not be immediate.

If I have answered your question, please mark your post as Solved.
If you like my response, please give it a Thumbs Up.
You can accept more than one post as a solution
Follow me on Linkedin, I talk about Power Platform
www.linkedin.com/in/jonathan-manrique-rios

Was this reply helpful? Yes No
Dubbel_Lars 23 on at

Like (1)

Report
@Jmanriquerios thanks a lot for your help! Today i managed to do it with Powerautomate.

For anyone else who ever wants to do this, this are the steps i took:
First get all the records from table A
Then add a for each loop and then get the records for table B filtered on the result from table A (i used a fetch XML query for this)
Then inside the for each loop add an update row action and fill the columns with the results from the for each loop (i used a length(outputs([result name])) to count the amount of records retrieved

Was this reply helpful? Yes No