Number format issue

[KevinHardy]

Hi All,

@mdevaney  has helped me a lot with pre-populating a Sharepoint List with times (30 minutes periods fro a full 24hrs) for an app I'm building.

I set up a test app in tablet format to learn how it does it stuff.

The list is populated with 700,730,800,830 etc

The Gallery on the left shows the time slots in a format more pleasing to the eye using Text(ThisItem.Time,"[$-en-US]00:00")

However, when I display the Gallery item in the form on the right, it displays the time as 730 (as the list) rather than 07:30 and I can't seem to get it to repeat the same format. I shall keep plugging away but any suggestions would be greatfully received 🙂

[mdevaney]

Hello again @KevinHardy  😺. 

The reason you are having trouble is a Text Input inside of a form will not accept Text(ThisItem.Time,"[$-en-US]00:00") if the SharePoint column is a number type.  This is because the form wants to ensure only a number is accepted into the SharePoint list once submitted.

In your situation I would recommend a using dropdown instead of a Text Input anyways.  It is better because you can restrict the user input to only times you assign.  It does take a small bit of work but I will give directions below.

First, please take a look at the sample app screenshot.  This is what we are working towards.

#1  Delete your Text Input within the Time_DataCard (some errors willl appear but don't worry about them yet)

#2  Add a dropdown control to the Time_DataCard with the following properties

Default: Text(ThisItem.Time,"[$-en-US]00:00")

Items: ["00:00","00:30","01:00","01:30","02:00","02:30","03:00","03:30","04:00","04:30","05:00","05:30","06:00","06:30","07:00","07:30","08:00","08:30","09:00","09:30","10:00","10:30","11:00","11:30","12:00","12:30","13:00","13:30","14:00","14:30","15:00","15:30","16:00","16:30","17:00","17:30","18:00","18:30","19:00","19:30","20:00","20:30","21:00",
"21:30","22:00","22:30","23:00","23:30"]

#3  Change the Update property of Time_DataCard to this code

Value(Substitute(Dropdown_Time.Selected.Value,":",""))

#4  Look at the App Checker for any errors caused by Step #1 replacing any references to the original Text Input or deleting the code entirely.

Hopefully you enjoy this approach.  This is the full realization of what I had in mind during my original response.  Let me know.

---
Please click "Accept as Solution" if my post answered your question so that others may find it more quickly. If you found this post helpful consider giving it a "Thumbs Up."

[v-xida-msft · Accepted solution]

HI @KevinHardy ,

Could you please share a bit more about your scenario?

Based on the needs that you mentioned, I think you still also need to apply the Text(ThisItem.Time,"[$-en-US]00:00") formula to format your time value within the Time field Text Box in your Edit form.

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

Set the Default property of the Time field Text box in your Edit form to following:

Text(ThisItem.Time,"[$-en-US]00:00") 

Set the Update property of the Time field data card (which contains the Time Text box) in your Edit form to following:

Value(First(Split(TimeFieldTextBox.Text, ":")).Result) * 100 + Last(Split(TimeFieldTextBox.Text, ":")).Result

Please consider take a try with above solution, then check if the issue is solved.

Best regards,

[KevinHardy]

@mdevaney , Thank you for looking at this, but please forgive me: I saw @v-xida-msft  solution and tried that first (as it required less work 😀) and it has sorted the display issue. But I really appreciate your input. Do you get the link I sent you?

Kev

[KevinHardy]

Thank you @v-xida-msft ,

It solved the issue in a very elegant way 😀

Kev

[KevinHardy]

@mdevaney ,

I'd also been wondering whether it would have been possible to amend the original formula that populated the SP column with 700, 730, 800 by making the column Text rather than a Number and using "07:00","07:30" etc instead?

Kev

[mdevaney]

@KevinHardy
Text would have been fine as an option. The reason I chose number is because your original post said you wanted a format of “0000” (with no semi-colon). I thought this was somewhat strange but I decided to take you very literally. Now seeing that you do want a semi-colon text is likely better (although we could even use a Date & Time column SharePoint columns if date is also relevant and formatting only to see the time)

I don’t mind that you used another solution at all. The forums are all about seeing multiple perspectives and using the best one. I am just happy trying to help someone who is working to help others!

[mdevaney]

@KevinHardy
I did not receive that link from you. Was it sent to my Private Messages on the forum?

[KevinHardy]

Hi @mdevaney 

I thought I had sent it, but it turns out I get the message "You have reached the limit for number of private messages that you can send for now. Please try again later." Even though I haven't sent any other messages :/.  But the link is https://www.autismhampshire.org.uk/

Kev

[KevinHardy]

Hi @mdevaney 

I suppose part of the problem is not knowing exactly what you're asking for 😀

The 00:00 didn't occur to me straight away. But we got there in the end.

Kev