Calculated Field should show merged data from 2 fields

(0) Share

Report

Posted on by Community Power Pla...

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.

Categories:

Microsoft Dataverse with Power Apps

I have the same question (0)

All responses (7)

Answers (1)

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report
Re: Calculated Field should show merged data from 2 fields

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!

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report
Re: Calculated Field should show merged data from 2 fields

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Calculated Field should show merged data from 2 fields

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.

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Calculated Field should show merged data from 2 fields

Hi Sik,

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

Was this reply helpful? Yes No
v-siky-msft on at

Like (0)

Report

Re: Calculated Field should show merged data from 2 fields

Hi @Anonymous

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

Was this reply helpful? Yes No
Community Power Pla... on at

Like (0)

Report

Re: Calculated Field should show merged data from 2 fields

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.

Was this reply helpful? Yes No
Verified answer

EricRegnier 8,720 Most Valuable Professional on at

Like (0)

Report

Re: Calculated Field should show merged data from 2 fields

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!

Was this reply helpful? Yes No