Calculated Field should show merged data from 2 fields

[Anonymous]

Hello,

I have the entities and fields as follows:

EntityA

Year            Month       Days     Rate      Payments (calculated field = days * rate)

2017            Jan              10       55           550

2017            Jan               21      60           1260

2018            Feb              15      75            1125

EntityB (one to many relationship with EntityA) 1:N

Year        Month      MonthlyTotal

2017       Jan             1810

2018       Feb             1125

Please suggest me how can i achieve this? Thank you. 

[EricRegnier]

Hi @Anonymous,

Based on what you mentioned, you need two entities. Here are some pointers:

• To create an entity you can follow this documentation: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-create-entity
• For EntityA Payment fields is a calculate field: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-calculated-fields
• EntityA, Year, Month and Day can be defined as one field of type "Date and Time"
• EntityB, requires a lookup type of field to EntityB: https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/data-platform-entity-lookup
• EntityB: what is MonthlyTotal? A rollup of EntityA? See https://docs.microsoft.com/en-us/powerapps/maker/common-data-service/define-rollup-fields

Hope this helps!

[v-siky-msft]

Hi @Anonymous ,

Which one in entity A is LookUp filed? What do you want to achieve?

Do you want to generate the entity B data based on entity A?

If so, please try this to generate the data in Collection.

ClearCollect(MyCol, DropColumns(AddColumns(GroupBy('EntityA', "Year","Month","Grouped"),"MonthlyTotal",Sum(Grouped,Payments)),"Grouped"))

Then, save the collection data to entity B in bulk by ForAll/Patch function.

ForAll(MyCol,Patch('EntityB', Defaults('EntityB'), {Year:Year, Month: Month, MonthlyTotal: MonthlyTotal}))

Hope this helps,

Sik

[Anonymous]

Hi EricRegnier, 

Thanks for quick suggestions. 

I was able to follow the steps. 

EntityB is basically the data of EntityA. Meaning grouping the combination records. This is more kind of excel format. 

I had tried Roll up field, but that does not serve my requirement. Rollup field needed me to define in Parent entity which combines the total of child entity records. 

I would need in vice versa and based on year and month - total needs to be calculated. 

[Anonymous]

Hi Sik, 

Yes i would like to generate the entityB data based on entityA in model driven approach. 

[v-siky-msft]

Hi @Anonymous 

Have no idea with model-driven approach, could you please describe your needs clearly?
Sik

[Anonymous]

Hi Sik,

I would like to implement this requirement in entity model driven applications. 

Through powerapps we can create canvas, model driven and portal applications right. 

I believe the formula's ClearCollect and ForAll not available for the fields which defined as calculated fields when working on model driven applications.

I tried with canvas applications and able to see the output. But i need to implement in model driven app. 

[EricRegnier · Accepted solution]

If roll-up fields can't satisfy your requirements then you wouldn't have an EntityB. What EntityB is, is some kind of report so you would need to create a report (i.e. PowerBI) or maybe out-of-the-box Views depending on your requirements. You can have FetchXML to aggregated data so grouping like you mentioned and save that FetchXML as a View which you can then reference in your model-driven app.

You can use this tool from the XrmToolBox to generate your FetchXml: https://fxb.xrmtoolbox.com/

And then use the View Designer tool also from XrmToolBox to design your View based on the FetchXml: https://www.xrmtoolbox.com/plugins/Cinteros.XrmToolBox.ViewDesigner/

Hope this makes sense and helps!