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.

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
Power Platform Call June 2022 768x460.png

Power Platform Community Call

Join us for the next call on August 17, 2022 at 8am PDT.

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Users online (2,523)