Answered

Formatting lookup value to currency and Patching to Sharepoint list as number

(0) Share

Report

Posted on by Community Power Pla... Microsoft Employee

Hi, currently working a powerapp which utilizes a sharepoint list. I have a text input which calls the most recent entry for pensionable earning and allows the user to edit this if there is a change. The submit button then submits the pensionable earning. Currently the pensionable earning is formatted as a number and submits to the sharepoint list as such, I would like for it to be formatted as a currency and rounded to two decimal places always ($758.00).

The syntax for the text input right now is as follows below.

LookUp(Sort('Pension Record',EAKey=EAKeyDropdown.Selected.Title,Descending),EAKey=EAKeyDropdown.Selected.Title,'Pensionable Earning')

Any suggestions?

Categories:

Building Power Apps

I have the same question (0)

All responses (8)

Answers (2)

Sort by

Verified answer

WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Something like this should do it

Text( Round( Value( LookUp( Sort( 'Pension Record', EAKey = EAKeyDropdown.Selected.Title, Descending ), EAKey = EAKeyDropdown.Selected.Title ).'Pensionable Earning' ), 2 ), "$##,##0.00" )

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz ,

Thank you for that, it is now displaying the information as I wanted. The other problem now is that when a user changes the text input, for example clears it before entering a new value, it is not formatted properly. How can I correct a users input so that when they unclick the text input the value is automatically formatted as we want?

Was this reply helpful? Yes No
Verified answer

WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(1)

Report
Copy link

Link copied!
Hi @Anonymous ,

Assuming you are saving all this back as Text - On Change

UpdateContext( { varFormatted: Text( Round( Value(Self.Text), 2 ), "$##,##0.00" ) } ); Reset(Self)

then the Default of the Text box

Coalesce( varFormatted, Text( Round( Value( LookUp( Sort( 'Pension Record', EAKey = EAKeyDropdown.Selected.Title, Descending ), EAKey = EAKeyDropdown.Selected.Title ).'Pensionable Earning' ), 2 ), "$##,##0.00" ) )

and Screen OnVisible (or anywhere else you need to reset this)

UpdateContext({varFormatted: Blank()})

Please click Accept as solution if my post helped you solve your issue. This will help others find it more readily. It also closes the item. If the content was useful in other ways, please consider giving it Thumbs Up.

MVP (Business Applications) Visit my blog Practical Power Apps

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

HI @WarrenBelz ,
This is great, now when I change the text input and click elsewhere on the screen the text input will refresh and format as desired. The problem now is that when I select a different EAKey from EAKeyDropdown, the pensionable earning value does not change, seems as if the lookup function is not working.

Was this reply helpful? Yes No
WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Anonymous ,

I am a little unclear here on the issue - the Text Box Default should be the Variable if it has a value at the time - have you checked the Variable value ?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz , sorry I should have clarified. If the textbox input value is changed and then the EAKeyDropdown value is changed, the textbox is not updated on the new EAKeyDropdown value selected. The textbox continues to display the inputted value instead of doing a lookup for the new value based on the new EAKeyDropdown value selected.

Was this reply helpful? Yes No
WarrenBelz 156,117 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

@Anonymous ,

Assuming you are using SubmitForm, the Update of the Data Card should reflect the Text Box output - are you saying it is not writing to the data source ?

Was this reply helpful? Yes No
Community Power Pla... Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!

Correct, thank you for the clarification. Looks like there was an error on my part. It is working now, thanks!

Was this reply helpful? Yes No