RE: target fields not created if source field data is null Wayne A Sobers 29.May.13 07:40 PM a Web browser LC LSX LC LSX - All ReleasesWindows
I didn't get a chance to look back at this posting as I ended up using a secondary procedure to go and "fill in" the missing data.
Here is the code I use to perform the update.
%REM
Class ItemStock_Update
Description: Load/Copy/Update Item quantities from external ERP system
'Calling procedure
Dim UpdateObj As ItemStock_Update
'
Set UpdateObj = New ItemStock_Update
If UpdateObj.setup("") Then
Call UpdateObj.RunUpdate
End If
%END REM
Class ItemStock_Update As LogItem
Private lcSession As LCSession
Private lconODBC As LCConnection
Private lconNotes As LCConnection
'
Private src_flds As LCFieldList
Private tgt_flds As LCFieldList
Private key_flds As LCFieldList
Private rcount As Long
Private TimeZone As Integer
'
Sub New
Set lcSession = New LCSession
Call lcSession.Clearstatus()
Set lconODBC = New LCConnection("odbc2")
Set lconNotes = New LCConnection("notes")
'
Set src_flds = New LCFieldList(FETCH_SIZE)
Set tgt_flds = New LCFieldList(FETCH_SIZE)
Set key_flds = New LCFieldList
rcount = 0
Dim session As New NotesSession
Dim international As NotesInternational
Dim exString As String
Set international = session.International
me.TimeZone = international.Timezone
End Sub
'
Sub Delete
If lconODBC.IsConnected Then lconODBC.Disconnect
If lconNotes.IsConnected Then lconNotes.Disconnect
End Sub
'
Function setup(filterclause As String) As Boolean
Dim result As Boolean
On Error GoTo setupError
result = True
' ODBC connection setup
lconODBC.Server = "ERPData"
lconODBC.Userid = ""
lconODBC.Password = ""
lconODBC.Metadata = SourceTable
lconODBC.connect
'
If (lcSession.Status <> LCSUCCESS ) Then
me.LogEvent("LSX Error: Status: " & lcSession.Getstatustext(lcSession.Status) )
result = False
Else
' Notes connection setup
lconNotes.Server = session.CurrentDatabase.Server
lconNotes.database = session.CurrentDatabase.FilePath
lconNotes.MetaData = TargetForm
lconNotes.View = TargetView
lconNotes.Connect
If (lcSession.Status <> LCSUCCESS ) Then
me.LogEvent("LSX Error: Status: " & lcSession.Getstatustext(lcSession.Status) )
result = False
Else
lconNotes.Index = TargetView
lconNotes.MapByName = True
lconNotes.UpdateViews = True
'
lconNotes.FieldNames = TargetFields
lconODBC.FieldNames = SourceFields
lconODBC.Condition = filterclause
'
rcount = lconODBC.Select( nothing, 1, src_flds)
'
Call tgt_flds.MapName(src_flds, MapSourceFields, MapTargetFields )
'
Call tgt_flds.Append("Process_Flag",LCTYPE_TEXT)
Call tgt_flds.Append("plDate_LRect",LCTYPE_DATETIME) ' convert YYMMDD style date to notes date...
Call tgt_flds.Append("plCategory",LCTYPE_TEXT)
Call tgt_flds.Append("plUnit_Cost",LCTYPE_NUMERIC)
Call tgt_flds.Append("plReOrderPoint",LCTYPE_NUMERIC)
Call tgt_flds.Append("plQtySoldYtd",LCTYPE_NUMERIC)
Call tgt_flds.Append("plItem_RCost",LCTYPE_NUMERIC)
'
tgt_flds.Lookup("plBranch_no").flags = LCFIELDF_KEY
tgt_flds.Lookup("plItem_No").flags = LCFIELDF_KEY
'
End If
End If
ExitFunction:
setup = result
Exit Function
setupError:
'Error Trapping
result = False
Resume ExitFunction
End Function
'
Sub ConvertFields(rcount As Long)
Dim tDateField As Lcfield, tReceiveDate_fld As lcField, tProcessField As Lcfield, j As Integer
Dim tDateTime1 As New LCDatetime
Dim nstream As New Lcstream
'
Set tReceiveDate_fld = src_flds.Lookup("LAST_RECEIPT_DATE")
Set tDateField = tgt_flds.Lookup("plDate_LRect")
Set tProcessField = tgt_flds.Lookup("Process_Flag")
nstream.Text = "X"
Dim tCategory As Lcfield, tUnit_Cost As Lcfield, tReOrderPoint As Lcfield, tQtySoldYtd As Lcfield, tItem_RCost As Lcfield
Set tCategory = tgt_flds.lookup("plCategory")
Set tUnit_Cost = tgt_flds.lookup("plUnit_Cost")
Set tReOrderPoint = tgt_flds.lookup("plReOrderPoint")
Set tQtySoldYtd = tgt_flds.lookup("plQtySoldYtd")
Set tItem_RCost = tgt_flds.lookup("plItem_RCost")
'
For j = 1 To rcount
Set tDateTime1 = convertdate8(tReceiveDate_fld.Text(j-1))
Call tDateField.SetDatetime(j,tDateTime1)
Call tProcessField.Setstream(j, nstream)
'
Call tCategory.Setstream(j, nstream)
call tUnit_Cost.Setfloat(j, CDbl("0.00"))
call tReOrderPoint.Setfloat(j, CDbl("0.00"))
call tQtySoldYtd.Setfloat(j, CDbl("0.00"))
call tItem_RCost.Setfloat(j, CDbl("0.00"))
Next
End Sub
'
Sub RunUpdate
Dim timekey As String, timetaken As Double
Dim insert_count As long, update_count As Long, fetch_count As integer
insert_count = 0
update_count = 0
fetch_count = 0
'
On Error GoTo processError
Dim fcount As Long, ncount As Long
Do
fetch_count = fetch_count + 1
fcount = lconODBC.Fetch(src_flds, 1, FETCH_SIZE)
'
If fcount > 0 Then
Call ConvertFields(fcount)
For ncount = 1 To fcount
'
If lconNotes.Update(tgt_flds, ncount, 1) = 0 Then
If lconNotes.Insert(tgt_flds, ncount, 1) = 0 Then
Error 5000, "Record insert failed..."
Else
insert_count = insert_count + 1
End If
Else
update_count = update_count + 1
End If
Next
End If
'
Loop Until fcount = 0
processError:
'Error Trapping
Resume ExitSub
End Sub
End Class
'----end of code ----
There is only one field that has to be converted, all of the others are mapped and should transfer. Only the fields with NUL content are not copied/created in the target document.
Return to top
. . . . RE: target fields not created if so... (Wayne A Sobers 29.May.13)
Print this page
