Free Text or formatted number in Power Apps control

[Anonymous]

Hi Community,

I should like the option for users to post free text in a Power Apps text control, otherwise post a number formatted as a percentage.

My App is partially pre populated with text and numbers (mostly as percentages).

@WarrenBelzhelped me with a scenario to report "Not Applicable" if the SharePoint control was blank, but now our users rather want the option to type free text if no data (blank SharePoint record) is pulled into the control.

The code to show "Not Applicable" or pull through the percentage was as follows:

If(
   IsBlank(Parent.Default),
   "Not Applicable",
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ) 
)

How do I correct this permit the user post free text unto 255 characters. Thankyou!

[timl]

Hi @Anonymous 

If you modify the formula like so, it'll display empty text rather than "Not Applicable" when the value is blank.

If(
   IsBlank(Parent.Default),
   "",
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ) 
)

You can then set the 'maximum length' property of the text input control to restrict the input value to 255 characters.

[Anonymous]

Hi @timl,  Thanks for your input. In fact where there is no data in the control, users want to be able to add free text (upto 255 characters) or add a number formatted as a percentage. The formula you have added really maintains the cell as blank or a number percentage. How do I give the option to add text or a number formatted as a percentage?

Thanks

[timl]

Hi @Anonymous 

I don't think I fully understand what you're getting at here. In the case where the user enters a number, do you want some 'input mask' to appear in the text input control, and/or do you want the value to be formatted with the % symbol as soon as the focus leaves the control?

[Anonymous]

Hi @timl 

SharePoint columns( in some instances) have been pre-populated with numbers. These were originally formatted as percentages. The need to arose to adjust the formatting to include text in addition to numbers formatted as percentages.

Records in SP (ie numbers or blanks) are pulled into certain controls in the Power App. Image below ( red question marks where blanks or number can be pulled in from SP):

If a number is pulled in, it must be formatted in the app as a percentage (as in the case of Attendance, Retention or Achievement)

Alternatively, if a blank is pulled into those same controls, the user is required to add free text (<255 characters)

Our efforts so far only offer the user the option to insert a number (formatted a percentage) or "Not Applicable" or retain a blank.

Users need to free text or provide a number formatted as percentage. If the number already exists, users will use the record for observation purposes only. If a blank is pulled  into the control (via the gallery), users need to be able to free text and have the edit form submit that free text back to SP.

Hope that makes sense. 

[Anonymous]

Hi @timl The problem I'm having if I go with your solution, even if I set Max Length to 255

When I attempt to type anything other than a number, the input control 'Achievement' doesn't permit it. If you are saying it should work, what am I doing wrong?

[timl]

Hi @Anonymous 

Thanks for clarifying that.

When loading a record, formula beneath will format the value as a percentage if the underlying value is a number. Otherwise, it will set it to the text value.

If(
   IsNumeric(Parent.Default)
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ),
   Value(Parent.Default)
)

If you still want it to display "not applicable" when blank, the formula would look like this.

If(
   IsBlank(Parent.Default),
   "Not Applicable",
   IsNumeric(Parent.Default)
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ),
   Value(Parent.Default)
)

With regards to the text input control not accepting a number, can you confirm the format property of the control? Is it set the 'Text'?

[Anonymous]

Thanks for getting back to me on this @timl , I was about to abandon the notion of a solution - at least for a season until MS come up with a workable solution. I'll give everyone a heads up here, in my labours to test your previous options I have uncovered another error that is related, but have reported elsewhere (I had been advised by another super user not overload each query with multiple questions and asks. That said, if it will help anyone reading this understand better about the scope of my endeavours, a link is here. ) Now back to answer your questions...

---

@timl wrote:

Hi @Anonymous 

Thanks for clarifying that.

When loading a record, formula beneath will format the value as a percentage if the underlying value is a number. Otherwise, it will set it to the text value.

If(
   IsNumeric(Parent.Default)
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ),
   Value(Parent.Default)
)

If you still want it to display "not applicable" when blank, the formula would look like this.

If(
   IsBlank(Parent.Default),
   "Not Applicable",
   IsNumeric(Parent.Default)
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ),
   Value(Parent.Default)
)

With regards to the text input control not accepting a number, can you confirm the format property of the control? Is it set the 'Text'?

The control I have used the above control "Percentage_Test" for test purposes only. I set it to Single line text on SP.

Another issue I have discovered. When I changed the columns (scoped to accept text and %) from Number to Single line Text, while there was no loss of data incurred (something SP warns you against), I lose the %.  For eg 78% now appears as 0.77.

It seems to me then, please correct me if I am wrong, that what you gain on the app by offering the option to enter data as text or %, you lose it at the SP end.

---

[timl]

@Anonymous - that's really wierd if 'format' is set to 'text' and it doesn't enable you to type anything other than a number.

I'd try refreshing the data source in the data panel and recreating the card from the 'fields' link of the form property. Maybe that might help.

[Anonymous]

@timl The problem occurs when I change from one format to another. I get the warning.. see screen shot...

If I refresh the app

It patches any text into SP

Then when I change back to number...

It patches without any further issue (but note it doesn't automatically put the % in the control.)

So now I have 2 issues. Some of the data is showing percenatges like this 0.78

While the test control is showing 99 without the %, until I actually add %.

Then the server message reappears

If I refresh it again, this happens

Then 9900% automatically appears in the control

However, now it lets me patch the data correctly:

Now if I change the entry to text again...

and the cycle continues..

That above however is using your original code;

If(
   IsBlank(Parent.Default),
   "",
   Text( 
      Value(Parent.Default) * 100, 
      "#%" 
   ) 
)

I'm going to try your modified code now.....