DotNetCurry Logo

Production Ready SharePoint Applications with Little or No Code

Posted by: Todd Crenshaw , on 6/12/2015, in Category SharePoint
Views: 12105
Abstract: This article demonstrates the steps of an actual in-production system that any SharePoint admin/site developer can provide an employer with little to no code. The concept is the use of multiple applications to provide a complete solution, using little to no code if possible. This helps to keep costs down and provides a quick solution for non-critical application needs.

When most people speak of SharePoint, they discuss it in the context of people collaboration. This is what SharePoint was designed for. What came about was a lot of .NET custom code to push SharePoint into the application area, making the SharePoint site a front end for data processing through custom code. What if I told you that SharePoint’s collaboration can include collaboration between multiple applications? This gives an employer a solution similar to a custom coded solution at lower cost and with faster rollout into production. This idea does not downplay the importance of a .NET developer. Depending on the requirements of the application, the use of code may be the only way to get a solution into production. Something to consider is just like a coder has his/her tools in whatever development package used (such as Visual Studio); a non-coder (admin/power user) can also use (multiple applications combined) tools to provide solutions in the SharePoint. This leaves the coder free to do more complex work.

 

It is important to distinguish that the type of solution provided here is not for critical application use. If responses must be in real-time, this type of solution is not recommended. If the solution can provide a response in the time frame of 1 to 2 minutes, this method of application collaboration may work for you and your employer. This method typically requires several applications. In the following solution, the applications used are SharePoint 2010 Enterprise Edition, Email2DB V3.2.1340*, Microsoft SQL Server 2008 Standard Edition, Data Sync Studio 3*, and HarePoint Workflow Extensions*.

Note: The applications marked as * are 3rd-party commercial solutions with a free trial.

What Is the Need?

In many natural gas and oil producing companies, a department typically named SCADA exists. You can think of SCADA similar to a NOC in that SCADA remotely monitors natural gas and oil well status. The SCADA control center is in many cases able to temporarily resolve issues with wells or reduce the issue to where the problem can wait for a field technician to resolve during the day. SCADA needs an effective method to record these issues and report the issues to the tech crew for resolution. The SCADA control center must also verify to the tech crew that other daily reports have been checked. The system I’m going to demonstrate is named the Virtual Operator Report or VOR for short. Full commercial applications exist for this need, but they are very expensive. In the down turn for the energy industry, a much less expensive solution was asked for from my companies IT department. Building the solution with existing applications and no code, was handed to me.

Requirements

The SCADA control center staff needs a method to record natural gas and oil well issues that occur during all shifts. At any given time, the SCADA staff needs to be able to send a report by email of the current issues to the field technicians. The SCADA staff must have the ability to review the report before sending it out to the field technicians. The report must include the ability to record that the SCADA staff has reviewed several daily reports. Field technicians need to be able to respond to each issue from the emailed report by smart phone, tablet, laptop, or PC through a reply email. The response must show up in the SCADA control center in SharePoint on the screen that the SCADA control center monitors.

SharePoint Collaboration Solution

From here as each application is used in the process flow, I’ll note which application is doing what.

SharePoint

Since this solution requires a method of recording the issues, a SharePoint list is created with the following fields:

WellName – Will contain the natural gas or oil well name. This is imported from another system and kept up to date using a 3rd party sync application. Manual method would also work but be more time consuming. The 3rd party application is Data Sync Studio and uses the timer from the Microsoft Scheduler.

MeterName – Contains the meter type name for the well.

Primary Issue – A drop down list (pulled from another list in SharePoint) with the top primary issues that occur.

Sub-Issue – A drop down list (pulled from the same list as above from a second column) with the sub-issues that match the primary issue.

Additional Information – Allows the SCADA control room personnel to provide information outside of the primary issue and sub-issue drop down selections.

Well Reported Down – Notes if the well is down (yes) or not (no) so the day shift crew can address the wells down first.

Field Response – Captures from email the response by the tech crew to the issue.

Responded To – A flag used to verify that the issue has been addressed, response is (yes) or (no).

IssueURL – This field is used to hold a URL link that is created by a workflow. The URL link will be in the email notice sent to the day shift crew so they can easily respond using an iPhone or other smart device capable of sending email.

The ID field is also used. Since SharePoint automatically assigns a unique ID, this field does not need to be created and can be used as is. The list looks like this:

sharepoint-description

Fig1. SharePoint List Description

A second list is created which will be used to record and report that the SCADA staff has reviewed the other logs as required and will also be used to trigger the generation of the report by sending an email to our email processor. This list is covered further down in this article.

SharePoint has the ability to send emails but it has difficulty generating a report. Instead of using SharePoint to try and generate an email report of all the wells that have issues, SQL will be used instead. SQL has the capability to format a clean looking email report to be sent to the field technicians containing all the well issues. Since the field technicians will respond from this email report, a way to control where that response or reply email is sent and the information in it, needs to be created. This solution can be done in SharePoint or in SQL. In this case, SharePoint was chosen as the location to generate and hold this information. A workflow is created to populate the field (IssueURL) that will hold this email URL information.

When a new item (issue) is added to the list, a workflow kicks off which populates the IssueURL field for that item. Here is the workflow.

HarePoint Workflow Extensions/SharePoint

hareflow-extension

Fig2. SharePoint Workflow with HarePoint Workflow Extensions

The breakdown is – set a variable named URLResponseLink with a mailto: email address. This address should be to whatever email address for your email processor that you decide to use. The email should include all pertinent information so the email processor can easily identify what the email is for. We use the SharePoint list ID field which the email processor extracts from the email and matches to the ID of the item in the SharePoint list.

sharepoint-workflow

Fig3. SharePoint Workflow using HarePoint Workflow Extensions

The body of the email is set by combining the variable URLResponeLink with other parts of the list.

sharepoint-hareflow-extension

Fig4. SharePoint Workflow using HarePoint Workflow Extensions

When this workflow runs, it populates the IssueURL column for the item with the data from variable Body_Well, writes a note to the log file (always a good practice for debugging), and then if the current field response is not empty, the column RespondedTo will be set to (Yes). We know that the RespondedTo for the new list item (well issue) is empty at this point so the workflow terminates as it should. The workflow creates an email hyperlink in the column for the item just created. This is just a standard HTML HREF email link.

sharepoint-list-column

Fig5. SharePoint list column containing a HREF email hyperlink

This hyperlink will be used in the email report sent to the tech crew. They will have an email hyper link in the report that they can click which will automatically open a reply response email and populate most of the body of the email. You’ll see how this works later in this article. Following is the email that the above URL will generate (this is an example but not of the same well as the html code above):

workflow-email

Fig6. SharePoint Workflow Email

The only thing the field tech needs to do is type in what was done to resolve the issue after the FIELD RESPONSE: line and click the Send button. This formatting looks great on the iPhone and works well with most email enabled devices. The only information that our email processor really needs is the Item ID: and the Field Response. The other information is included only for the field technician, so he/she can see what natural gas or oil well and issue they are responding to. This helps them to recognize if they tapped the correct well email hyper link in the email report or not.

So how do we get the natural gas or oil well issues to the field technicians so they can respond to these issues?

During any shift of the SCADA control room, the control room operators are entering issues into the SharePoint list. A nice display page has been created and SharePoint Designer is used to give a color code conditional formatting to the entries. Check Google on how to do conditional formatting (note this is no longer available in SharePoint Designer 2013 or SharePoint 2013 – Microsoft removed this feature). In SharePoint 2010 this is pretty easy to accomplish. In this case green are resolved issues, yellow are existing issues, and red are existing issues of wells down (considered critical). You can add a sort order if you wish, we sort by oldest issue first.

virtual-operators-log

Fig7. SharePoint Page with the Virtual Operators Log Displayed as a Web Part

Across the top you’ll see Reports Check. The SCADA control room operators are required to check other log files before sending out the report each morning. They click on the “Reports Check” link at the top of the page.

sharepoint-list

Fig8. SharePoint List

This takes us to a second list. The list is a simple Yes/No check list but it is very important. This list has a workflow attached to it that kicks off the report generation and sending process. This is where the email comes from that is sent to our email processor to initiate several other steps, including kicking off a stored procedure in SQL to generate the email report.

sharepoint-list-desc

Fig9. SharePoint List Description

The SCADA control room operator checks each one of the reports wherever they are located, then clicks on the Add new item link which opens a dialog box.

infopath-entry

Fig10. SharePoint List InfoPath Entry Form

The SCADA control room operator must verify they he/she has checked the logs by check marking the appropriate box(s). If the SCADA control room operator does not select the Send Report check box, a report is generated but only sent to the SCADA control room operators so they can verify the content. If the Send Report check box is checked, then the report is sent to the field technicians as well as to the SCADA control room. As previously mentioned, SQL does a very nice job of formatting the email report and sending so the workflow only starts this process by sending an email with the data to our email processor. SQL is what controls who this report is sent to. I’ll show you that here shortly.

 workflow-extension

Fig11. SharePoint Workflow using HarePoint Workflow Extensions

The email is sent to the email processor. A copy of the entry is made in a second identical list for logging and historical purposes and after 5 minutes, the original entry is deleted from the primary list. We delete the original to prevent confusion and keeping the list clear helps with this.

sharepoint-workflow-email

Fig12. SharePoint Workflow Email

The email that is sent from the workflow includes all the information that is needed to kick off the email report process. The email processor receives an email similar to the one below.

received-email

Fig13. The Received Email

Email2DB

The email processor checks for certain information in the email subject and body by parsing the email using an Account matching method – the email address. If a match is found, that kicks off the search for a trigger to process the email.

email2db-account

Fig14. Email2DB Accounts Screen

Here’s the trigger.

 email2db-triggers

Fig15. Email2DB Triggers Screen

For the triggers we use the subject line in the email. This is typically a unique key we generate. This is done for two reasons – first of course is for the trigger, so the correct trigger is used. The second reason is so stray or un-wanted emails can’t start the process. Only email with the correct subject key will kick off the process. When the trigger matches, it’ll extract the data from the email.

email2db-data-fields

Fig16. Email2DB data fields to search for and extract from the email

Clicking on one of the Name fields such as Chemical shows how the data is extracted using this tool.

email2db-screen

Fig17. Email2DB definition screen for searching the content of the email

Once the data is extracted, the trigger starts the action which is script based. Here’s a look at the script in its entirety.

email2db-script

Fig18. Email2DB Script Window

Here’s each step through the script.

a) The first step is to display a message on the server console. It’s a default, can be removed but I just leave it alone.

b) Send an email to me. I do this mostly in test mode so I can see what the system has received and as you can see from above, this step it is unchecked so it doesn’t run in production. If it was checked, here’s what I receive.

email2db-email-window

Fig19. Email2DB Email Window

c) The next step kicks off a scheduled task in Microsoft Scheduler. This task is to sync up the natural gas or oil well issues data that is in the SharePoint list, with the data in the SQL database.

action-window

Fig20. Email2DB Run or Execute an Action Window

Microsoft NT Task Scheduler

Notice the scheduled job has no time to run. This is because we only call it from the script.

nt-task-scheduler

Fig21. Microsoft NT Task Scheduler Window

a) This scheduled task calls our syncing application.

Data Synchronisation Studio

data-sync-studio

Fig22. Data Synchronisation Studio Schema Map Window

Now why do we sync this data? The data (natural gas and/or oil well issues) that may have been placed into the SharePoint list needs to find its way into the SQL table. Remember I mentioned that the email report is sent by SQL? If the data in the SharePoint list is up to date, but the data in SQL is not, our report will be in-accurate. This sync is for the well issues. We’ll update the SQL table that contains the logs check below.

Email2DB

After the sync is complete we’re back to the script. The script does a 30 second wait command. This is done to ensure that the data sync has completed and exited. It actually could be reduced or possibly even removed, but we leave it in because it also does no harm. It was originally placed in the script during testing.

script-window

Fig23. Email2DB Script Window

Next we make sure the records check database is also updated by placing the data that was extracted from the email directly into SQL. This is just a simple SQL insert command in the script.

sql-actions-window

Fig24. Email2DB SQL Actions Window for SQL Insert Command

Back again to the script which does a 15 second wait. This is another one that could possibly be removed but also does no harm, so I left it. I won’t show it here, it’s the same as Fig23 but 15 seconds instead of 30.

Finally, the stored procedure is called. This generates the report, determines if the report is just to the SCADA Control Room or out to the field technicians and the SCADA Control Room.

tsql-stored-proc

Fig25. Email2DB Actions Window for TSQL Stored Procedure

Now we jump to SQL to see the stored procedure that’s been called to run. Have your SQL DBA explain what it does and how it does it if you don’t know SQL. Know that as a SharePoint admin, I learned TSQL and wrote this script with some help from our SQL database admin and report writers. It’s not difficult to learn and though it is beginner TSQL coding and by far not the best practices, it does work correctly, is very reliable, and was done without having to bother the SQL developers too much.

Microsoft SQL 2008

USE [Sharepoint]
GO

/****** Object:  StoredProcedure [dbo].[********* VORReport]    Script Date: 06/03/2015 15:33:17 ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

/*** Spot rate data is needed and pulled from another SQL table, placed into variables so it can be included in this report 

DECLARE @SPOTRATE VARCHAR(10) , @SPOTRATETIME VARCHAR(25)
    SET @SPOTRATE = ( SELECT TOP ( 1 ) REPLACE(CONVERT(VARCHAR,CONVERT(MONEY,SpotRate),1), '.00', '')
                      FROM      [SERVERNAME].[TABLE_REPORTING].[DBO].[******SpotRateChart]
                      WHERE     [Field] = '*****_****'
                      ORDER BY  [seq] DESC)

    SET @SPOTRATETIME = ( SELECT TOP ( 1 ) DateNTime
                          FROM      [SERVERNAME].[TABLE_REPORTING].[DBO].[******SpotRateChart]
                          WHERE     [Field] = '*****_***'
                          ORDER BY  [seq] DESC)
    
/*** The SCADA log check information is pulled from its SQL table. This data was added to its own SQL table 
         in a prior portion of the email processor script and is now pulled from the SQL table. Since only the
         latest information is needed the sort order is reversed and the top row is pulled. 
                    
        DECLARE @COMMENTS VARCHAR(255),
        @Report_1 VARCHAR(5),
        @Report_2 VARCHAR(5),
        @Report_3 VARCHAR(5),
        @Report_4 VARCHAR(5),
        @SEND_REPORT VARCHAR(5)
        
        SET @COMMENTS = ( SELECT TOP ( 1 ) Comments
                        FROM    dbo.************RecordsCheck
                        ORDER BY [Record_ID] 
                        DESC)
        SET @Report_1 = ( SELECT TOP ( 1 ) Report_1 
                        FROM    dbo. ************RecordsCheck
                        ORDER BY [Record_ID] DESC) 
                        
                        IF @Report_1 ='False' SET @Report_1 = 'No' ELSE SET @Report_1 = 'Yes'
               
        SET @Report_2  = ( SELECT TOP ( 1 ) Report_2   
                        FROM    dbo. ************RecordsCheck
                        ORDER BY [Record_ID] DESC)
                        
                        IF @Report_2 ='False' SET @Report_2 = 'No' ELSE SET @Report_2 = 'Yes'
                                                         
        SET @Report_3  = ( SELECT TOP ( 1 ) Report_3   
                        FROM    dbo. ************RecordsCheck
                        ORDER BY [Record_ID] DESC)
                                                         
                        IF @Report_3 ='False' SET @Report_3 = 'No' ELSE SET @Report_3 = 'Yes'
    
        SET @Report_4  = ( SELECT TOP ( 1 ) Report_4  
                        FROM    dbo. ************RecordsCheck
                        ORDER BY [Record_ID] DESC)
                                                         
                         IF @Report_4 ='False' SET @Report_4 = 'No' ELSE SET @Report_4 = 'Yes'
                            
        SET @SEND_REPORT   = ( SELECT TOP ( 1 ) Send_Report  
                        FROM    dbo. ************RecordsCheck
                        ORDER BY [Record_ID] DESC)
                                 
/*** Create a temporary table. Populate this table with the last 100 well issues. Out of those we’ll pick the active issues and wells down to place
         into the emailed report.
    
    CREATE TABLE #*****VORWellIssues_Temp
          (
          [ID] [INT] NOT NULL ,
          [Well_Name] [VARCHAR](50) NULL ,
          [MeterName] [VARCHAR](30) NULL ,
          [Primary_Issue] [VARCHAR](30) NULL ,
          [Sub-Issue] [VARCHAR](30) NULL ,
          [Additional_Information] [VARCHAR](255) NULL ,
          [Well_Reported_Down] [BIT] NULL ,
          [Field_Response] [VARCHAR](255) NULL ,
          [Issue_URL] [VARCHAR](MAX) NULL ,
          [RespondedTo] [BIT] NULL
          )

    INSERT  INTO #*****VORWellIssues_Temp
              SELECT TOP (100) *
              FROM [Sharepoint].[dbo].[ ************VOR]
              ORDER BY [ID] DESC
    
/*** Build the report here into the email. Use variables along with HTML stored in variables to create the report.
        
    DECLARE @REPORT_WELLS_MSG_HEADER_DOWN VARCHAR(MAX) ,
        @REPORT_WELL_REPORT_CHECK VARCHAR(MAX) ,
        @REPORT_WELL_BODY_DOWN VARCHAR(MAX) ,
        @REPORT_WELLS_MSG_FOOTER_DOWN VARCHAR(MAX) ,
        @REPORT_WELLS_MSG_HEADER_ISSUES VARCHAR(MAX) ,
        @REPORT_WELL_BODY_ISSUES VARCHAR(MAX) ,
        @REPORT_WELLS_MSG_FOOTER_ISSUES VARCHAR(MAX) ,
        @REPORT_EMAIL VARCHAR(MAX) ,
        @TODAY VARCHAR(40) ,
        @DATE VARCHAR(20) ,
        @REPORT_NUMBER VARCHAR(20) ,
        @TIME VARCHAR(20) ,
        @REPORT_WELLS_MSG_TITLE VARCHAR(MAX)
    
    SET @TIME = ( SELECT    CONVERT(TIME, GETDATE()))
    SET @DATE = ( SELECT    CONVERT(DATE, GETDATE()))
    
    SET @REPORT_NUMBER = SUBSTRING(@DATE, 6, 2) + RIGHT(@DATE, 2) + LEFT(@TIME, 2) + SUBSTRING(@TIME, 4, 2) + SUBSTRING(@TIME, 7, 2)
    SET @TODAY = 'IN TESTING ************ VOR Report' 
    
    SET @REPORT_WELLS_MSG_TITLE = '<table border="1" width="100%"><tr><td><p align="center"><b>Report #</b></td><td>
    <p align="center"><b>************ Spot Rate</b></td><td><p align="center"><b>Spot Rate As Of</b></td></tr>
    <tr><td><p align="center">' + @REPORT_NUMBER + '</td><td><p align="center">' + @SPOTRATE + '</td><td>
    <p align="center">' + @SPOTRATETIME + '</td></tr></table></b></br>'

    SET @REPORT_WELL_REPORT_CHECK = '<table border="1" width="100%"><tr><td colspan="4"><p align="center"><b>Reports Checked</b>
    </td></tr><tr><td align="center">Spot Rate V. Deliverability</td><td align="center">Chemcial</td>
    <td align="center">Battery</td><td align="center">Surface / Intermediate Casing</td></tr><tr><td>
    <p align="center">'+ @Report_1 +'</td><td><p align="center">'+@Report_2+'</td><td>
    <p align="center">'+@Report_3+'</td><td><p align="center">'+@Report_4+'</td></tr><tr><td colspan="4">
    <p align="center"><b>Comments from the SCADA Control Center</b></td></tr><tr><td colspan="4">
    <p align="center">'+@COMMENTS+'</td></tr></table></br>'

    SET @REPORT_WELLS_MSG_HEADER_DOWN = '<B><table border="1" width="100%"><tr><td><b>Reported Wells Down</b>
    </td></tr></table></b></br>'
        
    DECLARE @REPORT_TO VARCHAR(255)
    DECLARE @WELL_DOWN VARCHAR(MAX)  
    
    SELECT  @WELL_DOWN = COALESCE(@WELL_DOWN + '', '') + ISSUE_URL
    FROM    #*****VORWellIssues_Temp
    WHERE   Well_Reported_Down = '1' AND RespondedTo = '0'
    ORDER BY Primary_Issue ASC ,[Sub-Issue]  
    IF @WELL_DOWN IS NULL SET @WELL_DOWN = '<B>No Reported Wells Down At This Time.</B><BR><BR>'
    SET @REPORT_WELL_BODY_DOWN = @WELL_DOWN 
    SET @REPORT_WELLS_MSG_FOOTER_DOWN = ''

    SET @REPORT_WELLS_MSG_HEADER_ISSUES = '<table border="1" width="100%"><tr><td><b>Reported Well Issues</b></td></tr></table></br>'
    DECLARE @WELL_ISSUES VARCHAR(MAX)  
    
    SELECT  @WELL_ISSUES = COALESCE(@WELL_ISSUES + '', '') + ISSUE_URL
    FROM    #*****VORWellIssues_Temp
    WHERE   Well_Reported_Down = '0' AND RespondedTo = '0' 
    ORDER BY Primary_Issue ASC ,[Sub-Issue] 
    IF @WELL_ISSUES IS NULL SET @WELL_ISSUES = '<B>No Reported Well Issues At This Time.</B><BR><BR>'
    SET @REPORT_WELL_BODY_ISSUES = @WELL_ISSUES 
    SET @REPORT_WELLS_MSG_FOOTER_ISSUES = '<table border="1" width="100%"><tr><td><b>End of Report</b></td></tr></table>'
    
    SET @REPORT_EMAIL = @REPORT_WELLS_MSG_TITLE 
        + @REPORT_WELL_REPORT_CHECK
        + @REPORT_WELLS_MSG_HEADER_DOWN 
        + @REPORT_WELL_BODY_DOWN
        + @REPORT_WELLS_MSG_FOOTER_DOWN 
        + @REPORT_WELLS_MSG_HEADER_ISSUES
        + @REPORT_WELL_BODY_ISSUES 
        + @REPORT_WELLS_MSG_FOOTER_ISSUES

/*** Who to send the report to. If SEND_Report is set to True then the report goes to everyone including the field techs.
         If set to anything other than true, then send only to the scada office and specific individuals, but not to the field techs.
    
    IF @SEND_REPORT = 'True' SET @REPORT_TO = 'scadacontrolroom@************.com; csmith@************.com; rsaunders@************.com; jphillips@************.com; mdon@************.com;jdaw@************.com;tcrenshaw@************.com; swinters@************.com' ELSE SET @REPORT_TO = 'scadacontrolroom@************.com; tcrenshaw@************.com'
    
    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'DBMail',
        @from_address = 'DCR@************.com',
        @recipients = @REPORT_TO,
        @subject = @TODAY,
        @body_format = 'HTML', 
        @body = @REPORT_EMAIL 
            
    DROP TABLE #*****VORWellIssues_Temp

GO

What this stored procedure does is gather data from 3 different tables on different servers, combines the data and formats an email to be sent. The email looks like the example below.

email-example

Fig26. Example of the Email Report Sent from SQL

Remember I mentioned those email URL’s that are created in SharePoint by the workflow? Well now you can see above where they are placed by the SQL stored procedure. This allows the field techs to see what wells are down and which have issues and respond as they bring each well issue under control. The field technician clicks (or on a phone taps) on the blue email hyper link to initiate the response process. This opens a pre-formatted email response (Fig6.) where the field technician types in the resolution where FIELD RESPONSE: is in the email, and then hits the send button.

The email is picked up by the same account as in Fig14. However, since this is a response, a different trigger is kicked off.

Email2DB

email-trigger-window

Fig27. Email2DB Triggers Window for Field Tech Response

This trigger extracts the information from the response email sent by the field technician.

search-data-fields

Fig28. Email2DB data fields to search for and extract from the email

As before, I’ll break down the script that is run.

email-script-window

Fig29. Email2DB Script Window

a) First we have the console pop up message, again this can be removed as it’s just a notification.

b) Following is from the ‘IF’ statement. If the response email from the field technician has no response or the response is typed into the email into the wrong place, the field technician is sent an email explaining this and to resubmit their response. The email parser can only pick up the data if it is where it’s expected to be. This sometimes takes some training of the field technicians, but eventually they learn where they have to enter their response.

email-window

Fig30. Email2DB Email Window

If the email parser can’t find the data (the test is true) then the script ends at this point.

c) If the response email contains the properly formatted response, then the script pulls the response data and combines it with the name of who sent the email.

d) The sync to the data in SharePoint is done just as it was done before through the task scheduler to ensure that the SQL database is up to date with what is in the SharePoint list.

e) The email parser updates the list item in SharePoint. It does this by using the list item ID which is sent in both the email report as well as in the response email from the field technician.

sharepoint-list-db

Fig31. Email2DB SharePoint List and Field Update Window

f) Once the list item in SharePoint is updated, it appears as shown below.

 

sharepoint-web-part

Fig32. SharePoint Page with the Virtual Operators Log Displayed as a Web Part

a) The last step is to run the report again which is sent to the field technicians and to the SCADA control room. Why is the report run again? The new report will no longer have this well issue that was just resolved in it, but will contain the wells that still have issues. This is the update for the field technicians so they know what well issues are still outstanding and that the well issue they responded to is no longer in the report.

Conclusion

This completes what appears to be a very complex process, but actually it’s not complex at all. The applications and their functions are quick, simple, and each does what it does very well. The power of collaborating applications comes in what each application can provide. SharePoint is excellent at accepting data, administering the security of that data, and accessibility of the data. Our email processor is excellent at extracting data from email and placing the data into SharePoint and other databases or data storage systems. It also does a good job at scripting, allowing the calling of other applications.

In one application we have, our VP’s are required for an approval process. However these VP’s are often in travel status. Using SharePoint we collect the information the VP’s need to make an approval or denial. We send an email to the appropriate VP via a workflow with all the needed information. The VP clicks on an Approve or Denied link in the email. The response is sent to our email processor. The email processor then places the appropriate information back into our SharePoint list where a workflow on that list can continue processing as needed. At first our VP’s were on the fence about using such a method. They said they wouldn’t use email for approvals. Once they started using it, we were told NEVER take that feature away. We’ve actually incorporated it into many other similar type collaboration applications with SharePoint. It’s important when doing application collaboration that you use the correct applications to provide the functions you need. In the case of the virtual operator, several applications were used but all are solid and perform very well. One other big advantage to doing application collaboration is that the total time spent designing and building this collaborated application was just over 3 days (24 work hours).

Now the title of this article is Production Ready SharePoint Applications with Little or No Code. What do I mean by that? Well as is with any solution, the customer wants what the customer wants. In one similar solution we provided, the customer was not happy about having to use a dialog box with check marks to initiate the processing of the data like the above application works. The customer wanted a button on the page that they could click. The easiest way we concluded to provide them their button was to have our .NET coder write a .wsp package, a button which when clicked would send the email to the email processor to start the processing of the data. Literally that’s all the button does it just sends the email to the email processor. In that application the data sync software is kicked off in the same manner as here, through a scheduled task, a SQL stored procedure runs which manipulates the data and the data is placed into reports and sent out.

I hope you’ve learned a new technique to add to your tools with SharePoint.

Was this article worth reading? Share it with fellow developers too. Thanks!
Share on LinkedIn
Share on Google+
Further Reading - Articles You May Like!
Author
Todd Crenshaw is a Systems and SharePoint Administrator for an energy company located in Dallas, Texas. Todd has worked in IT for 30+ years and has experience covering all Windows platforms, several flavors of UNIX, and IBM’s AS/400 series. On the application side, Todd’s managed SharePoint 2007/10, Exchange 6.x, several different flavors of enterprise backup software. Over the past 6 years Todd has been involved with SharePoint in the administration and Power User roll where he has pushed the limits of SharePoint workflows and InfoPath. Todd also provides training through his co-partnered company QualityTrainingAcademy.com. Todd can be reached through LinkedIn and maintains his own SharePoint blog at here


Page copy protected against web site content infringement 	by Copyscape




Feedback - Leave us some adulation, criticism and everything in between!