Custom format for new form entries

[Talles_Lessa]

Here is my scenario, I want to record a drivers license ID, which in my country has the following format :

###.###.###-##

Right now I am saving it as a text column because I want to keep the dots ans dashes (and don.t want that +/- option associated with number columns) However I have to manually input the dots and dashes to have them saved in the DB with the right format.

But I want it to input in the form just the sequence of numbers i.e: 66688899900 and have it saved in the DB as 666.888.999-00. Is there a way to do so?

I have been trying to use concatenate to create a text expression but I cant make it work

CONCATENATE(
MID([DriversID],1,3),".",MID([DriversID],4,3),".",MID([DriversID],7,3),"-",MID([DriversID],10,2)
)

This gives me the right output however I don`t know where to set it up in order for it to get the values from a form that is being filled.

Any clues on how to do that ?

[Steve · Accepted solution]

You’d either need to:

1. Add another normal (not virtual) column with an App formula expression to produce the desired format when the row is first added and every time the row is updated, then use that column’s value in the database where you need the formatted value.

   OR

2. Create an action of type Data: set the values of some columns of this row to replace the user-entered value with its formatted equivalent, then attach that action as the Form Saved event action of the form so that it applies the formatting when the row is first added through the form and each time the row is updated in the form.

(1) is the easiest and most robust, but requires an extra column.

[Talles_Lessa]

Thank you so much !