AHK – Useful AutoHotKeyScripts for Outlook specifically

Another set of very useful scripts mainly dealing with creating hotkeys for common Outlook tasks.

Note that if you are on a laptop you may have pre-mappings for the function keys in which case you will need to quite possibly go for alternatives.

F8::
Run C:\Program Files (x86)\Microsoft Office\Office14\OUTLOOK.EXE /c ipm.note
return

F9:: ;Inbox
keystroke = ^1
parameters = 
Gosub open
Return

F10:: ;Calender
keystroke = ^2
parameters =  "outlook:calendar"
Gosub open
Return

F11:: ;Contacts
keystroke = ^3
parameters = outlook:contacts
Gosub open
Return


F12::
PROCESS, EXIST, OUTLOOK.EXE
PID := ERRORLEVEL
IF ERRORLEVEL <> 0
{
       LOOP,
      {
            WINSHOW, AHK_CLASS rctrl_renwnd32 
            WINACTIVATE, AHK_CLASS rctrl_renwnd32 
            WINWAITACTIVE, AHK_PID %PID% AHK_CLASS rctrl_renwnd32
            WINGETACTIVETITLE, TITLE
            WINCLOSE, %TITLE%
            WINWAIT,, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?,3
            CONTROLSEND, , {ENTER}, AHK_CLASS #32770, Are you sure you want to permanently delete all the items and subfolders in the "Deleted Items" folder?
            IF A_INDEX > 30
                  PROCESS, CLOSE, OUTLOOK.EXE

            PROCESS, EXIST, OUTLOOK.EXE
            IF ERRORLEVEL = 0
                  BREAK
      }
}
return

DetectHiddenWindows, On
Process, Exist, outlook.exe
If !ErrorLevel
   Run outlook.exe
Return

open:
Process, Exist, outlook.exe
If (ErrorLevel != 0)
{
	WinActivate ahk_class rctrl_renwnd32
	WinWaitActive ahk_class rctrl_renwnd32
	Send %keystroke%
}
else
	Run outlook.exe %parameters%
Return

MS Access like development environments for the Web – 3 alternatives

So you would like to construct simple applications that you can at the moment create in MS Access but you want to do it on the web. By that I mean you would like to create a data driven application with somewhat complicated forms that can be accessed by anyone through either IE or Chrome anywhere in the world with a simple login screen at the front to prevent simply anyone accessing the applications collecting the information into a database. What are your options for programs that will assist you in a MS Access like environment rather than going the full IDE deep dive – Visual Studio route – for what I consider to be a reasonable fee?

From my experience the unicorn of access on the web is slowly coming to fruition BUT for the vast majority of people with a budget similar to that for MS Access – lets say £200 ($250) a year for unlimited applications there is simply nothing which is quite as easy and powerful as MS Access. Some are pretty close but simply not as stable and require typically several magnitudes greater amount of configuration. WYSIWYG design isn’t quite as WYSIWYG and stability is a few orders lower than the desktop.

What you are probably looking at can typically be described as either RAD tools for the Web, a Low Coding Platform or something called a Code Generator any of those phrases can be useful for Google.

Assuming you don’t have your own servers whatever you do you will need to spend money on a web host.

The minimum this is likely to cost you is in the region of $15 a month. If you don’t want to spend the next 6 months learning about the insides and outsides of frameworks then I would suggest you go to one of the below three providers who all provide complete environments set up and ready to go with their particular generators pre-installed. This is good value it is extremely difficult to beat these guys on cloud hosting costs and unless you are very advanced and have very particular requirements its a waste of time to try. All three of the below providers will allow you to create limitless number of applications albeit you are limited by the space you hire on your web server. Similarly distribution will be limited by the quality of web server you sign up for. In all likelihood if you have few users it is unlikely that the coding front ends of your applications will be a limit to the number you create more likely the size of databases you are attaching them to and the shear time you have available to create applications.

For a period I was paying a monthly amount for a Nubuilder Pro hosted platform. This performed well and I could create an unlimited number of applications. As it was so hosted I skipped the step of learning some of the deeper parts of the initial configuration. I hope at some point to go back to this. It is open source and seems well maintained with a very dedicated developer. The developer re-wrote much of it and at March 2019 it latest re-incarnation is Nubuilder Forte.

Be warned n-tier web applications do not play as friendly as the desktop you WILL be slower to construct applications than you are on the desktop, getting into it WILL take time and a bit of commitment, you WILL have far less flexibility regards coding, there WILL be less people about to ask questions and there is far far less WYSIWYG design capabilities, error trapping is poor and errors are far more likely to be fatal and the really big warning is that on release of new web frameworks you may not necessarily be able to update without a full site re-design (A fact that comes as a nasty surprise to many CIOs and Project Managers when they realise that they are locked into front end system replacements every 4 or 5 years ) Know how to get data to your local environment out of the back end and accept that the front end is ephemeral and not likely to last in the same way as your desktop applications. (Your database will last but don’t expect to be running it through the same front end ten years from now). Accept that you will now have monthly or annual rental fees for cloud provision.

That said the design of these items is significantly faster than its ever been.

Scriptcase and ASP Runner dot net (Xlinesoft also produces a PHP equivalent generator) have free downloads that are good for getting started.

Commit to one and go for it. – I’ve got both PHP and ASP.NET solutions.. Nubuilder only connects to MySQL whereas Scriptcase and ASPRunner.NET connect to pretty much any database. I started with Nubuilder and am using ASPRunner.net as well because importantly it connects to SQL Server and it was easy to get up and running in MS Azure. Scriptcase is php based and I believe the applications it build require some kind of security program to sit on the web server this put me off – they do however have hosting that you can sign up for which is pre-configured. Their IDE is web based which could be a winning advantage. One of the great advantages of ASP runner dot net is that the program produces an open web application that should run on all stock servers. I found Nubuilder Pro (now Nubuilder Forte) really conceptually elegant which despite its rather drab looks is incredibly flexible the applications it produces are however limited to MySQL and non responsive (But being non responsive you get get more detailed forms!). I would probably be able to change it’s look if I was prepared to get my own server and install everything on it myself. That is not something I have time to do at present.
Nubuilder hosts its IDE in the browser which again is an advantage. ASPRunner.net is more traditional in that you have a program running on a desktop that creates the plumbing of your application which you then need to push to a server for publication  this has the advantage that you get to see the plumbing in the background which makes backup of the site easier but publishing slightly harder.

You may have heard of other generators / design applications out there for example – Zoho Creator / Alpha 5 / Outsystems these hold your hand even more but as a result are even more proprietary and won’t fit in that budget of £200 per year ( by quite a long way!)

Some further information on costs – nubuilder being open source in theory could scale for very little money espectially if you have your own servers already. Scriptcase and Xlinesoft ASP Runner product have an initial fee followed by annual subscription – you may be able to configure it so that you can create unlimited applications for that one fee (if you have good access to web servers ) but it is likely that initially there will be some kind of variable cost per additional application you wish to build. I am using MS Azure with ASP Runner dot net and a developer database costs me about £5 a month with each application being hosted in a web application service which again costs £5. With both Scriptcase and ASP Runner if you stop paying the annual fees your applications will continue to work you will just not get version upgrades. You will be able to step back into version upgrades but you may need to restart your subscription with an additional fee.

Nubuilder Forte Link

Scriptcase Link

ASP Runner – PHPRunner and ASPRunner.Net Link

Good luck

Notepad ++ / AstroGrep / Autohotkey – 3 Useful Tools

Three useful tools for speeding up or automating tasks

Notepad ++
Text editor that has formating for programming – I often use it for editing XML documents and writing VB scripts.
Notepad ++ link

AstroGREP
Son of GREP – useful Text searcher particularly useful for identifying parameters within web files within applications. When you need to customize a web page
Astro Grep link

AutoHotKey
Automation of tasks program that allows creation of executables that can navigate browsers really very powerful
AutoHotKey link

nuBuilderPro – Import csv into a table of your application MySQL database (Its very easy)

I don’t know about you but for me its pretty rare to start an application without any information. At the very least there may be lookup tables or you have information collected in a spreadsheet. Thus when I came to nuBuilderPro one of the first things I researched was how to get information into a table. nuBuilderPro uses a vanilla version of mySQL in the background so this is what we will be working with. We will be attempting to import a csv file. You will need a clean organised csv file.

First create the tables that you require information to go into. Ensure that you have exactly the same table structure as the csv file that you wish to import. Therefore either adjust the table or the csv appropriately. Failure to have the same structure will halt the import.

Next navigate to the administration panel using your particular variation of the below url. Note that it is important to have the / at the end of the url otherwise you will be taken to the more specific database administration page where you design forms. Don’t worry if this happens you can still get to the php administration page by hitting the databases button. In fact this is an alternative way of getting to the screens that I show here.

https://youracount.nubuilder.net/nuadmin/

Use your username and password to get into the nuadmin index panel

Once you have entered your username and password appropriately you should be at the following address

https://youraccount.nubuilder.net/nuadmin/index.php

nuBuilderPro-nuAdminScreen

Now select the small spanner sign in the top right – this takes you to the php admin section for your whole VPS there are other ways of going into this web page but we will go this way for now.
You should be taken to a section which looks as follows

nuBuilderPro-phpMyAdmin

All databases within your VPS should be listed on the left. Each new application will have a database created for it. Each database holds all the required tables that hold your database and are listed on the left hand side. Click on the database in question and then hit structure. You are interested not just in the database but also the particular table. There is a notification grey line at the top of the page which shows you what database and what table you are in.

Importing a csv is a straightforward process of hitting the import button at the top selecting the csv file and hitting the go button. If the csv file contains column names you may wish to alter the row at which import starts.

nubuilderPro-csvimport

Once import has been completed it will indicate how many lines were imported and how long it took. If there are problems you will obtain a message indicating so. I tried to create a simple Russian / English dictionary and it was really very straightforward. It is important that the csv has the right number of columns as per your designed table.

Database Normalisation – a review of the rules to refresh my knowledge

Here is a review of the 6 forms of normalisation (yes the 4th is called 3.5 for some reason) – there are lots of summaries on the web of these, but I wanted to re-write them in my own words to ensure I understand them fully. Revision of these post real world experience is particularly enlightening.

Normalisation rules are cumulative ie a database or set of tables can be said to be 1 through to 5 compliant. To rank at the highest level it must comply with all previous forms.

That is to say that a database (or table) to be in the 2nd Normal Form must first fulfill all the criteria of the 1st Normal Form.

A database is said to be properly normalised if the first three normalised forms are inviolate.

Violations of normalisation forms subsequent to the third normal form may be legitimately required and as such are NOT absolutely required for a database to be classified as properly normalised.

Some web explanations do not list forms post 3 I would argue there is still a lot of value in trying to adhere to these less well forms but must admit to not always including them in design usually due to poor knowledge of the application domain.

The differing forms of normalisation start simple becoming increasingly complicated.
If you have a lot of experience in creating relational backends you may have deduced these issues for yourself.

(1NF) First Normal Form – Edgar F Codd – first stated 1970
Eliminate duplicate columns from the same table.
Create separate tables for each group of related data and identify each row with a unique column or set of columns (the primary key).

(2NF) Second Normal Form – Edgar F Codd – first stated 1971
No partial dependency on anything but the key fields

(3NF) Third Normal Form – Edgar F Codd – first stated 1971
Every non prime attribute of the primary key is non transitively dependent on every key of R. The example I saw was that if you had winners of wimbledon some of which repeated you would not store there birth dates. You would have a junction table of the winners and link to the names of the individuals and it would be in this table that you would store the dates of birth.

The 3.5 Normal Form or the Boyce Codd Normal Form
This is a slightly stronger version of the third normal form developed in 1974 by Raymond F. Boyce and Edgar F. Codd to address certain types of anomaly not dealt with by the 3rd Normal form as originally defined.

Only in rare cases does a 3rd Normal Form table not meet the requirements of Boyd Codd Normal Form. A 3rd Normal Form table which does not have multiple overlapping candidate keys is guaranteed to be in Boyce Codd Normal Form.

Web investigation indicates that the most concise description is;
Every determinant must be a candidate key

and that Boyd Codd Normalised Form is not always possible

Nearest Shops
Person Shop Type Nearest Shop
Mark Jewellers H Samuel
Mark Barbers Mr Man
Natalia Coffee Shop Starbucks
John Coffee Shop Costa
John Barbers Mr Man
John Jewellers H Samuel

The issue above is that Mr Man on record 2 could be changed to a Hairdressers for shop type but the second last record John would not change and as such it would seem to be inconsistent. In this case I would split this table into two with Nearest Shop and Shop Type being linked and there being some kind of table that links People to Nearest shops.

The 4th Normal Form
A table is in the 4th Normal Form if it has no multi-valued dependencies.
Definition: Multivalued dependencies occur when the presence of one or more rows in a table implies the presence of one or more other rows in that same table.

For example lets say you have a you have a shop selling coffee – lets call it Starbucks and you have a table that lists the permutations of cofee sold in its store
CoffeeShop Coffee Product Location
Starbucks Café Late Livingston
Starbucks Americano Livingston
Starbucks Café Late Edinburgh
Starbucks Americano Edinburgh

If we know that all Starbucks sell the same products if there is a new product in one shop then we would expect that new product to be in the other shops so adding a Skinny Late to the Livingston shop implies that there should be another record for Skinny Lates in the Edinburgh shop.

To eliminate the possibility of anomalies we must place the facts about the products and the location of the shops into two tables

Products
Starbucks Café Late
Starbucks Americano
Starbucks Skinny Late

Location of Coffee shops
Starbucks Livingston
Starbucks Edinburgh

Alternatively if the coffee types did vary from location to location the original table would satisfy 4NF

The 5th Normal Form
A table is in fifth normal form or Project-Join Normal Form if it is in the Fourth Normal Form and it cannot have a loss-less decomposition into any number of smaller tables.

Or as I like to call it the – I know about it and try to adhere to it but sometimes I’m a bit lazy form.

I certainly try to keep my designs to the full six normalisation principles but there are instances where certain tables do not comply.

Trusting Your Gut or When Not to Use Computers.

wargamesAs good as computers are at calculating numbers they are really really bad at doing somethings that humans are just great at like making decisions based on fuzzy logic or translating things between languages. In such situations always go for the human at least until C3P0 comes along. Watson cost $3 million to build, good if you want to play trivial pursuit but ask it to choose between competing software vendors and well you will be nowhere.

That’s you numerical based ranking systems for software purchase!!!

Summary and Links for Microsoft MCSE Data Platform Solutions Expert

mcsepicture

Microsoft MCSE Data Platform
Some notes on the syllabus for this – thinking of taking it.

https://www.microsoft.com/learning/en-gb/mcse-sql-data-platform.aspx

Earning an MCSE : Data Platform certification will qualify you for
jobs such as database analyst and database designer

1 : Querying Microsoft SQL Server 2012
Exam 70-461 – MS SQL 2012 credit towards certification MCSA and MCSE

* Create Database Objects (24%)
* Work with data (27%)
* Modify data (24%)
* Troubleshoot and optimise (25%)

See : https://www.microsoft.com/learning/en-gb/exam-70-461.aspx

2 : Administering Microsoft SQL Server 2012 Databases
Exam 70-462 – MS SQL 2012 credit towards certification MCSA and MCSE

* Install and configure (19%)
* Maintain instances and databases (17%)
* Optimise and troubleshoot (14%)
* Manage data (19%)
* Implement security (18%)
* Implement high availability (12%)

See : https://www.microsoft.com/learning/en-gb/exam-70-462.aspx

3 : Implementing a Data Warehouse with Microsoft SQL Server 2012
Exam 70-463 – MS SQL 2012 credit towards certification MCSA and MCSE

* Design and implementation of data warehouse (11%)
* Extract and transform data (23%)
* Load data (27%)
* Configure and deploy SSIS solutions (24%)
* Build data quality solutions (15%)

See : https://www.microsoft.com/learning/en-gb/exam-70-463.aspx

After completing steps 1-2 you’ll earn a Microsoft certified Solutions
Associate (MCSA) SQL Server 2012 certification

4: Developing Microsoft SQL Server 2012 Databases
Exam 70-464 – MS SQL 2012 credit towards certification MCSE

* Implement database objects (31%)
* Implement programming objects (21%)
* Design database objects (24%)
* Optimise and troubleshoot queries (24%)

See : https://www.microsoft.com/learning/en-gb/exam-70-464.aspx

5 : Designing Database Solutions for SQL Server 2012
Exam 70-465 – MS SQL 2012 credit towards certification MCSE

* Design database structure (29%)
* Design databases and database objects (32%)
* Design database security (15%)
* Design a troubleshooting and optimisation solution (24%)

See : https://www.microsoft.com/learning/en-gb/exam-70-465.aspx