Convert text to number

[sajarac]

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

[timl]

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

[sajarac]

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:

[RusselThomas]

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

[sajarac]

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,

[RusselThomas]

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.  

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 · Accepted solution]

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:

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:

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

[sajarac]

Thank you very much,

Works FANTASTIC!!

[PytByt]

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

[RusselThomas]

Hi @PytByt ,

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

 Hope this helps,

RT