cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

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
Highlighted
Super User II
Super User II

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!

View solution in original post

7 REPLIES 7
Highlighted
Super User II
Super User II

Re: Calculated Field should show merged data from 2 fields

Hi @vghanithala,

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

Hope this helps!

 

Highlighted
Community Support
Community Support

Re: Calculated Field should show merged data from 2 fields

Hi @vghanithala ,

 

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

 

Highlighted
Frequent Visitor

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. 

Highlighted
Frequent Visitor

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. 

Highlighted
Community Support
Community Support

Re: Calculated Field should show merged data from 2 fields

Hi @vghanithala 

 

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

Highlighted
Frequent Visitor

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. 

Highlighted
Super User II
Super User II

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!

View solution in original post

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Power Platform ISV Studio

Power Platform ISV Studio

ISV Studio is designed to become the go-to Power Platform destination for ISV’s to monitor & manage published applications.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Tech Marathon

Maratón de Soluciones de Negocio Microsoft

Una semana de contenido con +100 sesiones educativas, consultorios, +10 workshops Premium, Hackaton, EXPO, Networking Hall y mucho más!

Users online (5,925)