Solved: Data Validation - MAC Address

Erik_Meyer · 02-03-2020 09:33 AM

Hello,

I have an app where my installers will need to enter the MAC address for a device they are installing. I am looking for assistance in validating that the data entered is a valid MAC address.

The MAC field they are using is setup as a text type.

MAC address must contain exactly 12 characters, where each character is equal to 0-9 or a-f.

Additional characters may be entered as separators, eg aa.00.bb… or 00:de:9a….

What I would like to have happen is this:

User enters data
System looks at characters and extracts valid characters up to 12 leftmost found.
3a. If 12 valid characters, system reformats the data to be aa:bb:cc… dropping any invalid characters and performing a ToLower() on the alpha characters.
3b. If cannot find 12 valid characters, then an error is generated and the data entered is removed.

TIA for any assistance you may be able to provide.

PS - Secondary question: is there any way to get a UPC or QR code reader data to be entered directly into the app in order to optimize data collection?

MultiTech

I’ve got it all inside a sample app:
https://www.appsheet.com/samples/An-app-to-autoformat-and-verify-MAC-address-entries?appGuidString=a...

View solution in original post

Steve

AppSheet cannot do this.

Erik_Meyer

@Steve do you mean that AS can’t parse text, or that it can’t manipulate entered data? Or something else?

Steve

It’s parsing capabilities are extremely limited. For instance: no regular expressions.

Bahbus

Parsing generally (though I think @Aleksi has shown not always) requires a minimum of 2 columns. One to act as input and another to do all the parsing. For example, the easiest way to do it, would be to have the user enter just the 12 characters without any special characters. Then in the parsing column you would use LOWER() as well as insert the colons, and then you would only display this column in the future. You could take some other routes as well to accomplish the same thing.

Erik_Meyer

Thanks. What other ideas do you have, can you explain in more detail?

I am specifically looking to validate the data entered. Reformatting the final data is easy enough with the included parsing functions. So I need to be able to evaluate each part of the MAC and make sure it represents a valid 2-byte hex value.

Bahbus

I’m not entire sure how to pull it off, or if you can even craft it to do want you want it to. But I’ve seen examples of using [_THIS] in the Valid If to attempt to validate as you enter. I just don’t have any good ideas on how to use it in a way that could be useful. @Aleksi is surely the man for that kind of job.

Steve

The best I’ve been able to come up with is to do the validation at your data source. For instance, add a column to your spreadsheet with a formula that does the validation and outputs either the valid formatted address, or an error Indicator (#INVALID, e.g.). The downside of this approach is it occurs as part of a sync, so wouldn’t be available as data is entered into the form, and wouldn’t be available at all off-line.

MultiTech

If I’m understanding this correctly, you basically want to eliminate the need for someone to put in all the colons and such when entering the MAC address?

You would need to use a secondary column, one that uses a formula to derive it’s value:

This is just a matter of a bunnch of left() and Right() formulas nested inside each other… let me chew on it.

-----------------Edit-----------------
Okay, and the validations… the alphabet restrictions are…

MultiTech

I’ve got it all inside a sample app:
https://www.appsheet.com/samples/An-app-to-autoformat-and-verify-MAC-address-entries?appGuidString=a...

Erik_Meyer

Thanks @MultiTech_Visions. I will peruse that application and see if I can make it work for me.

Thanks for that, I wasn’t asking anyone to do the work for me. Your time and effort is appreciated!

MultiTech

I enjoy tough problems like this, it’s like a brain teaser for me.

A little of this…

Some of that…

it’s an easy way to get into a flow-state. One thing after another…

You’re welcome!

Erik_Meyer

@MultiTech_Visions I’d like to know how you made that a Sample App and got a link to share it in the forum post. I tried and failed to do it that way.

That said, I sent you an email invite for my reworked version. If you feel at all like looking at/trying to break my app, have at it.

Differences are that rather than restrict the users input, I have sheared away any invalid characters and tried to make a full mac from the parsed data. I can see my users copying/pasting in MAC addresses from other apps (and in various formats) to fill in this data so wanted to cover those angles also. I used one additional column (MAC F2) for working on the data, then put the final value into MAC Work. I changed the valid_if on the data entry field to reference MAC Work to make sure we have valid data so the user can save.

Formats that might be pasted in include:

00.55.da.40.37.8b dotted separators
00 55 da 40 37 9c space separators
00:55:DA:40:37:AD colon separators
005567123456 no separators

MultiTech

Nicely done.

How to make an app a sample app

To make something a sample app, there’s a setting inside the app:

Then this sample will be available on your AppSheet public profile (even if you’re not officially part of anything, you still have a public profile - it’s just no one sees it). But you can get a link to it:

Then you can grab the URL of the sample app:

https://p56.f1.n0.cdn.getcloudapp.com/items/z8unzgeK/Screen+Recording+2020-02-04+at+04.16.31.02+PM.g...

To configure what the sample app page says, there’s a place inside the app editor to specify this stuff:

PS: If you wish for someone to take a look at the back end of your app, you’ll need to add them as a colaborator.

Erik_Meyer

I tried changing your permissions to “Can View Definition”. Does that work for you to see my expressions?

MultiTech

Sure did. Nice!