Putting Logic in a Import Function

I have some questions about this, Jaziar, for what's supposed to happen in, say, January. Should it affect December? By the time we reach August, should it still be making changes to January?

By Jan.Col(4), do you mean the JanDelta? Or, are you referring to column 4 from the spreadsheet from which you're importing?

-- b.r.t.

p.s. Question 2 would have been quicker...

No, January will never affect Decemmber - due to January being a new year.  Once we leave January and go into feb.  There is no need to update January - when we reach August -  We will update July_Act and the All months until Dec_OL.  here is no need to worry with June - May - Etc.  

Jan.Col(4), I was just saying that was a vlaue off the spreadsheet - no importance.  I dont think we are going to do Question 2.

Barry I dont mond giving more points - I understand this is a hard challenge.  But very important.

Bummer. Question 2 was easy.

OK, what happens in December? Will there be a reason to update predictions for 2006? For that matter, will there be a new spreadsheet for 2006? I believe I mentioned the other day that you might want to put a year field in these documents somewhere, and include that as part of the key that the GetDoc() function uses to determine if a document already exists. You would have to have some kind of prompt in there (just before or just after you prompt for the file name to import). Perhaps if it's not January or December, just assume it's the current year and not prompt?

From my understanding, any import in the current month updates the current month and the previous month (excepting January), plus all future months in the same calendar year, right? Should this occur only for the first import in the new month, or every time the spreadsheet is imported?

-- b.r.t.

I'm fine with the points you're offering, Jaziar. You've been more than generous, and have pushed me up to 10th place in Notes for the year.

Barry
You deserve that post.. You are also one of the guys whose answers I read on a regular basis...Keep up the good work.

-Partha

I'm humbled by your recognition, Partha. You've got more points in Notes this year than I have since I joined five years ago. I pitch in where I can, but am still learning far more than I'm teaching.

-- b.r.t.

Yes - Year has became important.

In 2006 A new excel spreadsheet will be imported.

Each Month has a plan, ol, act and delta field.

First import sets all the months plan and ol.  The Plan fields never change.  
Second import should check date for month.  If the month is equal to March - here is what happens

Jan = We dont care  
Feb_Act is set
Mar_OL is set
April_OL is set
May_OL is set
ETC

If the current month is may

Jan = Dont Care
Feb = Dont Care
Mar = Dont Care
April_Act is set
May_OL is Set
June_OL is Set

The Current Month and all future months the OL field is set
The Previus Month the Act field is set
ALL months before that the Previous Month - we dont care about, but the Act field can be reset be the spread sheet, becuase that number will remain the same.

[Plan]                                       [OL]                                                                             [Act]
   ^                                            ^                                                                                  ^
 Always set on first Import          Always the Current Month and Future Months               Always the Previous Month

Not so worried about YearEnd right now

Hope this helps

I'll look into it in a bit, Jaziar (if nobody beats me to it!). I've got to leave for a dental appointment.

Good Luck with that - I dont have about 2 hours left to my work day.  we can pick this up on Monday Morning

I believe this will do what you want it to. I also believe you'll need to adjust the index values for the colValues array. The way things are numbered now would work if columns 5 through 16 were your twelve months. But from the look of things, you have quarterly breakdowns in the middle, so it will need to be fixed.

This is, more or less, what your AdjustDoc subroutine should look like:
==== BEGIN PASTE ====
Sub AdjustDoc(doc As NotesDocument, colValues() As Variant)
      ' This function adds all the current values, whether the doc is new or existing.
      ' Coded this way to give one place to maintain code
      Dim intMonth As Integer
      
      intMonth = Month(Now)
      
      doc.Dec_OL = colValues(16)
      If intMonth = 12 Then
            doc.Nov_Act = colValues(15)
      End If
      If intMonth < 12 Then
            doc.Nov_OL = colValues(15)
            If intMonth = 11 Then
                  doc.Oct_Act = colValues(14)
            End If
      End If
      If intMonth < 11 Then
            doc.Oct_OL = colValues(14)
            If intMonth = 10 Then
                  doc.Sep_Act = colValues(13)
            End If
      End If
      If intMonth < 10 Then
            doc.Sep_OL = colValues(13)
            If intMonth = 9 Then
                  doc.Aug_Act = colValues(12)
            End If
      End If
      If intMonth < 9 Then
            doc.Aug_OL = colValues(12)
            If intMonth = 8 Then
                  doc.Jul_Act = colValues(11)
            End If
      End If
      If intMonth < 8 Then
            doc.Jul_OL = colValues(11)
            If intMonth = 7 Then
                  doc.Jun_Act = colValues(10)
            End If
      End If
      If intMonth < 7 Then
            doc.Jun_OL = colValues(10)
            If intMonth = 6 Then
                  doc.May_Act = colValues(9)
            End If
      End If
      If intMonth < 6 Then
            doc.May_OL = colValues(9)
            If intMonth = 5 Then
                  doc.Apr_Act = colValues(8)
            End If
      End If
      If intMonth < 5 Then
            doc.Apr_OL = colValues(8)
            If intMonth = 4 Then
                  doc.Mar_Act = colValues(7)
            End If
      End If
      If intMonth < 4 Then
            doc.Mar_OL = colValues(7)
            If intMonth = 3 Then
                  doc.Feb_Act = colValues(6)
            End If
      End If
      If intMonth < 3 Then
            doc.Feb_OL = colValues(6)
            If intMonth = 2 Then
                  doc.Jan_Act = colValues(5)
            Else
                  doc.Jan.OL = colValues(5)
            End If
      End If
      
      ' doc.Q1_Total_OL = colValues(8)      ' Either value 8 is first quarter, or it's April. You may need to make some adjustments.
      
      Call doc.Save(True, False)
End Sub
===== END PASTE =====

Just as this uses Month(Now) to return an integer of the current month, there is a Year(Now) function that returns a four-digit integer of the current year. (I guess that means it's not Y10K compliant...) You can use some combination of Month(Now) and Year(Now) to determine (A) whether you need to prompt for the which year to affect in, say, November through January; and (B) what year to stuff into your documents in the MakeNewDoc routine accordingly.

For the sake of simplicity, I would recommend saving the year in the documents as text rather than as a number. That way, the formula for the column in the hidden view, which uses includes the year as part of the document key, won't have to perform an @Text conversion at runtime. That will speed things up some small amount in view indexing. You can do that in your MakeNewDoc function like this:

doc.Year = Trim$(Str$(Year(Now)))

The Str$() converts the numeric year to a text string, but does so with a space at the beginning (the space that would be filled with a minus sign were the number negative). The Trim$() removes that space. (You could use Trim(Str(Year(Now))) without the $s, as those functions return variants of type String rather than Strings directly. But my coding days go back to Applesoft, and to me the functions just don't look right without them.)

Then, the formula for that column in your (Jat2 Docs) view would be:
Year + @Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

This assumes you were using the column formula I gave you in the Phase 2 question.

It also means that in the GetDoc() function, you'll need to change
strKey = ""
to
strKey = strYear

You would do this just before the for/next loop that concatenates the four colValues into strKey, near the top. This assumes that you have declared strYear somewhere, and that you have performed your prompt or done your calculation to get the year.

Aw, nuts. Here, the code all together looks like this:
==== BEGIN PASTE ====
Sub Click(Source As Button)
      Dim file As Variant
      Dim ws As New NotesUIWorkspace
      Dim xlFilename As String
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim colValues(1 To 30) As Variant
      Dim intCount As Integer
      Dim intRow As Integer
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Dim blankLine As Variant
      Dim intMonth As Integer
      Dim intYear As Integer
      Dim strYear As String
      Dim strYearss(0 To 1) As String
      Dim varYear As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
      intYear = Year(Now)
      strYear = Trim$(Str$(intYear))
      intMonth = Month(Now)
      
      If intMonth = 1 Then
            strYears(0) = Trim$(Str$(intYear - 1))      ' Previous year
            strYears(1) = strYear      ' This year
      Elseif intMonth > 10 Then
            strYears(0) = strYear      ' This year
            strYears(1) = Trim$(Str$(intYear + 1))      ' Next year
      Else
            strYears(0) = ""            ' Empty, meaning use this year without prompting
      End If
      
      If strYears(0) >< "" Then
            ' {
            varYear = ws.Prompt(PROMPT_OKCANCELLIST, "Select Year", _
            "Please indicate the year for which you are importing data:", strYear, strYears)
            ' }
            If Isempty(varyear) Then      ' User hit Cancel
                  Exit Sub
            End If
            strYear = varYear
      End If
      
     ' Connect to Excel and open the file. Then start pulling over the records.
      xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
      Set Excel = CreateObject("Excel.Application")
      Excel.Visible = False '// Don't display the Excel window
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
     ' Get the data from the first row in Excel
      blankLine = True
      intRow = 1     ' Start with 2 instead of 1 if your Excel sheet has a header row
      For intCount = 1 To 30      ' 30 will take you out to column AD
            colValues(intCount) = Trim$(Str$(xlSheet.Cells(intRow, intCount).Value))
            If colValues(intCount) >< "" Then blankLine = False
      Next
      
     ' While we don't have a blank line, create (or update) a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(strYear, db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(strYear, db, colValues)
            End If
            ' This function adds all the current values, whether the doc is new or existing.
            Call AdjustDoc(doc, colValues)
          ' Get the data from the next line.
            intRow = intRow + 1
            blankLine = True
            For intCount = 1 To 30
                  colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
                  If colValues(intCount) >< "" Then blankLine = False
            Next
      Loop
      
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Exit Sub
End Sub
===== END PASTE =====

==== BEGIN PASTE ====
Function GetDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim view As NotesView
      Dim strKey As String
      Dim doc As NotesDocument
      Dim intCount As Integer
      Dim item As NotesItem
      
      ' See if there is already a document with the same first four values
      Set view = db.GetView("(Jat2 Docs)")
      ' Create a temporary document
      Set doc = New NotesDocument(db)
      strKey = strYear
      ' Concatenate the first four colValues into a string key
      For intCount = 1 To 4
            ' Create temporary items in that document, so we can reliably get the text of the variant colValues
            Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
            strKey = strKey & Trim$(item.Text)
      Next
      If strKey = "" Then
            ' If the key is blank, let the outer routine make a new document.
            ' (This should probably never happen, by the way.)
            Set doc = Nothing
      Else
            ' Get the document that matches this key.
            Set doc = view.GetDocumentByKey(strKey, True)
            ' Note that if no document matches the key, doc = Nothing.
      End If
      ' Return the doc (or Nothing) that we found.
      Set GetDoc = doc
End Function
===== END PASTE =====

==== BEGIN PASTE ====
Function MakeNewDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Year = strYear
      doc.Group = colValues(1)
      doc.Manager_4th = colValues(2)
      doc.Area = colValues(3)
      doc.Category = colValues(4)
      doc.Jan_Plan = colValues(5)
      doc.Feb_Plan = colValues(6)
      doc.Mar_Plan = colValues(7)
      doc.Q1_Total_Plan = colValues(8)
      
      Set MakeNewDoc = doc
End Function
===== END PASTE ====

Note that the year is now being passed as a parameter to the MakeNewDoc and GetDoc functions. The test near the top of the Click subroutine is set to prompt for the year if the month is 1 (January), 11 (November), or 12 (December). That way you can deal with the previous year's data in January, and can begin importing next year's data in November. You can adjust that as necessary.

Whew!

Let me know how this works for you.

-- b.r.t.

I've just realized that the above code will NEVER set Dec_Act. You'll have to do that manually. Of course, that means there's no need to ever run this against last year's data. Which means that part of the ...

Aw, nuts.

Ignore that last post. I'll get back to this later.

-- b.r.t.

WOW - Ok I am trying to parse through all this information.  

Take your time...

It seems to be working - WOW.  I will manually have to go in to each document and add The Jan_OL and Jan_act.  BUt I think after that it will work great.  Barry you have done a great job.  And Guess what - I have yet other questions coming :).

Not regarding this stuff - but more of mailing tools.

Thanks for the points, Jaziar. At this rate, I'll be in the top nine...

You could write a one-off agent to add the Jan_OL and Jan_Act, at this point. It might be quicker than manually updating 130 documents.

Or, you might be able to get away with setting the clock/calendar on your local PC to a date in January, running the import, setting it to a date in February, running the import, and resetting it correctly.

-- b.r.t.

OK, so you've reset your date, and now you're getting duplicate docuemnts?

Yes - It did not duplicate yesterday, but today I cleaned all the documents and reset my date to January and imported.


IT worked well.  Then I set my date to Feb and imported - the logic worked but it made duplicate documents.  Here is the code I am using, maybe I messed something up

Click

Sub Click(Source As Button)
      Dim file As Variant
      Dim ws As New NotesUIWorkspace
      Dim xlFilename As String
      Dim session As New NotesSession
      Dim db As NotesDatabase
      Dim doc As NotesDocument
      Dim colValues(1 To 30) As Variant
      Dim intCount As Integer
      Dim intRow As Integer
      Dim Excel As Variant
      Dim xlWorkbook As Variant
      Dim xlSheet As Variant
      Dim blankLine As Variant
      Dim intMonth As Integer
      Dim intYear As Integer
      Dim strYear As String
      Dim strYears(0 To 1) As String
      Dim varYear As Variant
      
      Set db = session.CurrentDatabase
      
      file = ws.OpenFileDialog(False, "File List", "", "c:")
      If Isempty(file) Then
            Exit Sub
      End If
      
      intYear = Year(Now)
      strYear = Trim$(Str$(intYear))
      intMonth = Month(Now)
      
      If intMonth = 1 Then
            strYears(0) = Trim$(Str$(intYear - 1))     ' Previous year
            strYears(1) = strYear     ' This year
      Elseif intMonth > 10 Then
            strYears(0) = strYear     ' This year
            strYears(1) = Trim$(Str$(intYear + 1))     ' Next year
      Else
            strYears(0) = ""          ' Empty, meaning use this year without prompting
      End If
      
      If strYears(0) >< "" Then
          ' {
            varYear = ws.Prompt(PROMPT_OKCANCELLIST, "Select Year", _
            "Please indicate the year for which you are importing data:", strYear, strYears)
          ' }
            If Isempty(varyear) Then     ' User hit Cancel
                  Exit Sub
            End If
            strYear = varYear
      End If
      
     ' Connect to Excel and open the file. Then start pulling over the records.
      xlFilename = Cstr(File(0)) '// This is the name of the Excel file that will be imported
      Set Excel = CreateObject("Excel.Application")
      Excel.Visible = False '// Don't display the Excel window
      Excel.Workbooks.Open xlFilename '// Open the Excel file
      Set xlWorkbook = Excel.ActiveWorkbook
      Set xlSheet = xlWorkbook.ActiveSheet
      
     ' Get the data from the first row in Excel
      blankLine = True
      intRow = 1     ' Start with 2 instead of 1 if your Excel sheet has a header row
      For intCount = 1 To 30     ' 30 will take you out to column AD
            colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
            If colValues(intCount) >< "" Then blankLine = False
      Next
      
     ' While we don't have a blank line, create (or update) a document out of what we do have.
      Do While Not blankLine
            Set doc = GetDoc(strYear, db, colValues)
            If (doc Is Nothing) Then
                  Set doc = MakeNewDoc(strYear, db, colValues)
            End If
          ' This function adds/adjusts all the current values, whether the doc is new or existing.
            Call AdjustDoc(strYear, doc, colValues)
          ' Get the data from the next line.
            intRow = intRow + 1
            blankLine = True
            For intCount = 1 To 30
                  colValues(intCount) = xlSheet.Cells(intRow, intCount).Value
                  If colValues(intCount) >< "" Then blankLine = False
            Next
      Loop
      
      xlWorkbook.Close False '// Close the Excel file without saving (we made no changes)
      Excel.Quit '// Close Excel
      Set Excel = Nothing '// Free the memory that we'd used
      Exit Sub
End Sub


MakeNewDoc

Function MakeNewDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim doc As NotesDocument
      
      Set doc = New NotesDocument(db)
      doc.Form = "Jat2"
      doc.Year = strYear
      doc.Group = colValues(1)
      doc.Manager4th = colValues(2)
      doc.Area = colValues(3)
      doc.Catergory = colValues(4)
      doc.Jan_Plan = colValues(5)
      doc.Feb_Plan = colValues(6)
      doc.Mar_Plan = colValues(7)
      doc.Q1_Total_Plan = colValues(8)
      doc.Apr_Plan = colValues(9)
      doc.May_Plan = colValues(10)
      doc.Jun_Plan = colValues(11)
      doc.Q2_Total_Plan = colValues(12)
      doc.Jul_Plan = colValues(13)
      doc.Aug_Plan = colValues(14)
      doc.Sep_Plan = colValues(15)
      doc.Q3_Total_Plan = colValues(16)
      doc.Oct_Plan = colValues(17)
      doc.Nov_Plan = colValues(18)
      doc.Dec_Plan = colValues(19)
      doc.Q4_Total_Plan = colValues(20)
      Set MakeNewDoc = doc
End Function

GetDoc

Function GetDoc(strYear As String, db As NotesDatabase, colValues() As Variant) As NotesDocument
      Dim view As NotesView
      Dim strKey As String
      Dim doc As NotesDocument
      Dim intCount As Integer
      Dim item As NotesItem
      
     ' See if there is already a document with the same first four values
      Set view = db.GetView("(Jat2 Docs)")
     ' Create a temporary document
      Set doc = New NotesDocument(db)
      strKey = strYear
     ' Concatenate the first four colValues into a string key
      For intCount = 1 To 4
          ' Create temporary items in that document, so we can reliably get the text of the variant colValues
            Set item = New NotesItem(doc, "Temp" & Trim$(Str$(intCount)), colValues(intCount))
            strKey = strKey & Trim$(item.Text)
      Next
      If strKey = "" Then
          ' If the key is blank, let the outer routine make a new document.
          ' (This should probably never happen, by the way.)
            Set doc = Nothing
      Else
          ' Get the document that matches this key.
            Set doc = view.GetDocumentByKey(strKey, True)
          ' Note that if no document matches the key, doc = Nothing.
      End If
     ' Return the doc (or Nothing) that we found.
      Set GetDoc = doc
End Function


AdjustDoc

Sub AdjustDoc(strYear As String, doc As NotesDocument, colValues() As Variant)
     ' This function adds all the current values, whether the doc is new or existing.
     ' Coded this way to give one place to maintain code
      Dim intMonth As Integer
      
      intMonth = Month(Now)
      
      If Val(strYear) = Year(Now) Then
          ' We're processing the current year. Set everything as normal.
            doc.Dec_OL = colValues(19)
            If intMonth = 12 Then
                  doc.Nov_Act = colValues(18)
            End If
            If intMonth < 12 Then
                  doc.Nov_OL = colValues(18)
                  If intMonth = 11 Then
                        doc.Oct_Act = colValues(17)
                  End If
            End If
            If intMonth < 11 Then
                  doc.Oct_OL = colValues(17)
                  If intMonth = 10 Then
                        doc.Sep_Act = colValues(15)
                  End If
            End If
            If intMonth < 10 Then
                  doc.Sep_OL = colValues(15)
                  If intMonth = 9 Then
                        doc.Aug_Act = colValues(14)
                  End If
            End If
            If intMonth < 9 Then
                  doc.Aug_OL = colValues(14)
                  If intMonth = 8 Then
                        doc.Jul_Act = colValues(13)
                  End If
            End If
            If intMonth < 8 Then
                  doc.Jul_OL = colValues(13)
                  If intMonth = 7 Then
                        doc.Jun_Act = colValues(11)
                  End If
            End If
            If intMonth < 7 Then
                  doc.Jun_OL = colValues(11)
                  If intMonth = 6 Then
                        doc.May_Act = colValues(10)
                  End If
            End If
            If intMonth < 6 Then
                  doc.May_OL = colValues(10)
                  If intMonth = 5 Then
                        doc.Apr_Act = colValues(9)
                  End If
            End If
            If intMonth < 5 Then
                  doc.Apr_OL = colValues(9)
                  If intMonth = 4 Then
                        doc.Mar_Act = colValues(7)
                  End If
            End If
            If intMonth < 4 Then
                  doc.Mar_OL = colValues(7)
                  If intMonth = 3 Then
                        doc.Feb_Act = colValues(6)
                  End If
            End If
            If intMonth < 3 Then
                  doc.Feb_OL = colValues(6)
                  If intMonth = 2 Then
                        doc.Jan_Act = colValues(5)
                  Else
                        doc.Jan_OL = colValues(5)
                  End If
            End If
      Else
          ' We're processing a year other than the current year.
            If Val(strYear) = Year(Now) + 1 Then
               ' We must be processing a spreadsheet for next year. Set all the OL values.
                  doc.Jan_OL = colValues(5)
                  doc.Feb_OL = colValues(6)
                  doc.Mar_OL = colValues(7)
                  doc.Apr_OL = colValues(9)
                  doc.May_OL = colValues(10)
                  doc.Jun_OL = colValues(11)
                  doc.Jul_OL = colValues(13)
                  doc.Aug_OL = colValues(14)
                  doc.Sep_OL = colValues(15)
                  doc.Oct_OL = colValues(17)
                  doc.Nov_OL = colValues(18)
                  doc.Dec_OL = colValues(19)
            Else
               ' We must be processing a spreadsheet from last year. Set the December value only.
                  doc.Dec_Act = colValues(19)
            End If
      End If
      
     ' doc.Q1_Total_OL = colValues(8)     ' Either value 8 is first quarter, or it's April. You may need to make some adjustments.
      
      Call doc.Save(True, False)
End Sub

The obvious suspect is the GetDoc() function. If it can't find the existing document, it returns Nothing, which is a sign to the main subroutine to make a new document.

So I suspect the problem is between that and the view it uses.

What is the column formula you're using in your (Jat2 Docs) view?

-- b.r.t.

@Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Catergory)

That's the problem, Jaziar.

The long post that was two posts before the accepted answer had this in the middle:

Then, the formula for that column in your (Jat2 Docs) view would be:
Year + @Trim(Group) + @Trim(Manager4th) + @Trim(Area) + @Trim(Category)

You might want to look over that whole chunk of text, because it talks about storing the year as text rather than as a number to speed up view indexing.

The code you're using includes the year as part of that key to find the files. The view doesn't include the year at all, so it's never going to find a match.

-- b.r.t.

I have said it once and I will say it again - You are the MAN!!!

Yes it is working now!!!!!!!

Jaziar --

The difference between you and me is I've already found these problems once. It's easy to find troubles in code when you know what you're looking for, and it's easy to know what you're looking for when you've had to find it before.

Glad to help.