How do I make a column such that users can add values only in a specified (Custom) format

Hello Honeycode Community,

How do I make a column that allows users to values only in a specified format? In other words, suppose I have a column named 'Device Number'. The format of that column should be '12345-67890-41257'. While the user enters a new value in a form, the column should accept only 15 digits and also in a specified format. ('-' should be automatically added after ever five digits.)

Any suggestions, how do I achieve this?

Thanks!

1 Like

This answer seems to be helpful. (Data Modelling and Validation: Table Column Width). Definitely can restrict the length of the characters in the column with the LEN() function and Visibility of the button. I am working on that.
In my case, I can lock the length of the characters = 17. (15 digits + 2 dashes)

There are two problems with this.

  1. I can still enter the same 'Device Number' in multiple ways. Like,
    '123-4567890412-57' or maybe '1234567890-4-1257'. Which is not an acceptable format.

  2. If there are no dashes in between the digits, the system converts the digits into the scientific format. (If I add 17 digits only and no dashes. e.g. '12345678904125700')

I believe the solution for both of the problems is to force the format on the column. The column should ONLY accept the '12345-67890-41257'.

Please suggest if this is achievable.

Many Thanks!!

Hi @Gaurav_Kothari,

Thanks for posting here, I'm happy to help! :slight_smile:

I just built an app that could meet your needs, in that it only accepts 15 numbers (5 in each field), and writes to a column in the format of 12345-67890-41257 as desired. Let me show you how I set it up:

I created a screen with three data cells, and in between I have two content boxes with only a dash. Each of the three data cells are editable and only accept a number format, as I've showed in the properties here:

I made a greyed out submit button that will display if the amount of entered numbers / characters in each field is not equal to 5:

In my example, using the formula: =OR((LEN($[Data1])<>5),(LEN($[Data2])<>5),(LEN($[Data3])<>5))

And the Submit button will only display if the amount of numbers is 5 in each:

Formula in my example being: =AND((LEN($[Data1])=5),(LEN($[Data2])=5),(LEN($[Data3])=5))

I also made an error message that appears if a user enters more than 5 numbers in a box:

Now for the Submit button's automation, I have an action that adds a new row to a table with all of entered data in the desired format with the CONCATENATE() function.

The formula I used in my automation was: =CONCATENATE($[Data1],"-",$[Data2],"-",$[Data3])

Now when you enter the data in the app, it will only accept 5 numbers in each field and write the entered data as desired to the table:

This shows the data entered correctly in the app:

And this is how it writes to the table when clicking the Submit button:

Also, this is what the error message looks like if someone were to enter more than 5 characters into a field:

Hope this helps you out! Let me know if it does. :slight_smile: :honeybee:

2 Likes

Hi @Alyssa

Thank you so much for the detailed response. This way, the column will strictly follow the format. We can use this method in almost anything where we need to strictly follow the format. I am also planning to implement it for one of the phone number columns I have.

Also trying to avoid duplicates with the help of this formula: (Warning visibility)

=FILTER(Devices,"Devices[Device Number]=%",$[Device Number])>0.

Here, Just need to replace $[Device Number] with CONCATENATE($[Data1]","-",$[Data2]","-"$[Data3]")

Please correct me if I am wrong.

Hi @Gaurav_Kothari,

You're welcome, I'm happy that method helps :slight_smile:

And for what you're asking, I would have a $[Device Number] variable on the screen, and have it store the value of =CONCATENATE($[Data1],"-",$[Data2],"-",$[Data3])

This is what it looks like in Builder. (You can also make the visibility of that content box that contains this variable FALSE so it doesn't show it the app):

That way for your visibility formula of the error message, you can just use $[Device Number] to call the value, as you wrote it. This is what I did for mine:

=FILTER(Serial_Numbers,"Serial_Numbers[Serial Number]=%",$[Device Number])>0

You can also update your formulas on your submit button and grey-out submit button to check if it exists as well:

Now in the app, if I enter a serial that already exists, it will return the error message and also show the grey-out submit button:

Let me know if you have any further questions on this! :slight_smile: :honeybee:

1 Like

Perfect!! This makes the 'Submit' button formula much simpler. I will go with this approach. Thank you so much for your help @Alyssa . I will keep you updated on how it goes and if I come across anything else.

1 Like

Hi @Gaurav_Kothari,

Great! :star2:

And sounds good, yes, let us know if we can help you out again in any way :smiley:

1 Like

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.