Solved

sequential, non-repeating number on my Outlook form

Posted on 2004-08-04
7
898 Views
Last Modified: 2008-01-09
I am creating a form in Outlook to be used as an IT MAINTENANCE REQUEST FORM.  It will be published in the ORGANIZATIONAL FORMS LIBRARY and available to all in our company to use.  On this form I need a unique form number that does NOT change when the IT DIRECTOR or other IT personnel passes it from person to person.  Any idea how to do this?

In a similar question, TallerMike gave the accepted answer below.  From his answer I was confused where one would put the code...

<cftransaction isolation="SERIALIZEABLE">
  <cfquery name="getNextID">
    SELECT MAX(ID)+1 AS nextID
    FROM IDTable
  </cfquery>
  <cfquery name="getNextID">
    UPDATE IDTable
    SET ID = ID + 1
  </cfquery>
  <cfset nextID = getNextID.nextID>
</cftransaction>

Is this to be put somewhere in the Outlook form?  Or in the Access database?  By the way... what type of language is this?  Yes, a little new to Outlook programming.  Just getting started.  Thanks for any help you can give.



THIS WAS TALLERMIKE's ACCEPTED ANSWER THAT CAUGHT MY EYE.

Accepted Answer from TallerMike
Date: 03/19/2003 12:44PM PST
Grade: A
 Accepted Answer  


For it to be a truely sequential non-repeating number... you'll need to store this value in a DB somewhere. Then you'll need to query the next ID from the DB everytime you want a new one. If you're storing the form submissions in the DB, simply add a column to the DB that will house the ID. Then when you want a new ID, you'll need to do the following:

<cftransaction isolation="SERIALIZEABLE">
  <cfquery name="getNextID">
    SELECT MAX(ID)+1 AS nextID
    FROM TableName
  </cfquery>
  <cfset nextID = getNextID.nextID>
</cftransaction>

Alternatively, you could have a table where you simply house your sequential IDs, like so:

<cftransaction isolation="SERIALIZEABLE">
  <cfquery name="getNextID">
    SELECT MAX(ID)+1 AS nextID
    FROM IDTable
  </cfquery>
  <cfquery name="getNextID">
    UPDATE IDTable
    SET ID = ID + 1
  </cfquery>
  <cfset nextID = getNextID.nextID>
</cftransaction>
 
0
Comment
Question by:mdiapro
  • 4
  • 3
7 Comments
 
LVL 7

Expert Comment

by:rosesolutions1
ID: 11722901
To answer your question, this is some sort of database-specific stuff - it wouldn't 'just run' in OL.
Your code needs to be in the template file - while in design view, you go View Code, and write in VBScript.

===HOWEVER===
Your overall task is not straightforward in the type of implementation you are after.
For OL, the traditional way is to store the ID number in a hidden mail item in a public folder that all users have read/write access to. Then you code increments the number and then uses the incremented number in the particular form.

The problem with this approach is that you may have difficulties when users compose a new item while offline (you can get it to work, but there may be duplicate numbers created), or when different exchange servers a slightly out of sync (if you have multiple exchange servers physically distributed). - If those problems don't worry you, then it is all straightforward.

The 'perfectionist" solution is actually to generate a GUID, which should always provide a unique ID number - GUIDS, however, are very long numbers... The approach I have taken in the past was to  assign the "case number" afterwards - for example, all items get send to a automated mailbox I control. A rule in the mailbox runs some code which assigns the number and forwards to wherever it actually needs to go... Happy to provide more details iff you want to go down this route...
0
 

Author Comment

by:mdiapro
ID: 11730302
rosesolutions1,

That sounds rather involved, but I really would like a way to put form numbers on this form that will NOT be changed once they are assigned.  If you include "code"... PLEASE tell me where this code goes.  Thanks so much!
0
 
LVL 7

Accepted Solution

by:
rosesolutions1 earned 160 total points
ID: 11735715
(a) In design view of the form,
     - add a new field Type Number, Format Truncated, called say SeqNo.
     - put a label where you want the number to display, and go to properties/value and choose field SeqNo.
     - set the TO address to be yourself, and move the TO address to a page that doesn't display
     - set the subjectline for the messages to all start with a defined string (ie. I used "TEST: ")

(b) Publish the form, and send a copy to yourself with the subjectline "SEQNOITEM". Now drag and drop the copy to the root of your MAILBOX. (That way we don't have to search through much to find it.)  This item will store the "next number". (It doesn't matter if the form is subsequently changed - we only want this one to hold a number for us.)

(c) In OL VBA, paste the following into ThisOutlookSession:
Sub ApplySeqNo(objMailItem As MailItem)
    Dim myOlApp As Outlook.Application
    Dim myNamespace As NameSpace
    Dim myFolder As MAPIFolder
    Dim objSourceItem As MailItem
    Set myOlApp = CreateObject("Outlook.Application")
    Set myNamespace = myOlApp.GetNamespace("MAPI")
    Set myFolder = myNamespace.GetDefaultFolder(olFolderInbox).Parent
    Set objSourceItem = myFolder.Items("SEQNOITEM")
    intCurrentCount = objSourceItem.UserProperties("SeqNo")
    'MsgBox "Current Count = " & intCurrentCount
    objSourceItem.UserProperties("SeqNo") = intCurrentCount + 1
    objSourceItem.Save
    objMailItem.UserProperties("SeqNo") = intCurrentCount
    objMailItem.Save
    'MsgBox "save2"
    Set objSourceItem = Nothing
    Set myFolder = Nothing
    Set myNamespace = Nothing
    Set myOlApp = Nothing
End Sub

(d) Now create an Outlook rule that will run for the items we care about that triggers the code (ie. if "TEST: " in the Suibject run a script "Project1.ThisOutlookSession.ApplySeqNo)

DONE.
0
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
LVL 7

Expert Comment

by:rosesolutions1
ID: 11735747
oops -
(e) Now you can either add to the rule to forward the message to wherever it should go, OR use code for that decision making...  Also consider CC'ing a storage mailbox/PF to create an archive.
0
 

Author Comment

by:mdiapro
ID: 11738027
rosesolutions1,

I follow everything so far except running the script in (d).  When setting up a rule, I don't know how to get it to point to or run the script.  You really do explain well.
0
 
LVL 7

Expert Comment

by:rosesolutions1
ID: 11741222
In the rules wizard, "Run a Script" should be one of the actions you see.
(What version of OL are you on?)
0
 

Author Comment

by:mdiapro
ID: 11773530
We are running Outlook 2000  SR-1.
0

Featured Post

Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

Join & Write a Comment

Suggested Solutions

Use these top 10 tips to master the art of email signature design. Create an email signature design that will easily wow recipients, promote your brand and highlight your professionalism.
Is your Office 365 signature not working the way you want it to? Are signature updates taking up too much of your time? Let's run through the most common problems that an IT administrator can encounter when dealing with Office 365 email signatures.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
To add imagery to an HTML email signature, you have two options available to you. You can either add a logo/image by embedding it directly into the signature or hosting it externally and linking to it. The vast majority of email clients display l…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now