October 2016
Current page:

Create an expense report in SharePoint - step-by-step

By: Vladi Gubler | Comments [0] | Category: Products | 10/26/2016


Expense Report is one of the most common forms that almost any organization needs. You want to be able to quickly and easily report your business expenses and then route the form for approval. You would not believe how many places, even today, still use pen and paper, Excel or Word documents! Not everyone has the skills and the knowledge to build a robust modern solution for this common need.

But with SharePoint it's all so much easier. It's a great collaboration platform that is designed from the ground up to handle data and processes in shared, multi-user environment, while still preserving the necessary security restrictions. SharePoint out of the box is a great starting point for many business solutions and a lot can be done using SharePoint Designer or custom development. However, it's not always easy to build something slightly more advanced. For instance, multiple line items within the expense report or dynamic routing (when the approver is set dynamically based on the department) are certainly challenges that might have you scratching your head.

In this blog I will provide you with step-by-step instructions on how to build a real life expense report, complete with an approval stage. I will be using our product, Ultimate Forms, to add the necessary extra on top of regular SharePoint lists to make sure the process works exactly how I need it to work.

Basically, we will be implementing the following:

  1. Being able to create a new expense report. Some of my information, such as my name, will be automatically pre-filled.
  2. The line items can be added directly within the New form of the expense report, no save is necessary to start adding the line items
  3. Summary is automatically calculated based on the line items
  4. Manager for approval is automatically determined by selecting the appropriate department
  5. Manager will receive a notification in email with a link to the expense report. For extra credit we can even implement email-based approvals!
  6. Once the expense report is approved or rejected, the employee is notified.

Our finished form will look something like this:

Cool, right? :)

So let's start! I will be using the Office 365 add-in version of Ultimate Forms, but you can do exactly the same on premises as well.

Creating Lists

In SharePoint, our data is stored in lists, so we will need several lists for our Expense Report soluition.

  1. Departments
    This list will contain the various departments our employees belong to. It will then be used as a lookup source for our Department column in the Expense Reports list. We will start with a regular Custom list and just add one column of type Person or Group and call it Manager. It will hold the manager for the department

  2. Line Items
    This list will contain the line items for the expense report. We will start by create a new Custom list. To allow the parent and the child lists to create and maintain a relationship, we will need to replace the default Item content type with our special Infowise Associated Item content type. It contains a set of special hidden columns that allow us to create the relationship between the child and the parent items. To replace the content type do the following:
    1. Go to List Settings
    2. Go to Advanced Settings
    3. Choose Yes for Allow management of content types (the first option on the page)
    4. Save
    5. Under List Settings you will now see a new section for managing content types. Click on "Add from existing site content types".
    6. Select "Infowise Associated Item" content type and save
    7. Back on List Settings, click on Item content type and then delete it from the list

    Next, we will configure the list columns:
    • Rename Title to Details
    • Add a choice column Expense Type and fill th choices you want (such as Fare, Lodging, Meals, Entertainment, etc.)
    • Add a date colum Date
    • Add a currency column Amount

  3. Expense Reports
    The main list holding all the data regarding the expense reports, excluding the line items. Start by creating a new Custom list. Now let's configure the columns:
    • Rename Title to Report ID
    • Add a date column Date, you can set the default to Today
    • Using Ultimate Forms add a new User Property column named Employee Name. It will be used to automatically prefill the submitter's name. In the Office 365 add-in version, you can use the following formula: [FirstName] [LastName]. In the on-premises version you can do the same to fetch the data from SharePoint Profile service, or, if using Foundation, directly from Active Directory: [givenName] [sn]
    • Add a lookup column Department using the Departments list, make it required
    • Add a text column Location (to specify where the expenses took place)
    • Add date columns Start Date and End Date
    • Using Ultimate Forms add a new Associated Items column named Items. It will point to our Line Items list and use the Infowise Associated Items content type
    • Using Ultimate Forms add a new Associated Items Summary column named Subtotal. It will calculate a Sum of the Amount column in Items

    • Add a currency column Advance, set the default to 0
    • Add a calculated column Total, using the formula =Subtotal-Advance, set the display format to Currency
    • Add a choice column Approval Status, add choices N/a, Approved and Rejected, make it required and make sure N/a is the default
    • Add a date column Approval Date
    • Add a multiple lines of text column Approval Comments

That's it, all our lists are created, so we can start turning them into an actual business application.

Configuring Forms

Well, we actually just need to configure one form, the Expense Report itself.

First, let's configure automatic numbering for Report ID. This way each report will have a unique identifier. I used the format EXR-<submitter name>-<year>-<numerator>, but you can use any other format you like.

  1. Go to Expense Reports list
  2. On the List ribbon click on Ultimate Forms' Design button
  3. Under Configure Business Logic click on Item IDs
  4. Add a new rule for Report ID column. Set it to run on New item and check Prevent manual editing. Under Pattern enter: EXR-[Employee Name]-$Year([Created])-[#]
  5. Save

Next, let's configure the tabs and tab permissions. This will ensure users can only update the information they are allowed to.

  1. Go to Expense Reports list
  2. On the List ribbon click on Ultimate Forms' Design button
  3. Under Configure Layouts and Permissions click on Tabs and tab permissions
  4. Add a new tab Expenses
  5. Under Tab-column Association pick the following columns to appear on the tab:
    1. Employee Name
    2. Date
    3. Department
    4. Add section: Trip Details
    5. Start Date
    6. End Date
    7. Location
    8. - Blank Row -
    9. Items
    10. - Blank Row -
    11. Subtotal
    12. - New Row -
    13. Advance
    14. - New Row -
    15. Total
  6. Under Tab Permissions add the following rules:
    Permission level  For users/groups Default Apply in forms Conditions
     Write      New  
    Read       Edit;View  
     Write      Edit  Approval Status equals N/a
  7. Add a new tab Approval
  8. Underr Tab-column Association pick the following columns to appear on the tab:
    1. Approval Status
    2. Approval Date
    3. Approval Comments
  9. Under Tab Permissions add the following rules:
    Permission level For users/groups Default Apply in forms Conditions
     Deny (hidden)     New  
     Read     Edit;View  
     Write  Column: Department -> Manager    Edit  Approval Status equals N/a
  10. Under General Settings choose Modern theme, Label location: Above field, Description location: Under field and Columns: 2
  11. For extra credit, let's apply custom styling to the form to make it really stand out. You can make use of the styles described in this article. Or, in Office 365, you can now import the style from this file (right-click as Save As).


Configuring Alerts

With the forms done, now it's time to configure some alerts. We are going to be adding 2 alerts, one to inform the manager that an expense report has been submitted and the other one to inform the submitter regarding the outcome of the approval.

Alert #1

  • Go to Expense Reports list
  • On List ribbon click on Add Alert button
  • Under Recipients remove yourself from To box
  • Click the plus button to add a new recipient
  • Under Users in column select Department and then in the additional selector - Manager

  • Under What to send clear everything but Item is added
  • Under Mail Templates create your own email template, it can contain text, column values and special functions (such as Edit Item link)
  • Save the alert

Alert #2

  • Go to Expense Reports list
  • On List ribbon click on Add Alert button
  • Under Recipients remove yourself from To box
  • Click the plus button to add a new recipient
  • Under Users in column select Created By
  • Under What to send clear everything by Item is modified
  • Under Conditions add Approval Status after change not equals N/a - that will make sure the alert is only sent when an approval decision is made
  • Under Mail Templates create your own email template, it can contain text, column values and special functions
  • Save the alert

 That's it! Your Expense Report system is ready for action!


Actions Instead of Workflow in SharePoint

By: Will Cooper | Comments [0] | Category: Products | 10/21/2016

We had a great session today talking about how easy it is to set up actions instead of creating workflow in SharePoint Designer.

When it is so easy and fast to create actions using Infowise, it is hard to imagine a reason that we would want to create traditional SharePoint workflow.

If you missed out on the session today, I hope you can take some time to see just how easy it is to set up a wide variety of actions with Infowise Ultimate Forms.

We are all the time finding new ways to use these tools to create great solutions in SharePoint. I hope you find this useful.

Please drop me a note to discuss whatever is on your mind SharePoint related! willc@infowisesolutions.com 




Integrating applications using REST

By: Vladi Gubler | Comments [0] | Category: Development | 10/19/2016


In today's world it's important to make your applications talk to each other and exchange data. But in today's cloud-based world, some of the applications you use don't even belong to you! Fortunately, there is an industry-standard way of gluing the pieces together. REST is a simple but powerful protocol that modern applications expose to allow you to fetch, add, update and delete information over the web, without the need for direct programmatic access.

Today I'm going to show you how to leverage this protocol to be able to link together two applications:

  1. Your SharePoint (on-premises or in Office 365)
  2. Mailgun - an incredibly powerful, but simple mass mailing service

Basically, what we are going to do is create a mailing list in Mailgun for our partners and add the partner email to the list automatically whenever a partner is added to a Partners list in SharePoint. So next time you want to send your newsletter to partners, this new partner will already be on the list.

We are going to be using Ultimate Forms app (yes, this example is based on Office 365), but you can also use the on-premises version. We are going to be using just the Actions component, so you can also get it separately if you prefer.

  1. First, create a list of partners in your SharePoint site. It can be any type of list, as long as it contains text columns for the partner name and email.
  2. Set up your free Mailgun account here. They even allow you to send up to 10,000 emails per month for free! Mailgun with provide you with an API key that will need for the following steps. You can find the API key under Domains -> <your production domain>
  3. In Mailgun set up a mailing list called Partners, with an email such as partners@yourdomain.com
  4. From your Partners list in SharePoint click on Design button in the ribbon to enter Ultimate Forms, and then under Configure Business Logic click on Actions
  5. Select Call web service as your action type and choose New as your event.
  6. Switch to Action Settings tab
  7. Switch to REST mode under Service type.
  8. Enter the URL for the REST endpoint. It will be https://api.mailgun.net/v3/lists/<your mailling list URL>/members
  9. Under Authentication choose Credentials.
  10. Under User name enter api.
  11. Under Password enter your API key.
  12. Set Method to POST.
  13. Map paramers name and address to the columns containing your partner name and email address respectively.
  14. Save the action.

This is it, when you add your next partner to the SharePoint list, their details will be automatically added to the mailing list in Mailgun!


More design examples

By: Vladi Gubler | Comments [0] | Category: Development | 10/3/2016


UPDATE (206-11-14): you can now import styles directly through a file!

We got great response for my previous example of form design that can be easily achieved using Ultimate Forms. Today I've create a couple more designs to help our customers create great looking forms.

The first design is dark and modern:

Download the style here and then import into your list:


The second design is very pink and cheerful :)

Download this style here and then import it into your list

Shouldn't take you more than just a minute to apply it, feel free making any modification to suit your needs!