SQL Table With A Call To Action?

Question

This is all done in Microsoft Access 2007 and SQL Server. We are creating a way for our users to quickly make notes on a customer. These quick-notes will contain tags that will prompt the user for data based on that tag. The tags are to be limited to a few select options for the user to pick from. The tags will be coded as [TAG] in the database.

A couple examples:

Order refund

[[OrderNumber]] - Was refunded 

When the user selects the above example, he/she would be prompted with a list of the currently selected customer's orders and a field to allow the user to input a specific order. When the user chooses the order, the quick note would look like:

[123456] - Was refunded

General Note

Emailed receipt for order [[OrderNumber]] to email address: [emailAddress]

Would look like:

Emailed receipt for order [123456] to email address: [email protected]

The Problem

The main issue is that each tag invokes a different action.

1. An order number tag calls up a function that will extract the order number from a few columns in an order table.
2. An email address tag will show all the current customer's email addresses or allow the user to put a different email into a field.

Our Purposed Solution

We though about putting the data into a table with the following format:

╔════════╦══════════════╦══════════════════════╦════════════════╗
║ NoteID ║ NoteTag ║ columnReference ║ tableReference ║
╠════════╬══════════════╬══════════════════════╬════════════════╣
║ 1 ║ emailaddress ║ email address ║ emailAddTable ║
╚════════╩══════════════╩══════════════════════╩════════════════╝

Now this table may be a quick way to grab the data, but it will be awkward for function calling, and a lot will still be handled programmatically.

The Questions

What is a clean and simple way to invoke particular calls to action in a program based on the tags in a quick note? A big loop checking for tags in a string and then calling the specific functions?

Answer 1

Use a Factory Method.

Public Function GetObjectByClassName(ByRef className As String) As Object
 Select Case className
 Case "Cat: Set Factory = New Cat
 Case "Dog": Set Factory = New Dog
 Case Else: Err.Raise Description:="Factory does not recognise this className."
 End Select
End Function

Now all you need is a class for each action, and an "execute" method in each class.

Dim executor as object
executor = GetObjectByClassName("Dog")
executor.Execute() 'woof

• Elias: So, assuming here, you are suggesting that the program has a list of values, and pending the value passed it will initialize different classes rather than trying to build off of the database?
• Robert Harvey: A Factory Method accepts a string (in this case, your tag), and returns an object corresponding to your tag. If every object has the same method ("Execute"), then all you have to do is just call Execute on whatever object is returned. I'll add some more detail to my answer.