Currently we use an excel template to do the following tasks for us, using a combination of VBA and spreadsheet formulae, using about 7 different worksheets.
What we do is:
1) import data from a csv file. This file contains information such as a unique client id (6digits), unique animal id, contact information, drug products and dates when these are due or sold to a specific animal. Every different drug will appear as a new line in the .csv for an animal, so if 'client a' has 'animal x' which needs 'drug 1' and '2', this will be on two lines of the .csv (one for each drug).
2) process the data to remove unnecessary information from it – such as removing unnecessary characters from a phone number string which may have been entered in the database (eg in the field mobile: '07123-432943 (mrs)' becomes '07123432943')
3) The most important bit: Analyse the data so that we concatenate (combine) information belonging to one client into a single output string which makes sense and reads appropriately. So for example 'client a' may have animals x, y and z. x may need to be told it needs drug 1 , y needs 1 and z needs 1 and 2. The final output might read ‘dear client a, animal x,y and z require drug 1, but z also requires drug 2...'. It is important that the program doesn’t just add one animal after the other in a continuous addition but creates a well composed output for each senario as far as possible and trying to avoid generics such as: require(s). The way the spreadsheet does this is to create a code for each senario and then use different templates depending on the code. There would probably be a cut off at which it would be too complicated to incorporate multiple animals requiring multiple different drugs – the current excel based system creates unique outputs for up to 3 animals requiring different medications (still quite a few permutations!). More than this and it produces a more generic output, but the alternative would be to split the output into two I suppose (eg if there are 5 animals, do the first three then the other two as two different output messages)
4) It will select which is the most appropriate method of communication available – so will preferentially send to email rather than SMS, and create different message depending on the modality – as characters are obviously limited in SMS. We don’t have emails and mobiles for every client so hence the different methods. In addition some data is more suited to certain communication – appointment reminders are probably best sent by text.
5) This then automatically sends the message to each client by email or email2SMS via outlook.
What we would like is a program that does this without needing to use excel. It also needs:
1) to be autoschedule-able or to be able to run manually at the press of a button (so perhaps continuously running the back ground) – we currently just do this by using scheduled tasks to run a auto opening macro in excel.
2) A preferences area where some options could be entered although this could be added functionality at a later date – such as time to send email, file locations, select an email client (or even integrated email client?); as long as I would be able to edit this per installation initially then these options don’t have to be there, but it would be a development step in the future.
3) As an additional output we are likely to want to be able to transfer the data to specific online print service which we use. Currently data is uploaded manually to the site by importing a csv and telling the website each time what column is used for what information, but I need to be able to automate this – so the program launches and loads the website in an internet explorer window, and automatically populates the data to the point where a preview is shown. We are due to meet with the website developer so I can find out more about how we can link the services.
4)Run on windows
Please let me know if you don't understand this brief