Converting formula to column

When you import your data there is often a necessity to process imported value in some way. For example, to divide a name value into two fields http://blog.teamdesk.net/2007/09/using-formula-c.html). Such operations cannot be performed at primary importing; however, it is possible to solve this task with the help of formulas.

Using of formulas can be very helpful while importing files into columns. To convert imported values into a necessary format, do the following:

Import a field value in the initial format;

Make up a formula to perform a necessary transformation. After a transformation has been performed, the column will contain a desired value.

Convert column of formula type to a regular column of the same type (for example, convert Formula-User column type to the User column type).

Delete the column with initially imported values.

Example1:

Say you have the Name column where you store the first and last names of your clients in a simple text format. However for some future manipulations you need to store them in two columns: First Name and Last Name. Basically you should do just the following:

Create a formula – text column called First Name. The formula should be Left([Name], " ")

Create a formula – text column called Last Name. The formula should be NotLeft([Name], " ")

Thus, data Thus, data from the Name column is duplicated and divided into 2 additional fields - First Name and Last Name. When two new columns are ready, check if views and reports represent data correctly and in the way you need.

Change type of the First Name and Last Name columns into regular text column. At this step all data taken from the original Name column are recorded into the newly created columns.

Now if necessary, you can change type of the Name column: make it a formula - text column. The formula should be: List(" ",[First Name],[Last Name]).

Example 2:

We need to process an imported value of the Duration type. It is not possible to get a desired result right after importing, as we do not know the measurement units (days, hours, minutes, seconds). The next steps should be performed:

Import a field into a column of the Numeric type.

Make up a formula that processes an imported value, for example Days([Field]), Hours([Field]), Minutes([Field]), Seconds([Field]).

Convert a received Formula-Duration column type into a simple Duration type.

Delete a column with initially imported values.

Next: Changing Column Type