Filter and Count two unrelated tables

(0) Share

Report

Posted on by ashokpershad

321

So, I have two tables:

Table1 - "Service Requests" with Columns SR#, Description, Priority

Table2 - "Details" with Columns Request#, Status(Completed, In-Progress, Open)

In both tables SR# and Request# are same. So if anyone raise any service request than while working on that request he has to update the Table2 with the same SR# in Request# column.

Now, I want to count the total number for each Status based on matching SR# and Request#. So there are three Labels each for status "Completed" , "In-Progress" and "Open", I wanna show the total count based on matching SR# and Request#.

Highly appreciate if someone can help me out.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (10)

Answers (0)

Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @ashokpershad,

Hope you are doing good. Create a Rollup Field in Service Requests table Then the Rollup Calculation should be Related Service Request(Details) --> Count(Details)

You can find my YouTube video for Calculated and Rollup field based on your Requirement : https://www.youtube.com/watch?v=P-6OTlmRWw4&t=9s

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
ashokpershad 321 on at

Like (0)

Report

@rampprakash I tried that but it is of no help. I want to know the Formula I should use to do all these filtering, calculations etc.

Was this reply helpful? Yes No
Bipin D365 971 on at

Like (1)

Report

@rampprakash ,

Rollup field will not work in this because there is not relationship exist between Service Request and Details. both are unrelated.

Hi @ashokpershad ,

I would request you to create a relationship between two entities first by creating a lookup field in Details entity. This way you can create rollup field to show the count.

Please mark my answer verified if this is helpful!
Regards,
Bipin Kumar
Follow my Blog: https://xrmdynamicscrm.wordpress.com/

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @bipinshan,

As per the Statement, i thought its a Lookup Reference. Between Table 1 and Table 2

As per @bipinshan stated you can create a lookup and follow the video which i posted

Was this reply helpful? Yes No
ashokpershad 321 on at

Like (0)

Report

@bipinshan You are right there is no relationship thats wh y i mentioned in subject unrelated tables.
Actually i wanted to avoid creating relationship using lookup because it will create issue if i want to write Patch function for same. Anyways, since you guys are suggesting same let me try it out. will get back to you soon

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @ashokpershad,

Another Suggesstion,

Create a Whole Number Field for Completed/In-Progress etc

1. Create a Flow when a Details Record is created
2. Use List Rows and Filter the Details Record Based on Request#
3. Use length (Length(ListRows)) update this Length in the Parent Record

Note:

You need to have identification which Record is Parent.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
ashokpershad 321 on at

Like (0)

Report

@rampprakash
@bipinshan
Thank you so much for your help and time.
Creating Flow will help but it will increase my costing because it comes under paid subscription. Any how i tried a different way because rollup thing not working for me. If you guys can tell me exact formula in Rollup it will be of great help. Even this different way have some bug which i noticed in other apps of mine as well. I informed same to Microsoft and they agreed its a BUG. So now I have no other option but to wait till it get resolved.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (0)

Report

Hello @ashokpershad,

Shall we have a Quick Sync Up?. Because need to understand what is the issue in Rollup else We will do the same in JS or Plugins.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No
ashokpershad 321 on at

Like (0)

Report

@rampprakash I can best explain if we could catch through shared screen. I hope its OK for you ? Else we can have it done once Microsoft resolve BUG..
Thanks for your time.

Was this reply helpful? Yes No
Ram Prakash Duraisamy 5,593 Super User 2025 Season 2 on at

Like (1)

Report

Hello @ashokpershad,

If Microsoft Said its bug, then we can wait unit Microsoft Responds.

Please mark as Answer if it is helpful and provide Kudos

Subscribe : https://www.youtube.com/channel/UCnGNN3hdlKBOr6PXotskNLA
Blog : https://microsoftcrmtechie.blogspot.com

Was this reply helpful? Yes No