cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sajarac
Super User
Super User

Convert text to number

Hi there,

 

I have a field "txtAmount" with format number. I am tryitng to collect that field in my Excel file but I am geetting this error:

 

Incompatible type. The 'txtAmount' column in the data source you're updating expects a 'Number' type and you are using a 'Text' type.

 

My field in my excel file is formatted as "Currency"

 

Can someone point me in the right direction?

 

Thanks in advance

1 ACCEPTED SOLUTION

Accepted Solutions
v-xida-msft
Community Support
Community Support

Hi @sajarac,

 

Is the "txtAmount" a column (which is formatted as "Currency" (a Number type)) within your Excel table?

 

If the "txtAmount" is a column which has been formatted as "Currency(a Number type) within your Excel table, I agree with @RusselThomas's thought almost. You should remove all the text formatting of your input and save it as the number that's entered.

 

I have made a test on my side, please take a try with the following workaround:1.JPG

 

 

Set the OnSelect property of the "Save" button as below:

Patch(
Table1,
Defaults(Table1),
{
ProductName:TextInput1.Text,
ProductArea:TextInput2.Text,
Price:Value(TextInput3.Text)
}
)

Note: The Price is a column within my Excel table, which has been formatted as "Currency" (Number type).

 

The GIF image as below:Test1.gif

 

On your side, you should type the following formula:

Collect(
Collection1,
{
ProposalType:DrpProposal.Selected.Value,
Proposal:txtproposal.Text,
...,
Proposal_Value:Value(txtvalue_input.Text),
Notes:txtnotes.Text,
User:txtuser.Text,
Date:Date.Text
}
)

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
timl
Super User
Super User

Hi @sajarac

 

Have you looked at the value function? This function will convert text to a number.

 

https://docs.microsoft.com/en-us/powerapps/maker/canvas-apps/functions/function-value

 

Hi, thank you very mcuh for your prompt reply.

 

I did, and after several attemps I added a new field with the "Value Function". but I am trying to collect that value and the formula used doesn't like. see the enclosed picture, I guess everything is there:

 

Picture1.png

Hi sajarac,

 

It looks to me like you're still capturing "Text" into a Number field with that formula by using the Text() function.

 

There is no "Currency" type as such that I'm aware of - it's actually just a number that can be formatted with a currency symbol and two decimal points.

 

Even Excel, though it shows you what looks like a currency in your spreadsheet, is formatting a normal number to look like a currency.  What is stored as data is still a plain number.

 

So, for your app, the collection you're using has been created with that column as a number so, you need to put a number into it - leave the formatting for when you retrieve the number and want to show the user a currency - but for editing or new records, just a plain number which you save back to source.

 

I'd suggest remove all the text formatting of your input and save it as the number that's entered.

If your text field is formatted as TextFormat.Number, you shouldn't have to use the Value() function

 

Kind regards,


RT

 

Thank you very much,

 

I will follow your advice. I don't like the way that the data is enterd into my excel file. I would prefer formated as it is a currency value. but if this solve the issue, I am ok with that.

 

Thank you very much for your advise and prompt response.

 

Regards,

 

Hi Sajarac,

 

It still shows up as a currency in your excel file, and you can easily format it for viewing in PowerApps using the Text() function you had.  Below I originally had 2 entries, the last 2 were added by PowerApps.  

 

xcelcurrency.PNG

If you want the input field to look like the user is capturing currency - well I tried that with one of my apps once - it was such a headache I eventually just stuck a currency symbol manually over the input field and padded the input 🙂

 

Kind regards,

 

RT

v-xida-msft
Community Support
Community Support

Hi @sajarac,

 

Is the "txtAmount" a column (which is formatted as "Currency" (a Number type)) within your Excel table?

 

If the "txtAmount" is a column which has been formatted as "Currency(a Number type) within your Excel table, I agree with @RusselThomas's thought almost. You should remove all the text formatting of your input and save it as the number that's entered.

 

I have made a test on my side, please take a try with the following workaround:1.JPG

 

 

Set the OnSelect property of the "Save" button as below:

Patch(
Table1,
Defaults(Table1),
{
ProductName:TextInput1.Text,
ProductArea:TextInput2.Text,
Price:Value(TextInput3.Text)
}
)

Note: The Price is a column within my Excel table, which has been formatted as "Currency" (Number type).

 

The GIF image as below:Test1.gif

 

On your side, you should type the following formula:

Collect(
Collection1,
{
ProposalType:DrpProposal.Selected.Value,
Proposal:txtproposal.Text,
...,
Proposal_Value:Value(txtvalue_input.Text),
Notes:txtnotes.Text,
User:txtuser.Text,
Date:Date.Text
}
)

 

Best regards,

Kris

Community Support Team _ Kris Dai
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thank you very much,

 

Works FANTASTIC!!

Hi @v-xida-msft 

How can i use Value option with LookUp formula?

I have this formula below, and i want it to get the number value from my lookup fromula:

Value(LookUp(c_MandatoryItems, Mandatory_Procedure_ID = Procedure_ID_Line && Mandatory_Category = ItemCategory_Line, Mandatory_Quantity))

How can i get this right? 

Thanks.

pytbyt

Hi @PytByt ,

 

LookUp(c_MandatoryItems, Mandatory_Procedure_ID = Procedure_ID_Line && Mandatory_Category = ItemCategory_Line, Value(Mandatory_Quantity))

 Hope this helps,

RT

Helpful resources

Announcements
Ignite 2022

WHAT’S NEXT AT MICROSOFT IGNITE 2022

Explore the latest innovations, learn from product experts and partners, level up your skillset, and create connections from around the world.

Power Apps Africa Challenge 2022

Power Apps Africa Challenge

Your chance to join an engaging competition of Power Platform enthusiasts.

Top Kudoed Authors
Users online (1,466)