cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajeshvsnk
Helper II
Helper II

How to create a 2 entity in CDS second entity has calculation based on first enntity

Hi ,

I want to create 2 entities (tables) in CDS.

First entity should be similar to below

 

Table1:

 

jobID                    jobstatusCode      InterviewDate      ComplettionDate

Java                          0                         22-May-2020      24-May-2020

Java                          1                         22-May-2020      24-May-2020

Java                          2                         22-May-2020      24-May-2020

Java                          1                        22-May-2020      24-May-2020

Java                          0                         22-May-2020      24-May-2020

Java                          2                         22-May-2020      24-May-2020

Net                          0                         22-May-2020      24-May-2020

Net                          1                         22-May-2020      24-May-2020

Net                          2                         22-May-2020      24-May-2020

Net                          1                        22-May-2020      24-May-2020

Net                          0                         22-May-2020      24-May-2020

Net                          1                         22-May-2020      24-May-2020

 

Second Entity should be based on data in first entity

 

JavaJobStatuscode2Count                 NetJobStatusCode2Count

  

                    2                                                      1

1 ACCEPTED SOLUTION

Accepted Solutions
v-yutliu-msft
Community Support
Community Support

Hi @rajeshvsnk ,

Do you want to create two entities like the tables that you mentioned?

If so, I suggest you follow these steps:

1)create entity1 with the data that you mentioned:

jobID(text type)      jobstatusCode(number type)   InterviewDate(date type)      ComplettionDate(date type)

Java                          0                                                    22-May-2020                     24-May-2020

Java                          1                                                    22-May-2020                    24-May-2020

Java                          2                                                    22-May-2020                    24-May-2020

......

2)create entity2 with these fields:
JavaJobStatuscode0Count(number type)                 NetJobStatusCode0Count(number type)

JavaJobStatuscode1Count(number type)                 NetJobStatusCode1Count(number type)

JavaJobStatuscode2Count(number type)                 NetJobStatusCode2Count(number type)

3)create a canvas app, connect with these two entities

4)use this formula to update entity2:

Patch(entity2,Defaults(entity2),
{JavaJobStatuscode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=0,num),
JavaJobStatuscode1Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=1,num),
JavaJobStatuscode2Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=2,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=0,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=1,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=2,num)
}
)

         

 Then you will update entity2 based on the data in entity1.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

3 REPLIES 3
HSheild
Super User
Super User

Hi @rajeshvsnk 

 

Have you looked at rollup fields? https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields

 

Create a 1:Many relationship between your second entity and first entity.  Then on your second entity add rollup fields to count the number of related first entity records, filtered on the jobID e.g. filter on jobID = Java for JavaJobStatuscode2Count

 

I hope that makes sense.

v-yutliu-msft
Community Support
Community Support

Hi @rajeshvsnk ,

Do you want to create two entities like the tables that you mentioned?

If so, I suggest you follow these steps:

1)create entity1 with the data that you mentioned:

jobID(text type)      jobstatusCode(number type)   InterviewDate(date type)      ComplettionDate(date type)

Java                          0                                                    22-May-2020                     24-May-2020

Java                          1                                                    22-May-2020                    24-May-2020

Java                          2                                                    22-May-2020                    24-May-2020

......

2)create entity2 with these fields:
JavaJobStatuscode0Count(number type)                 NetJobStatusCode0Count(number type)

JavaJobStatuscode1Count(number type)                 NetJobStatusCode1Count(number type)

JavaJobStatuscode2Count(number type)                 NetJobStatusCode2Count(number type)

3)create a canvas app, connect with these two entities

4)use this formula to update entity2:

Patch(entity2,Defaults(entity2),
{JavaJobStatuscode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=0,num),
JavaJobStatuscode1Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=1,num),
JavaJobStatuscode2Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Java"&&jobstatusCode=2,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=0,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=1,num),
NetJobStatusCode0Count:LookUp(AddColumns(GroupBy(entity1,"jobID","jobstatusCode","same"),"num",CountRows(same)),jobID="Net"&&jobstatusCode=2,num)
}
)

         

 Then you will update entity2 based on the data in entity1.

 

 

 

Best regards,

Community Support Team _ Phoebe Liu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

Agree with @HSheild 

@roll up fields only recalculate every 12 hours so be aware of that.

 

for more frequent calculation create a power automate flow that runs and calculates

 

to save api calls, filter it to just records where there have been new javajob records in the past X hours rather than recalculating all records

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Users online (2,082)