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

[rajeshvsnk]

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

[HSheild]

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 · Accepted solution]

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,

[jlindstrom]

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