Its mentioned in the Help db, but searches in the Enterprise Integration forum and the Notes/Domino 6/7 and 4/5 forums come up empty.

1. Rob Wills10/13/2006 03:34:56 AM
Homepage: http://www.inter-weavers.com

I found this routine that I wrote about 18 months ago (so my memory of it is a bit hazy). It definitely works though:

' This routine will connect up to Oracle and will return the next available job number in the knowledge
' that this same number hasn't been used for any other jobs or projects
Dim lcsess As New LCSession
Dim conn As New LCConnection ("oracle8")
' Set the connection parameters
' This data will have to be either passed in or read
conn.Server = CorusCompDoc.OraDBTX(0)
conn.UserId = CorusCompDoc.OraLoginTX(0)
conn.Password = CorusCompDoc.OraPasswordTX(0)
' Connect to the database. If this fails it will throw an error.
conn.Connect
' Define the TABLE that we want to browse
conn.Metadata = "JCJOBS"
Dim LCKeys As New LCFieldList
Dim LCF As LCField
' Only look at Jobs with the same company code
Set LCF = LCKeys.Append("COMPANY", LCTYPE_TEXT)
LCF.Flags = LCFIELDF_KEY
LCF.Text = CorusCompDoc.CompanyTX(0)
Dim NumChars As Integer
NumChars = Len(Cstr(CorusCompDoc.MaxNextAvailNO(0)))
Dim NextAvail As Integer
NextAvail = CorusCompDoc.NextAvailableNO(0)
' Selection of JOB LIKE ...
' The Job number is created from the current next available
Set LCF = LCKeys.Append("JOB", LCTYPE_TEXT)
LCF.Flags = LCFIELDF_KEY Or LCFIELDF_KEY_LIKE
Do
GetNextAvailable = CorusCompDoc.PrefixTX(0) & Right(("00000" & NextAvail), NumChars)
LCF.Text = GetNextAvailable & "-%"
conn.FieldNames = "Job, Name_Short"
' If no records were found then we know that this Job doesn't exist in Progression
' We will also need to check for projects as well
Dim ReturnedFields As New LCFieldList
If conn.Select (LCKeys, 1, ReturnedFields) = 0 Then
Exit Do
Else
If conn.Fetch(ReturnedFields) > 0 Then
'Print "Job: " & ReturnedFields.Job(0) & " " & ReturnedFields.Name_Short(0)
Else
Exit Do
End If
NextAvail = NextAvail + 1
If NextAvail > CorusCompDoc.MaxNextAvailNO(0) Then
GetNextAvailable = ""
Exit Do
End If
End If
Loop
conn.Disconnect

I hope that helps.
Rob.

---

2. Michael Sobczak10/17/2006 07:48:19 PM
Homepage: http://www.punkdbynotes.com

Hi Rob,

The "%" character was what I was looking for. I added that to the string I was assigning to my LCField object's Text property, and it worked perfectly. Thanks!

---