Formatting Text with DriveWorks

Pop quiz: Jenny just gave you her phone number, which way is easier to read? (555) 867-5309 or 5558675309? If you want any way of remembering this, you probably said the first option (or just remembered the song). So how does this work with DriveWorks? You probably don’t need an easy way of remembering numbers, but ERP systems need properly formatted data. I’ve written before about how DriveWorks can integrate with other systems. So today, let’s talk about formatting text with DriveWorks to get any result you want.

Why Formatting Matters

Some people don’t care about the formatting of their text. That’s not a big deal. However, I mentioned earlier that your systems might care. Or, what if you have customers entering data and you want to make it easier for them?

This data lives in different formats. Email addresses need to have a username and a domain. Without either of those things, you don’t have a valid email address. It might make sense to have someone enter an email address and validate it with DriveWorks rules.

Numeric values are another subset of formats. If someone gave me 10000000, it would take a second to know what I’m looking at. Compare that to 10,000,000 and it’s easy to see the difference. So, with the groundwork set, let’s see how DriveWorks can help us.

Validate Formatting

DriveWorks has some pretty sweet validation rules. If you haven’t checked out the DriveWorks Tech Stack, they have a nice section about validation rules. Validation rules ensure your formatted text is what you want it to be. Here’s just a sneak peak of what you will see.

You can validate information like file names, folder paths, and email addresses. This is important because if a customer gives us an email like the last one, our CRM system likely won’t accept the values. It also means that we won’t have invalid file and folder names, ensuring that our files will save correctly.

Use DriveWorks to Format for You

One function that doesn’t get much attention is TEXT. TEXT lets you format data as it comes in. That’s a great way you can format things like percentages, dates, and currency. The TEXT function receives your inputs and formats according to the format you set up.

But sometimes data needs to be a specific type to go into your system. A great example is a SQL database. SQL lists each field as some kind of input. This might be something like a character value (char) or numeric (int). If your users aren’t providing the data in the exact format you want, you’ll notice data not populating in your tables. One great way around this is to use functions such as STRING or NUMBER, which will transform your data into another format.

Formatting Phone Numbers

Recently, some of the DriveWorks guys at InFlow were trying to figure this problem out. How do you format a phone number using DriveWorks 18? Simple, we said, it’s just formatting text with DriveWorks, nothing crazy! Little did we know, there’s a catch with how the text function works. Turns out, if the number it’s working with exceeds 999,999,999, the formatting goes out the

window. So now what do we do? Well, there’s more than just “TEXT” at our disposal.

In order to make the formatting work, we had to separate the text. Phone numbers are typically (123) 456-7890, so that’s doable. The Left and Right functions let you grab sections of text and use that instead of the full length. And by using those, along with text, this was the result:

Dialing it in

We all know that formatting is important. It’s how we keep track of information. Not only that, it’s how our systems track information as well. Now we can port our data into any system. Whether that’s SQL, Excel, or anything else, we’re ready to add information. And better than that, clean and accurate formatting ensures a great experience for our customers.