cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Anonymous
Not applicable

Calculated Field should show merged data from 2 fields

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. 

1 ACCEPTED SOLUTION

Accepted Solutions

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!

View solution in original post

7 REPLIES 7
EricRegnier
Super User
Super User

Hi @Anonymous,

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

Hope this helps!

 

v-siky-msft
Community Support
Community Support

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
Not applicable

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
Not applicable

Hi Sik, 

 

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

Hi @Anonymous 

 

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

Anonymous
Not applicable

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. 

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!

Helpful resources

Announcements
Power Apps News & Annoucements carousel

Power Apps News & Announcements

Keep up to date with current events and community announcements in the Power Apps community.

Power Apps Community Blog Carousel

Power Apps Community Blog

Check out the latest Community Blog from the community!

Users online (3,236)