Custom Database Design & Development

Web based custom business system

The client has 2 main office locations but also some staff that work from home. They all need to use the data for different purposes so a web based system is ideal for them. Different job functions get different parts of the system made available to them via role based security.

The system comprises data entry screens, wizards for complex processes, reports that can be viewed on screen as well as exported to Excel, an invoicing feature which produces pdf's of the invoices and a dashbord including dynamic charts.

We host the system for the client and use asp.net and SQL Server.

Customer Surveys

I know… we all hate them! But the customers customer said they needed a measure and as they say, the customer is always right!

The customer didn’t want a web site or web page, this was going to be done as an attachment to an email on a monthly basis. The difficulty was the rules they applied to determine if one of their customers was due to be sent a survey. The customers needed to be between 6 months and 3 years old, never have had a survey before and only for certain product purchases. It sounds relatively straightforward, but our customer tried to create something themselves using Excel and pretty soon realised Excel wouldn’t be able to cope! The list was about 30,000 rows of items purchased and still in warranty. So it wasn’t 1 row per customer.

Our solution consisted of 3 buttons. One to upload the list of purchased items and one to generate a spreadsheet of customers that were due a survey. The third allowed a list previously created to be regenerated. Behind the scenes we used some code to upload the Excel file into a SQL Server table, some logic built into SQL Server stored procedures, some code to pull out the results and generate a new spreadsheet. Other checks included ensuring an email address existed and that we hadn’t tried sending a message to it beforehand and it failed.

Generating emails from data

Instead of creating emails manually in Outlook we can integrate this facility into your application. The email can be pre-populated with data from the application and either edited by the user before it’s sent or just sent without the user seeing it. The user does not use Outlook to create these emails. Frequently it is very useful to be able to see these emails in Outlook’s Sent Items. Most systems do not do this. We can integrate emailing features such as this into your custom built software.

Our customer wanted a completely automated way of reminding their staff to book themselves on a regular mandatory Health and Safety refresher course. Our solution runs overnight and generates personalised emails to those people who are due to attend the refresher course in the next couple of months. As well as this we implemented a similar system to generate an email to course attendees on the night following their training. Each email contains a link to a personalised course evaluation form they need to complete. If the delegate "forgets" to complete it, another email is generated 3 days later. All of the data collected in the evaluations is reported against in another web application.

If the email provider is Microsoft with Office 365 (now Microsoft 365), the preferred method of doing this nowadays is to use Microsoft Graph. Another project we work on uses this method. As well as sending emails using Microsoft Graph it’s possible to display Inbox or Sent Items in a custom application. As well as integrating with email, Microsoft Graph works with any Microsoft 365 application. This is even more powerful if the files are stored in SharePoint or OneDrive.

Portals

Most of the time our customers need systems to run their business. Only their staff use and produce the data. But a lot of the data concerns their customers who create orders, make enquiries, ask about progress etc. normally by telephone or email which is in their system. A portal is a doorway into the data which is presented differently to how it is used internally. One of our customers wanted their customers to have insight to their system but were rightly concerned about security and if their customers were running complex queries that it could affect the performance of their internal system. Our solution was to build a separate application (including database) for their customers to access that got updated from the main system throughout the day. Progress and reports can now be viewed without having to contact internal staff.

Working with API's

An alternative to downloading files of data is to pull a stream of data and work with that. Many online services now expose an api (application programming interface). When you understand what they can provide and how you ask for it you request some data and process the returned stream. We find that that is normally the easy bit! It’s how you handle that incoming stream (usually in json format) that takes the effort. This is particularly so when you request time-based data. Normally the api lets you specific a start date and time. The difficulty is handling duplicated data. To further automate the process we set up automated pulling of the data from the api and running our processing of the incoming json stream.
Our customer needed to get data from their business partner via an api (internet connection that bypasses a web browser). Users have an internal web page to process the new data.

Excel / CSV processing

The only usable interface to an application our customer was using was by importing or exporting csv files. This is a common requirement, importing data from files exported from other systems in csv format. Once you get past a few thousand lines, processing line by line becomes very time consuming, so we used SqlBulkCopy to pull the exported data into the SQL Server database that our application was using. Then there were all sorts of processes such as does the customer already exist, if not, create one and link it to the order, has the contract expired, if so update, and many more checks and potential changes.
Our customer processes a file of 100,000 rows every morning and on a weekly basis processes files of customer (40,000 rows) and contract information that is associated with the daily event file. We produce reports of anomalies that our customer can then investigate. We made this a “manual” button pushing process initially to give control, but are about to automate it further by detecting when a new csv file arrives and running the process automatically at that point.

SMS automation (text messaging)

Did you know that it's possible to set up a system to send text messages (SMS) via your computer? We added this feature to an application we built that made bookings. Our customer wanted to reduce the amount of "no shows" and offer an extra service to their customers. The day before their appointment, the system produces a list of customers that are due to visit. The user selects which customers to send a message to and clicks OK! Their customers receive a text reminding them of their appointment. The advantage of texting is that it is unobtrusive, better than a phone call and easy to do. It's actually relatively easy for us to include this feature in your application.

Brochure production

An unusual request we had was for a system to generate a brochure that was going to be professionally printed and bound by a printing company. It was very important that the user couldn't tweak the finished product before printing as any errors at this stage would be costly. We decided to use Microsoft Word as a platform for the output. Access manages the data. After the (very nice looking) layout was designed we then needed to output the data from the application which stores the data into the template. As well as words there are photographs and charts that need inserting. Using Word development skills, our system resizes and compresses them appropriately. When the document is complete the user can save it as a pdf to give to the printer.

The system evolved to enable users to produce customised subsets of the whole brochure.

© 2023 - Blackman Associates Ltd.