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

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

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

Hi @Anonymous 

 

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Anonymous
Not applicable

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. 

Highlighted
Super User II
Super User II

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

Hi @Anonymous

 

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Anonymous
Not applicable

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.  😞

Highlighted
Super User II
Super User II

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

Hi @Anonymous 

 

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Anonymous
Not applicable

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

 

Highlighted
Super User II
Super User II

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


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Highlighted
Super User II
Super User II

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?


@iAm_ManCat

Please 'Mark as Solution' if someone's post answered your question and always 'Thumbs Up' the posts you like or that helped you!


Thanks!
You and everyone else in the community make it the awesome and welcoming place it is, keep your questions coming and make sure to 'like' anything that makes you 'Appy
Sancho Harker, MVP


Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Come join us today! Find your favorite faces from the community presenting at the Power Platform Community Conference!

Watch Now

Experience what’s next for Power Apps

See the latest Power Apps innovations, updates, and demos from the Microsoft Business Applications Launch Event.

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

Top Solution Authors
Top Kudoed Authors
Users online (2,808)