Calculated column formula vs Powerapps form formula

Copper Contributor

Hi,

I have a calculated column that is automatically assigning a classification based on a number series

AS = 1-1999

QX = 2000-2999

OO = 3000-3999

=IF(ISBLANK([Flight #]),"",IF(VALUE([Flight #])>=3000,"OO",IF(VALUE([Flight #])>=2000,"QX",IF(VALUE([Flight #])>=1,"AS",""))))

The formula is working great but I need to allow the user to make an entry when there is a series 9000 number entered becuase that number could belong to AS or OO.

 

I thought I could create a Choice Column with the three choices listed above and use that same formula, tweaked for the datacardvalue of the text field that houses the numbers, but that formula does not work for me:

If(ISBLANK(DataCardValue1),"",If(VALUE(DataCardValue1)>=3000,"OO";If(VALUE(DataCardValue1)>=2000,"QX";If(VALUE(DataCardValue1)<2000,"AS",""))))

 

What do I need to do to modify that formula, I have tried different things and nothing seems to work for me?

 

Any help will be greatly appreciated

 

Thanks 

REn

 

 

2 Replies

@RenPNW the logic of your formula is wrong; the last part of it says "IF(VALUE(datacardvalue1)>=1,"AS" but all values will be greater than or equal to 1. So the very first thing you need to do is to change that last part of the formula to <2000.

 

But then how are you going to decide whether a 9000 number is assigned AS or OO?

 

Rob
Los Gallardos
Microsoft Power Automate Community Super User.
Principal Consultant, SharePoint and Power Platform WSP Global (and classic 1967 Morris Traveller driver)

Thanks Rob,
I will try that change.
That is the reason that I changed to a choice box so that it can be manually entered for 9000 series.