Answered

isnumeric if statement with number formatting help

(0) Share

Report

Posted on by JP86

Hello,

I currently have a form datacardvalue which can be used as text or numeric when creating a new project entry. When displaying the data on the view form display I would like to format the data if it is numeric, if it's alphanumeric then leave it as is. I thought my current function would work but It keeps giving me an error (Invalid argument type, Expecting a number value. Location DataCardValue26.Value)

If(!IsNumeric(Parent.Default), Parent.Default, "$" & If(Parent.Default > 999, Text(Parent.Default, "[$-en-US]0,000.00"), Text(Parent.Default, "[$-en-US]0.00")))

If I change the false value to "no", the function works

If(!IsNumeric(Parent.Default), Parent.Default, "no")

when I use the numeric formatting on it's on for a number it works as well. Is there a reason why I can't nest the number formatting if statement in?

Categories:

Building Power Apps

All responses (9)

Answers (1)

Sort by

WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!
HI @JP86 ,

I am actually surprised #2 works as you are testing for two different types of data in the underlying data field and there is only one possibility (whatever the type of field is). What type of field is in Parent.Default? If it is Numeric, then the first test is redundant (it will always be numeric). If it is Text (but can be formatted as a number) then

If( Value(Parent.Default)>0, "$" & If( Value(Parent.Default) > 999, Text( Value(Parent.Default), "[$-en-US]0,000.00" ), Text( Value(Parent.Default), "[$-en-US]0.00" ) ), Parent.Default, )

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.
v-xiaochen-msft Microsoft Employee on at

Like
a
(0)

Report
Copy link

Link copied!
Hi @JP86 ,

Please try this code

If(!IsNumeric(Parent.Default), Parent.Default, Parent.Default > 999, Text(Parent.Default, "[$-en-US]0,000.00"), Text(Parent.Default, "[$-en-US]0.00"))

Best Regards,
Wearsky
If my post helps, then please consider Accept it as the solution to help others. Thanks.
JP86 70 on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @WarrenBelz , Thanks for the response. You are correct, I have CDS table column set as Text, this field should be able to accept text or numeric. If it is a number, i want to format it using the numeric formatting.

Neither of the suggestions worked @WarrenBelz and @v-xiaochen-msft . I'm kinda at a lost, the only two options for this field will be T&E or a dollar value ex: 1000.00. Shouldn't the isnumeric work in this case?
JP86 70 on at

Like
a
(0)

Report
Copy link

Link copied!
@WarrenBelz I would like to add that when i use this function, it works and provides the proper response true and false based on if the data is a number (1000.00) or text (T&E).
If(IsNumeric(Parent.Default), "true", "false")

I'm assuming the error occurs when trying to format the number within the if statement.
WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

HI @JP86 ,

This is a guess as I have never tried what you are doing, but I will go back to my first comment - you are working with two different data types in the If statement and I think the authentication logic in Power Apps is struggling with that.
JP86 70 on at

Like
a
(0)

Report
Copy link

Link copied!
@WarrenBelz , interesting. Thanks for the support with this issue. Would you be able to assist with the text formatting. I can't figure out why this isn't working, I have followed the guide online and other forum posts but it doesn't format the number into currency. Am i missing something?

Text(Parent.Default, "[$-en-US]$ #,###.00")

This doesn't format the number. It just shows 1000. Does this have anything to do with the CDS table column format being Text?
JP86 70 on at

Like
a
(0)

Report
Copy link

Link copied!
Text(Value(Parent.Default), "[$-en-US]$ #,###.00")

This worked. I am going to try with my original if statement to see if i can get it working.
Verified answer

JP86 70 on at

Like
a
(0)

Report
Copy link

Link copied!
If(IsNumeric(Parent.Default), Text(Value(Parent.Default), "[$-en-US]$ #,###.00"), Parent.Default)

This seems to be working for me and doing what I need it to do. Thank you @WarrenBelz and @v-xiaochen-msft for pointing me in the right direction!
WarrenBelz 156,528 Most Valuable Professional on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks @JP86 ,

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.