Adding Records into Database

[RMittelman]

Noob questions about defining forms and adding records.

I have 2 phone number fields, PrimaryPhone and SecondaryPhone. I would like to store them as raw numbers: 8052317500, rather than with parentheses and dashes. I tried defining them as integer because I want only numbers, but integer won’t hold a 10-digit US phone number. So I had to change the types to text and now it works.

So is that the correct field type? How would I validate that it is numeric? If there was a long integer type, I could maybe use that. Do I need to write a function to validate it?
If so, any suggestions on the code of the function?

I’d also like to display it formatted: (805) 231-7500, after leaving the field. How would I accomplish that? If it requires code, should I do that in the same function that would validate it being numeric?

Thanks for any help…

[Pranoy]

Hi RMittelman

You can keep the field as an integer. When you define a field using the Form wizard in Five, it typically assigns a default size for storage in your database. The average size for an integer field is 4 bytes. You can redefine these sizes to store higher values by following these steps:

1. Click on ‘Data’ → ‘Tables’ to see a list of all tables defined in your database.
2. Select the table containing your phone number fields and navigate to the ‘Fields’ tab.
3. Choose the phone number fields and assign them a larger size. I tried using 10 and it was able to hold 8052317500 as a value
   
   

To display the fields in a formatted manner, you are correct: it would require a function. You can develop your logic and link it to the ‘on Validate’ event for each field. To locate this event, you can

1. Go to ‘Forms’.
2. Navigate to ‘Pages’ and select the general page of the form you wish to edit.
3. You’ll see all the fields listed here. Click on any field, then select the ‘Events’ tab to view the available events for that field. The ‘on Validate’ event will be visible here."
   
   

Let me know if this helps or if you have any other questions.

[RMittelman]

Thanks for the answer Pranoy. I didn’t realize it would work to change the field size. I am used to things like “integer” or “long integer”. I believe if I changed the field back to “integer” with a larger size, it would automatically warn if text characters were entered, right?

Regarding the function… I am a bit familiar with calling functions from the events of the fields. What I’m not familiar with is what I would do in this particular function. This is a different use-case than I am familiar with. In my experience with .Net and Access, you can apply a format mask to a field, and when you enter the field the formatting goes away, leaving it as numbers only. Then when you leave the field it would re-display using that format.

I don’t really want to change the value of the field. I want to store it as raw numbers. So if the on validate function changed it to a formatted phone, such as (805) 231-7500 it would be stored that way in the database, right? If I do that, I would need to keep the field as text to handle the formatting. Then I suppose I could do a function in the On Enter event to remove the formatting, then another function to re-format it from the On Validate function. This seems a bit cumbersome. Is that the correct way to accomplish what I want? Thanks again…

[Jo-Anne]

Hi RMittelman,

You could also create a display type and give it a mask, like in the image below.

Write one small function that would replace all non-numeric characters including spaces with nothing.

function OnSaveRecord(five, context, result) {
context.new.Phone = context.new.Phone.replace(/\D/g,‘’);
return five.success(result);
}

Then at the table-level increase the size to 8.

At the table-level add the function to both of the events Do Before Insert and Do Before Update.

Don’t forget to attach your display type to your field.

You can test this by running your app.

If you want to check what gets stored in the database, go to the Queries view and open the SQL editor by clicking in the Query field followed by the SQL tab and type a SELECT * statement from your table.

Please remember because it is an integer data type, if your phone number starts with 0 it will be truncated/removed as it is being treated as a number.

[RMittelman]

Thanks Jo-Anne.
I tried your suggestions, but I can’t get past saving the table. I was able to change the 2 fields to integer 8. But when saving the table, I get “Failed to execute SQL : Error 1264: Out of range value for column ‘PrimaryPhone’ at row 1”. I deleted the 2 test records and still same thing.

[Jo-Anne]

Hi RMittelman,

I have reproduced your scenario, the largest 32 bit integer you can currently store is 2147483647, for now you can just store the number as text and the solution with the display type above, will only store a textual number in the database (the OnSaveRecord function will ensure this).

Going forward we will support 64 bit integers which will allow a much larger number to be stored.

[RMittelman]

Thanks for the suggestion. I have replaced the field types with Text.

I want to calculate a field called VerboseEmail, which should end up looking like:
“Ron Mittelman <rmittelman@gmail.com>”. Since it’s dependent on name and email, I thought I could add this to my CalcName function, since it also uses the name fields to calculate the FullName and SortName fields.

I modified the code to include updating the VerboseEmail field, but it doesn’t work. I can’t figure out why, the screen fields are complete. It looks like everything is defined properly. I’m uploading my project just in case you need to look at it.

I had tried to create a CalcEmail function, but since the verbose email depend on the names, and I couldn’t figure out how to call 2 different functions in On Validate, I just added the code to CalcName function and code. But now I can’t delete the CalcEmail function or process. It keeps giving me an error.

Thanks…

BrandeisConejo-20240305-2256362710181.fdf (3.3 MB)

[Jo-Anne]

Hi RMittelman,

You just have to add one extra line in your code, your function CalcName is correct and can be used for your VerboseEmail field, you just have to assign the value you have created to the VerboseEmail field using the field property on the Five object:

five.field.VerboseEmail = eml;

Add this line just before return five.success(result);

five.field.VerboseEmail = eml;

return five.success(result);

You already have it attached in the right place.

[RMittelman]

SO obvious when someone with a full brain explains it. Thanks…

[Jo-Anne]

You are doing a great job in Five!