Populate field with latest record from different entity

[kagostinho]

Hello smart people!

I am pretty new to Power Apps and have been trying to use this amazing community to figure out a solution to my current issue with no luck so I thought I would ask directly for assistance.

I am using D365 CRM Sales Hub (model driven app). I have created a new entity to store the exchange rate to be used for item costs in the Opportunity/Quote. It has a very simple form so the CFO can enter the new rate to be used by the salespeople. 

I have created a column in the Opportunity entity and am trying to have it populate with the most recent rate entered in the table. I have considered 2 options:

#1 - use a formula column which pulls the record with the highest sequence

#2 - use a Power Automate flow so when a new record is added to the table, it changes the status of any other rate to Inactive so my Opportunity column looks just for the Active rate in the table

Unfortunately, I haven't been able to get either option to work and I have a tight deadline to get this working so your assistance/thoughts/suggestions would be greatly appreciated! TIA

PS - I am aware that there is a currency entity that comes out of the box but cannot seem to use it for anything other than selling prices and I specifically need this for costs.

[Jmanriquerios]

Hi @kagostinho 

The first thing is that you can use the currency field for prices and costs.

Now in your approach to inactivate the registry using Power Automate, you must change both the status and the reason for the status if you do not change both values to inactive it will not do so.

In your case you must add the trigger that when a row is added triggers the flow and then searches for the first active value and inactivates it, you must search through the list row action

[kagostinho]

Thanks for the guidance @Jmanriquerios . How do you have the flow just search for the first active value? In my first attempt I just made the new record inactive so I have missed something 🙂

[Jmanriquerios]

Hi @kagostinho 

Use this filter, where the value after Ne is the value created in the previous record

[kagostinho]

I keep receiving fail errors:

[Jmanriquerios]

Hi @kagostinho 

you must remove all the text that is after createon

statuscode eq 1 and createdon ne value

[kagostinho]

Ok - I did that and the flow succeeded but it made my new record inactive instead of the previous one:

[Jmanriquerios]

Hi @kagostinho 

in the update row you must place the row id of the list row action

[kagostinho]

I am still getting errors, no matter which option I select from the 'list rows' action:

[kagostinho · Accepted solution]

Thanks so much for trying to help me today - I do really appreciate it. I ended up just trying different ways to identify what the latest record is and managed to get a flow to work. So in case this can help anyone else:

After the List rows (no columns selected) I added an Apply to each which picked up the value from the List Rows. From there I added a Condition which compared the Seq value from the List Rows to the Seq value from the 'When a row is added'. If Yes, update status and status reason to Active. If No, update status and status reason to Inactive. It is a super inefficient workflow but given that this is never going to be a huge table, I am hoping that it will be ok.