Tutorial

Time and Date Functions (1/2)

SharePoint is not just a document repository, it is also meant to house information and encourage collaboration and drive projects forward. One of the key aspects in driving projects forward is measuring where they are and the progress they have made. Infowise Ultimate Forms Suite offers formula functionality above and beyond out of the box SharePoint that makes reporting on projects a breeze.

Instructions

Infowise offers the following functions for working with Date and Time fields:

Year(DateTime)

Month(DateTime)

Day(DateTime)

Weekday(DateTime)

Hour(DateTime)

Minute(DateTime)

WeekNumber(DateTime)

Today

AddDate(DateTime|Amount|Unit)

ToDate(String)

Days(DateTime|DateTime)

Hours(DateTime|DateTime)

Minutes(DateTime|DateTime)

Seconds(DateTime|DateTime)

 

Lets quickly discuss what some of the simpler formulas in this list accomplish. The Year, Month, Day, Weekday, Hour, Minute, WeekNumber all accept DateTime as their input and parse out a specific portion of the DateTime input.

So for this example, we will start with the Office 365 version of Infowise and a large Projects list that we have been using for years. We are just now looking to do some reporting on the library. It would be easier to report on these values if the specifics of the time and date were separated out. Luckily for us, we can use some simple Infowise functions to achieve this for our entire list at once!

 

Our first step is to create new columns that our formulas will be able to populate to. So navigate to your List Settings page for your list and create the following columns as Single line of text fields:

Year

Day

Hour

Minute

Weekeday

WeekNumber

Month

 

Order does not matter, but when you are done your table should look something like below.

Our next step will be to build the Actions that calculate the columns. Navigate to your Infowise Design Screen and then click Actions under the Configure Business Logic section to start the setup.

Leave the Action Type as Update List Item and give a descriptive Name in the next field.

The Run on Events section at the bottom of our form is one of the most important aspects of our Action. This is where we specify our trigger and can set up such things as timer-based actions for nightly reports or have actions run on triggers such as an item being added or deleted.

Since we are testing these functions, we want to be able to manually trigger these actions, so uncheck each of the boxes in the Run on Event section and navigate to the Advanced Settings tab.

In the Advanced Settings tab, select Execute action through context menu or ribbon. Infowise will allow you to specify a button icon and specific permissions below if you would like. In this instance, we can leave them as their default.

 Next move onto the Action Settings tab. Here, in the Values to set section, is where we will input our formulas.

First, select the column you want to output to in the Values to set section. Next, input the corresponding formula using the $ to denote the Infowise keyword. Specify the value you wish to convert like so: ([VALUE]).

 

Do this for each of your columns using the formulas below:

$Hour([Project Start])

$Minute([Project Start])

$Month([Project Start])

$Year([Project Start])

$Day([Project Start])

$Weekday([Project Start])

$WeekNumber([Project Start])

 

When finished, your Actions tab should look something like the below image:

Since we are manually executing this action we can ignore the Conditions tab. Save you action and navigate back to the list to test the functionality. Our list looks similar to before.

Let's test running our newly created action by selecting all of our records and running our Time and Date Function action from the ribbon above.

Once you click the ribbon, a pop-up window will appear and offer you the various actions for this list. Click the Time and Date Functions action we just created. 

Since there are multiple calculations being made at once, you will most likely see a loading screen similar to the one below whilst the values are being calculated.

 

When Infowise is done calculating, it will generate a Success message for each record as shown below.

Now let's take a look at our list to see what effect the formulas had.

We could easily turn these outputs into meaningful reports to help us understand such things as when our busiest weeks or months are. Keep in mind that any DateTime field can be reported on so both the Created and Modified fields can be used as well.

 

 

 

Now that we have seen how to work with these functions in the Office 365 version of Infowise, let's take a look at the differences between this and the on-premise Infowise product. Similar to Office 365, we will start by creating a column for each formula we would like to test in the List Settings of our list. Next, we need to navigate to the Infowise Design Screen for our list. Here we will see our first difference. As shown below, on-premise Infowise requires that you create an Action column for actions to be performed.

Name your column Actions and click the OK button to the right to add it to your list.

 

Now move onto the Add/Update Action section. Leave the Action Type as Update List Item, give your action a descriptive title and uncheck the boxes below for the Run on events.

Now move onto the Advanced Settings tab where things are a bit different from the Office 365 version. As you can see, we need to specify the action column in the Column name drop-down. The default value for the Execution drop-down is Always, but we want to select the Show as column option so that we can manually run the action and test our functions.

Once Show as column is selected, the section will change to offer us more options. Check the Add to context menu and Add ribbon button boxes.

Next move onto the Action Settings tab which is similar to the one seen in Office 365. Input each function with the corresponding column. At the end, your Action Settings tab should look like below.

Next, we need to navigate back to our list to test this functionality. We can see that as expected our columns are still blank.

 

We added a button to the ribbon to manually execute our action. So select some amount of records and click the Items tab above where the icon we created should be. Click the icon to execute the action.

 

Infowise will take some time to calculate the columns in the background and when finished we should have a list that looks similar to the below image.

 

Summary

In this example, we discussed how to set up and utilize the simple functions for parsing out date information provided by the Infowise Forms Ultimate Suite package. SharePoint is used as an information repository and often times we look back at a list or library and wish to extract date information. Infowise Ultimate Forms Suite makes it easy to extract important information.

Last modified: 9/1/2017 5:56 PM
Be the first to add a comment to this article
 
Comments are not designed to replace support calls. If you have a specific issue with one of our products, please send an email to support@infowisesolutions.com to open a support ticket.