Number Formatting

[Matthew Carpenter]

I do not know if this is possible or not but I am going to throw it out there to see what happens.
I created my DB to track my students and assist them with their professional licensing process.
One of the pieces of data I maintain is their Social Security Number.
Because I am new to this DB thing I made the field a number (because it’s a number)

Is there a way to make it a custom format?
Optimally I would like the output on my form to look like this…

## – ### – ####

[Brendan]

Hi Matthew,

I don’t have custom formatters for Number fields at the moment. But it’s something I’d like to add. I do have it for Date fields, but just not Number fields yet.

Thanks,

Brendan

[Glen Forister]

Is it possible to create 3 fields with a part in each field and then another filed that would concatenate those 3 fields with a – between them?

[Matthew Carpenter]

Glen that is brilliant! The only issue I have with your suggestion is that I already have over 300 entries into my DB. Can it be done? Yes it can. That is a project may have to consider.

[Matthew Carpenter]

Brendan I would love to see that as part of the next update if at all possible please. I have a DB with over 300 established records. I like the solution that Glen has offered but I do not have the luxury of time in my side right now.

I an very new to DB creation and maintenance. I have used several others that were free but they were not stabile and crashed often. This is why I opted to pay for TapForms, stability.
I am pleased to report that I have not had on issue to report to TapForms and very comfortable with my decision to migrate my data into TapForms.

BTW Brendan, I know I have hit this forum with several needy things. Thank you for stepping up to answer EVERY time. I am grateful.

[Glen Forister]

Do you have the Social Security Numbers in a spreadsheet already? If so, you could export your TapForms database to a spreadsheet, add the numbers, import back to a new TapForms database, at that time you will have your 3 extra filds filled in and you just have to add one field that concatenates the there into the format you want.

[Brendan]

You could actually parse the SIN numbers from one field, then format them using the script to display in another field.

Here’s code to do that. Make a new field and set the Field Type to Script. Paste in this code:

function formatSocialSecurity(val){
	val = val.replace(/\D/g, '');
	val = val.replace(/^(\d{3})/, '$1-');
	val = val.replace(/-(\d{2})/, '-$1-');
	val = val.replace(/(\d)-(\d{4}).*/, '$1-$2');
	return val;
}  		

function Formatted_Sin() {

	var sin = record.getFieldValue('fld-4289784906dd4822ab1b738c404ca783');
	console.log(sin);
	
	if (sin != undefined) {
		return formatSocialSecurity(String(sin));//"135-71-1458"
	} else {
		return nil;
	}
}

Formatted_Sin();

I didn’t make up the formatSocialSecurity() function. I Googled for it.

You would have to replace your Social Security Number field ID with the one from your own form.

The script requires the values to be Text, so this part converts it to a String: String(sin)

See if that works for you. You may need to modify the code a bit to get the right format for you.

[Brendan]

Oh, and the Script must be set to return a Text result.

[Matthew Carpenter]

Good Morning Brendan!
Thank you so very much. I do not have the code working yet but I have decided to make this my puzzle for the day.
My SSN field is already set to text per the suggestion of Glen.
Once I get my code sorted out, I will post it here to show my accomplishment as a NOVICE.

You have been super approachable and helpful. I an so very grateful. Thank you again!

-Matthew.

[Matthew Carpenter]

I FREAKING GOT IT!!!
I tweaked the code and also went to a few tutorials on JAVA in YouTube to find my errors.

THE OUTPUT ON THE FORM IS PERFECT!!!!

Thank you so very much!

Now I only have to edit my form and make it pretty!

Here is the code I ultimatly went with;

function formatSocialSecurity(val){
	val = val.replace(/\D/g, '');
	val = val.replace(/^(\d{3})/, '$1-');
	val = val.replace(/-(\d{2})/, '-$1-');
	val = val.replace(/(\d)-(\d{4}).*/, '$1-$2');
	return val;
}  		

function Formatted_Sin() {

	var sin = 
record.getFieldValue('fld-acb6f0bd72854f81952fe16c83756afb');
;
	console.log(sin);
	
	if (sin != undefined) {
		return formatSocialSecurity(String(sin));//"135-71-1458"
	} else {
		return nil;
	}
}

Formatted_Sin();

• This reply was modified 1 year, 11 months ago by Matthew Carpenter.
• This reply was modified 1 year, 11 months ago by Brendan.

Attachments:

You must be logged in to view attached files.

[Brendan]

That’s awesome Matthew!

I hope you don’t mind but I edited your post to add in the back-tick characters surrounding your code so the website formats it as code.

Thanks!

Brendan

[Matthew Carpenter]

Totally fine with the edit.
Thank you again for helping me and being so available.

I hope others are able to use this as well.

[Brendan]

Now I just need to get off my ass and write the code to let you customize the number formatter :)

[Daniel Leu]

Since SSN should to be protected, it might be good to show them as ###-##-#### per default and only display the real numbers when clicking on the field. Maybe something similar to what you do with passwords. Just a thought.

[Matthew Carpenter]

Good Morning Daniel,

I like that idea if I ever choose to share my database. Maybe that is something Brendan may want to consider for the next update?

For now my DB is only for the eyes of my supervisor and myself (from my personal MacBook). My supervisor doesn’t even have access to the DB without my presence.

If I share with my supervisor, she sees it on a huge monitor that is cabled directly to my machine that is password and fingerprint protected.

-Matthew.

[Matthew Carpenter]

I’m learning!

Okay so I took the old code and tweaked it to fit another line of data. The format does not need to change because it is simply a Student ID Number. I got it to spit out the same number but as a script. The scripted output is now my form and cannot be edited from the custom form.

This is what I did;

function formatStuID(val){

	val = val.replace(/(\d)-(\d{4}).*/, '$1-$2');
	return val;
}  		

function Formatted_Sin() {

	var sin = 
record.getFieldValue('fld-66039384e7ac46f0aecb546f5e8c4ac9');
	console.log(sin);
	
	if (sin != undefined) {
		return formatStuID(String(sin));//"1234567890"
	} else {
		return nil;
	}
}

Formatted_Sin();

BAM!!! Protected form value!

• This reply was modified 1 year, 11 months ago by Matthew Carpenter.

[Brendan]

I suppose something similar to the Mask Field Value thing would work. But I’ve got so many properties on the Field object in my database it’s getting very bloated. I literally have over 100 properties on my Field object now. There’s so many different settings for fields. :)

[Matthew Carpenter]

I know this may sound cheesy but I am just having fun and learning at this point.
I went to my local college earlier today. I think I am going to take this coding thing a little more seriously.

Look at me! More than half a century on the earth and my silly ass is going back to school. What the heck am I thinking???

[Brendan]

That’s awesome! You’re never too old to learn new things. I only just recently, in the past few years, learned how to ride mountain bikes. I hadn’t ridden a bike in over 30 years until then. I remember those first days when I was scared going down a relatively gradual hill. Now I fly down them and ride in the mountains and do drops and jumps! And I’m over 50 too!

[Author]

Posts