cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
meenuu
Level: Powered On

Don't want to change the saved data after lookup field updates

I have a calculation which has to calculate tax value for two currencies.   I set it the exchange rate field as lookup in different Sharepoint list. The exchange rate will be updated when they want. Now after i update the exchange rate field, the existing data calculations changes according to the latest exchange rate. How can i solve this? I don't want to change the existing record and remains as previous exchanging rate. 

8 REPLIES 8
Super User
Super User

Re: Don't want to change the saved data after lookup field updates

Hi @meenuu 

 

I would use a number field in the record, to store the rate at the time that the record was created.

 

So when the form loads, it sets that field to the LookUp value, and then the final tax value is calculated from that Form's field value rather than from the Lookup directly. That way it will store the USDRate value for that record with the record and looks at that for the value, and the previous values won't be affected.

 

Something like this:

image.png

 

 

Hope this helps!

Cheers,

ManCat




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
meenuu
Level: Powered On

Re: Don't want to change the saved data after lookup field updates

Hi @iAm_ManCat The values are not changing unless i click edit and save. It is quite risky if the users click edit and save accidently. 

Super User
Super User

Re: Don't want to change the saved data after lookup field updates

Hi @meenuu

 

When editing existing records, I usually have something like the following in my Apps - this formula may need some tweaking for your use

 

Default:

 

If(
//Check if no value exists in the current form's usdrate textbox
IsBlank( USDRateDataCardValue.Text ),
//If no value/(blank), then set it to the LookUp
LookUp( 'Visitor Setup', Categ="usdrate"),
//If it is not blank then keep the default value
Parent.Default
)

 

 

 

.. also then have a variable that hides the DataCard when in edit mode?

 

Let me know how you get and and if you need more help then please share the formula's that you've used so far and what you've tried to make it work,

 

Cheers,

ManCat




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
meenuu
Level: Powered On

Re: Don't want to change the saved data after lookup field updates

@iAm_ManCat error.PNG

I am getting error. error message.PNG

I tried to change TextInput1.Text to TextInput1.Default. Still getting an error. I am really sorry for being a poor programmer. I am a student and learning it by myself.  😞

Super User
Super User

Re: Don't want to change the saved data after lookup field updates

Hi @meenuu 

 

No need to apologise - we are all learning, just at different speeds and different paths and different starting points 🙂

 

So, to help with your investigation as to why it's not working, you can click into the formula bar, this will then put squiggly red lines underneath any serious issues.

image.png

Then if you hover your mouse over one of the parts, it will try to tell you what it thinks the issue is:

image.png

In your formula, I notice you still have Desc at the end of the LookUp - the third part of a LookUp is the result, so is the resulting column that you want to return data from in the 'Visitor Setup' Data Source the 'Desc' column?

image.png

 

Also what field is your DataCard5 mapped to (DataField in advanced tab) - this is what Parent.Default refers to.

 

Cheers,

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
meenuu
Level: Powered On

Re: Don't want to change the saved data after lookup field updates

Yes, only if i add desc i will get the value. Or do i have any other easy way to have exchange rate lookup ?datacard5.PNG

 

Super User
Super User

Re: Don't want to change the saved data after lookup field updates

And Looking at the formula I gave you, that was for a slightly different situation - in your specific case, seeing your screenshots and trying to replicate it, you will get a circular reference (checking the object itself to set the object which will keep going infinitely) so what you should have is something like this:


If(
//Check if no value exists in the existing item for usdrate
IsBlank( Parent.Default ),
//If no value/(blank), then set it to the LookUp
LookUp( 'Visitor Setup', Categ="usdrate", usdrate),
//If it is not blank then keep the default value
Parent.Default
)


So this should check to see if the parent (the field that this textbox updates) is blank, then set the usdrate only if it is blank, otherwise keep the existing value.

 

To stop people from editing it altogether if there is already an usdrate, you can set the Visible property of DataCard5 (the container) to:

If( !IsBlank( USDRateDataCardValue.Text ) )

This checks if there is an item already, and makes the entire card invisible if there's already a value 🙂

 

Could you try experimenting with that a bit and let me know how you get on?

 

Thanks,

Sancho




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!
Super User
Super User

Re: Don't want to change the saved data after lookup field updates

Ah, I see you've got multiple values in that card - how are you then using or updating those other values? Are they pulling live data every time?

 

Is there a column now for usdrate in the SharePoint List you are submitting? you need to be submitting the value as a value for a column when you initially create a record, otherwise it will keep looking up the present value instead of the one stored for that item - does that make sense?




Don't forget to 'Mark as Solution' if a post answered your question and always 'Thumbs Up' the posts you like or helped you!

Helpful resources

Announcements
thirdimage

Power Automate Community User Group Member Badge

Fill out a quick form to claim your user group badge now!

sixthImage

Power Platform World Tour

Find out where you can attend!

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

fifthimage

Microsoft Learn

Learn how to build the business apps that you need.

Top Kudoed Authors
Users Online
Currently online: 336 members 6,928 guests
Please welcome our newest community members: