cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ashokpershad
Kudo Kingpin
Kudo Kingpin

Filter and Count two unrelated tables

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.

10 REPLIES 10
rampprakash
Super User
Super User

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

ashokpershad
Kudo Kingpin
Kudo Kingpin

@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.

@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/

 

 

Hello @bipinshan,

 

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

 

rampprakash_0-1641202493962.png

 

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

ashokpershad
Kudo Kingpin
Kudo Kingpin

@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

rampprakash
Super User
Super User

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

@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.

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

ashokpershad
Kudo Kingpin
Kudo Kingpin

@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.

Helpful resources

Announcements
Microsoft 365 Conference – December 6-8, 2022

Microsoft 365 Conference – December 6-8, 2022

Join us in Las Vegas to experience community, incredible learning opportunities, and connections that will help grow skills, know-how, and more.

Difinity Conference 2022

Difinity Conference 2022

Register today for two amazing days of learning, featuring intensive learning sessions across multiple tracks, led by engaging and dynamic experts.

European SharePoint Conference

European SharePoint Conference

The European SharePoint Conference returns live and in-person November 28-December 1 with 4 Microsoft Keynotes, 9 Tutorials, and 120 Sessions.

Power Apps Ideas

Changes to Ideas Coming

We are excited to announce a new way to share your ideas for Power Apps!

Users online (4,844)