Everyone who has worked with the public sector in Belgium knows that there are strict procedures you need to follow, in order to work together with public organizations like cities, police departments or port authorities.

3P has created software to automate the administrative process of document creation and contract follow up for public contracts. However, 3P licenses are expensive and you don’t want your entire organization contacting the legal or contract department every day to inquire about a contract’s status, right? Keep reading for a romantic story of how SharePoint can bring peace to the world of public contracts, while still benefiting from the fantastic functionality 3P provides.

1. What is 3P?

3P

3P software (www.3p.eu) provides an end-to-end solution to manage public contracts and purchases. It can be used by public authorities and by their contractors to create contracts or offers, and allows users to follow the entire process. This process consists of several phases from Design (NL: ontwerp) of the contract over Awarding (NL: gunning) to Execution and Closure. In each of these phases there are a lot of standardized documents that need to be generated and exchanged. 3P currently provides two ways to deploy: either on premise (Desktop application) or hosted by 3P (web application). This blogpost talks about the on-premise application.

2. Why integrate?

Ok great, this sounds like a fantastic tool, why would you want anything else?
Well, there are several reasons:

• License cost: 3P licenses are rather expensive and there is no “read-only” type of user available. Therefore, integrating data from 3P into a company intranet is a good idea to cut costs for users that don’t need the full capabilities of the tool.
• Security: as there is no read-only user, every user can modify contracts. These contracts are heavily regulated and any parameter change can have severe impact on the process of the public contract. You don’t want users that are not aware of this impact to start fiddling with your contracts.
• Simplification: 3P is an expert tool for legal contract managers. It is not for everyone. You need a thorough understanding of public contracts to be able to use the tool effectively. Therefore it can be better for end users that you just provide a simple overview without needing to access the ‘abstract’ environment in 3P.
• Last but not least you may want to provide insight into these contracts without having to expose all (confidential) information in them. Some contracts may be sensitive and some fields may not be suitable for everyone to read. With smart integration, you can tackle these issues as well!

3. What does 3P offer?

Why build it if you can buy it, right?
Well, let’s explore what 3P has to offer in terms of integration with SharePoint.

To put it bluntly, there is no out-of-the box integration with SharePoint. Documents can’t be stored in SharePoint and metadata of the contracts can’t be pushed into SharePoint either. So, that stopped the show quickly…

But 3P does offer integration capabilities in terms of an “export database”! This is a database that runs on either Microsoft Access or Microsoft SQL Server . Basically ‘export database’ pushes a copy of the actual 3P database, including all changes since the last export, to an exported database. The exported database is also optimized for reporting and other use. In the case of one of our clients, we have realised the following set-up:

3P Export Database

The user’s PC is connected through a fileshare to the Microsoft Access database. All changes are shared between all connected users, since this database is shared across all 3P application instances. The export database that is available from 3P is installed on a separate database server. It is configured in the client, and that configuration is shared for all clients, which means that whenever a user updates a contract, the export database is also immediately updated.

Not all data is exported to the export database, but most of the data is available there. I won’t share internal documentation of 3P, but I can share a few interesting fields that are available:

• Code: number of the contract or tender
• Description: name of the contract
• ConProc: procedure type
• CompName: name of the company to which the contract is awarded
• CostFirstEst: first cost estimation of the contract
• MgrName: name of the 3P manager of the contract
• IsEU: is it a Belgian or above European publication limits contract?
• Date1-Date64: many dates are available like creation date, approval dates, real end date, …
• and so on…

4. How did we do it?

Given the rich nature of the export database, it was easy to imagine how insights could be delivered to a larger audience. Our customer uses SharePoint as their main information management platform and hence it made sense to link the 3P export information with SharePoint.

The first phase of the integration started out with a simple custom list in SharePoint where we stored all information per contract lot (some contracts have multiple lots). This list contains only a few fields of the export database that are relevant for most users. We chose fields like ID, Lot number, IsEU, contract type, status, publication date, description, first estimation, amount awarded, 4 dates, contract manager, awarded party, organizational unit, sponsor and a link to the documents (more on that later).

4.1. A timer to get the job done

To get this information inside SharePoint, we need to create something that checks the export database occasionally and sends out updates to SharePoint. One possibility is to write a timer job for this. For SharePoint on premise, this is typically a console application that is scheduled using Windows Task Scheduler on a server in your data center. For SharePoint Online (but would also work for an on premise farm), you can use an Azure WebJob for that.

4.2. The ‘Logic’

The code itself is not very difficult but has some advanced SharePoint technicalities I won’t bother you with now. Here is the algorithm in “pseudocode”:

Load all taxonomy fields used in SharePoint

Read the database contracts into .NET entities¹

For each database contract

Map the database fields to SharePoint contracts (contains
business logic to have more readable output, for example “8. Closure” instead of just “8” as the status and for
data cleaning as well)

For each SharePoint contract

Load all users (people picker values) for this contract
(and store them in cache)
Find the contract in SharePoint

If it exists

Update the item if the Updated date in the database
is larger than the modified date in SharePoint

Else

Create the item in the SharePoint list

^{1. Reading in only the contracts that we want, and selecting distinct rows based on the columns we want. This was needed because some contracts span multiple years and 3P also allows tracking of budgets per year, hence you can have rows for each budget year, but referring to a single contract. We want to hide this complexity from end users.↩}

4.3. What about the documents?

Each contract in 3P has a folder on the network drive that holds a subfolder for each status of the contract (Design, Awarding, Closure, …). When the tender application document is generated in 3P, it is stored in the corresponding folder. The export database also contains a link to the folder for each contract.

We’ve included a link to the document location in SharePoint as well. This way it is accessible when a user wants to dive deeper. After clicking the link, Windows Explorer opens the shared network drive on the folder for that contract. No more long searches required!

4.4. Libraries

To make life easier, we’ve used the following libraries:

• Dapper: for simple database mapping from the export database to .NET entities
• SeriLog: for simple but elegant logging output
• SharePoint Client-Side Object Model (CSOM): to connect to SharePoint and to make sure it is compatible with SharePoint Online in the future

This seriously reduced development and debugging time.

4.5. The Result

The result looks like this (in Dutch):

Result

Not all fields are shown, because there are many columns. Notice the search box that allows fast and intuitive search.

Here is a contract with some history in the standard version history of SharePoint:

Version History

5. The Road Ahead

This is just the first phase of the integration project. As usual there are improvements that were not yet implemented. The following paragraphs contain some areas of improvement that we’ll tackle in the future.

5.1. Document integration

Although basic document integration works, the documents themselves are still not available on SharePoint. 3P doesn’t allow saving to SharePoint itself, but does allow saving to network locations. This makes it possible to map a network drive to a SharePoint WebDAV location. The only downside is that every 3P user needs to have this mapping configured in the same network drive letter. For small teams, this approach could work.

We have tried this approach and it works, but there seems to be a small bug. The first time you open the document, you can’t save it in Microsoft Word. However the second time you open it, there are no issues. It may be due to the old .doc format, and we’re looking into testing more around this topic in the future.

5.2. Relevant documents

3P’s document storage contains all documents ever generated, unless a contract manager manually removes them. There is no way to know which one is the “final” document. In a legal context however it is of the utmost importance to know which one is final! There is currently no way to indicate this, except in the name of the document itself.

Another issue with this is that all users get to see all documents, which can be confusing and may not be relevant. If documents would be saved to SharePoint, we could apply standard minor and major document version logic, allowing contract managers to define which documents are final by creating a major version for it in SharePoint.

This also enhances security, since users with the “Read” permission can only access major versions.

5.3. Document sets

People who know me well, know that I’m a dear fan of document sets. So, I couldn’t leave that out in this blog. Document sets could be used in this context as well. Instead of using a simple folder in SharePoint to contain all documents for a public contract, why not use a document set and apply all metadata that we currently save to a custom list to the library itself?

Better yet, all documents related to that contract will immediately inherit all properties of the contract as well. Sounds like a win-win to me!

5.4. Not just contracts

The beauty in the 3P export database is that it contains far more than just contracts. It also has other entities available like organizations, budgets, tasks. So who knows the next business case for integration, right?

We sincerely hope you’ve enjoyed this romantic journey of SharePoint coming together with 3P in its many aspects, from timer job to document set.

Author: Sebastiaan Mindreau  – Lead Strategist Digital at LoQutus

Reference:

Mindreau, S. (2018). SharePoint and 3P: A Love Story. Available at: https://whitepages.unlimitedviz.com/2018/01/using-power-bi-to-report-on-person-fields-in-sharepoint/ [Accessed 3 April. 2018].