How to sort --- I am creating a document from the form Action of another document and while creating I need to sort the data...Please help

hcl-bot · August 4, 2008, 8:54am

Hi All,

My form Action button is called create a New Document

Here is the code for the Form Action Button that calls the Function as below

In the below function I need to sort it by category cat_#

before new document is created …Is it possible?

Function Create New Document()

Dim workspace As New NotesUIWorkspace

Dim session As New NotesSession

Dim db As NotesDatabase

Dim uidoc As NotesUIDocument, QuoteUIDoc As NotesUIDocument

Dim doc As NotesDocument, QuoteDoc  As NotesDocument

Dim Server As String

Dim Pos As Integer

Dim NewPos As Integer

Dim FieldName As String

Dim rows As Integer

rows = 10  '--- number of rows & columns

Err = 0

VALIDATIONERROR = 4412



msg =  "You need to be connected to the Server to Create a Quote." &_

Chr$(13) & Chr$(13) & "Are you currently on-line ?"      

flag = Messagebox (msg, MB_ICONQUESTION + MB_YESNO, "On-line Reminder")

If (flag = IDNO) Then                    

	Exit Function

End If

'-----------------------get current document -----------------------------------------------------------

Set uidoc = workspace.CurrentDocument

Set doc = uidoc.Document

Set db = session.CurrentDatabase

Server = doc.QuoteServer(0)    



Call session.SetEnvironmentVar ("BDPSQuoteFromPAF","Y")

Call session.SetEnvironmentVar ("BDPSfiscalyear",doc.FiscalYear(0))

'------------------------ compose new document ------------------------------------------------------------------

Set QuoteUIDoc =   workspace.ComposeDocument(Server, "Bdps\BDPS Price Quotes.nsf", "Quotes")

If (QuoteUIDoc Is Nothing) Then

	Messagebox "Unable to create new  document.   Make sure you are on-line.", MB_ICONSTOP, "Error"

	Call session.SetEnvironmentVar ("BDPSQuoteFromPAF","")

	Call session.SetEnvironmentVar ("BDPSfiscalyear","")

	Exit Function

End If   

Set Quotedoc = QuoteUIDoc.document

'----------------------------- init fields------------------------------------------

Quotedoc.Company = doc.Company

Quotedoc.Address1 = doc.Address1

Quotedoc.Address2 = doc.Address2

Quotedoc.City = doc.City

Quotedoc.State = doc.State

Quotedoc.Zip = doc.Zip

Quotedoc.Country = doc.Country

Quotedoc.AccountExec = doc.srname

Quotedoc.PAFno = doc.PAFno

Quotedoc.WhoCanRead = doc.WhoCanRead

 '--- add these fields to get back to this document ---

Quotedoc.PAFunid = doc.UniversalID

Quotedoc.PAFserver = db.Server     



For k = 0 To (rows-1)

	FieldName = "cat_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	If TField(0) = "" Then

		Exit For

	End If          

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	FieldName = "Desc_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	FieldName = "lot_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	NewPos = 0

	For Pos = 0 To (rows-1)

		FieldName = "pr"  + Trim(Cstr(k))   + "_" + Trim(Cstr(Pos))

		TField = Doc.GetItemValue(FieldName)

		If TField(0) = "" Then

		Else

			FieldName = "pr"  + Trim(Cstr(k))   + "_" + Trim(Cstr(NewPos))

			Call Quotedoc.ReplaceItemValue(FieldName, TField)

			

			FieldName = "vb"  + Trim(Cstr(k))   + "_" + Trim(Cstr(Pos)) 

			TField = Doc.GetItemValue(FieldName)

			FieldName = "vb"  + Trim(Cstr(k))   + "_" + Trim(Cstr(NewPos)) 

			Call Quotedoc.ReplaceItemValue(FieldName, TField)

			NewPos = NewPos +1

		End If             

	Next     

Next



Call QuoteUIdoc.reload

Call QuoteUIdoc.refresh

'-------------------------------------------------------------------------------------

uidoc.Close

End Function

Thanks in advance,

ac ac

hcl-bot · August 4, 2008, 8:55am

Subject: Any chance you can stop posting your question over and over?

3 times in less than 24 hours…

Date

Topic

08/01/2008

Sort ----Need help

08/04/2008

How to sort data in a form (which has 10 rows and 10 columns ) from preexisting data (in another form with 10 rows and 10 columns) —Please help

08/04/2008

RE: Sort ----Need help

08/04/2008

How to sort — I am creating a document from the form Action of another document and while creating I need to sort the data…Please help

I found an answer by searching this forum for lotusscript and sort, it suggested searching the sandbox for sort, I did and it found lots of sort methods…

http://www-10.lotus.com/ldd/sandbox.nsf/Search?SearchView&Query=sort&SearchOrder=0&Start=1&Count=100

hcl-bot · August 4, 2008, 11:14am

Subject: How to sort — I am creating a document from the form Action of another document and while creating I need to sort the data…Please help

Sorry Carl,

I looked into the posting , I found Qsorter , from Sandbox,

but how could I implement this with my current Form Action button code…

Do I have to make an array of 10 columns in a row , and since there are 10 rows , how do I go about it…

Every row has 10 fields (coulmns) and I need to sort it with the First Field cat_#

and there are 10 rows …Each row contains fields that has values based on cat_# , so If I sort cat_# , it should replace the entire row with that category number …and the sorting should be automatic without user selecting any cat_# …

In the code before it creates a new document in a differnt database , can you sort before it replaces the value…

Please help, how can I achiev this.

Thanks in advance

ac ac

hcl-bot · August 4, 2008, 12:09pm

Subject: RE: How to sort — I am creating a document from the form Action of another document and while creating I need to sort the data…Please help

I have posted another way in my blog, a link to which appears below.

It might take an hour or so to show up there by replication.

Andre Guirard, IBM/Lotus Development

Useful blog: Best Practice Makes Perfect

hcl-bot · August 5, 2008, 8:11am

Subject: RE: How to sort — I am creating a document from the form Action of another document and while creating I need to sort the data…Please help

Hi Andre,

I just looked into the tablesorter.lss , I just need to ask you , if you could help me , Where and how can I import .lss file , I tried importing as a script library , I dod not see the option to import…

Secondly in the Sort button, I need to add this line of code

Call SortATable(uidoc, "cat,lot,cst,vb0_0,vb0_1,vb0_2,vb0_3,vb0_4, vb0_5,vb0_6, vb0_7, vb0_8, vb0_9,Desc, h0_0,h0_1,gp,PercentIncrease, pr0_0, “cat”, 10, “_”, True)

Your code was

Call SortATable(uidoc, "keys, "DueDate, “Comment,OrderAmt”, “Key”, 5, “_”, True)

What if I have DueDate0_0, DueDate0_1 , across (as columns ) and I have 10 rows , The 5 number I would replace it by 10, but how will I replace DueDate0_0 …and so on…

Please help

Thanks in advance,

ac ac

hcl-bot · August 4, 2008, 11:56am

Subject: You’ll want to put your values into an array

You’ll want to put your values into an arraySort the array

Then put the sorted values back into the fields

hcl-bot · August 4, 2008, 3:02pm

Subject: RE: You’ll want to put your values into an array

Since I have 120 fields 10 rows and 10 columns …each row has 12 Fields and 2nd row has another set of 12 fields and so on …

How will I create an array of these fields.?

and here is my code , I want to modify this code so that it does the sort first and then places the sorted values in the Fields…

Also I don’t need to sort all the Fields in a row , only first Field that is Cat_# and corresponding values should be sorted and reordered automatically as per the cat_#'s

Here is the code currently without the sort (the way it is , it brings in data)

I want to use the same code , but just modify for the sort…and can it be sorted before composing as in the code below…

Please help

Thanks fr all your help & responses.

ac ac

Dim workspace As New NotesUIWorkspace

Dim session As New NotesSession

Dim db As NotesDatabase

Dim uidoc As NotesUIDocument, QuoteUIDoc As NotesUIDocument

Dim doc As NotesDocument, QuoteDoc  As NotesDocument

Dim Server As String

Dim Pos As Integer

Dim NewPos As Integer

Dim FieldName As String

Dim rows As Integer

rows = 10  '--- number of rows & columns

Err = 0

VALIDATIONERROR = 4412



msg =  "You need to be connected to the Server to Create a Quote." &_

Chr$(13) & Chr$(13) & "Are you currently on-line ?"      

flag = Messagebox (msg, MB_ICONQUESTION + MB_YESNO, "On-line Reminder")

If (flag = IDNO) Then                    

	Exit Function

End If

'-----------------------get current document -----------------------------------------------------------

Set uidoc = workspace.CurrentDocument

Set doc = uidoc.Document

Set db = session.CurrentDatabase

Server = doc.QuoteServer(0)    



Call session.SetEnvironmentVar ("BDPSQuoteFromPAF","Y")

Call session.SetEnvironmentVar ("BDPSfiscalyear",doc.FiscalYear(0))

'------------------------ compose new document ------------------------------------------------------------------

Set QuoteUIDoc =   workspace.ComposeDocument(Server, "Bdps\BDPS Price Quotes.nsf", "Quotes")

If (QuoteUIDoc Is Nothing) Then

	Messagebox "Unable to create new Quote document.   Make sure you are on-line.", MB_ICONSTOP, "Error"

	Call session.SetEnvironmentVar ("BDPSQuoteFromPAF","")

	Call session.SetEnvironmentVar ("BDPSfiscalyear","")

	Exit Function

End If   

Set Quotedoc = QuoteUIDoc.document

'----------------------------- init fields------------------------------------------

Quotedoc.Company = doc.Company

Quotedoc.Address1 = doc.Address1

Quotedoc.Address2 = doc.Address2

Quotedoc.City = doc.City

Quotedoc.State = doc.State

Quotedoc.Zip = doc.Zip

Quotedoc.Country = doc.Country

Quotedoc.AccountExec = doc.srname

Quotedoc.PAFno = doc.PAFno

Quotedoc.WhoCanRead = doc.WhoCanRead

 '--- add these fields to get back to this document ---

Quotedoc.PAFunid = doc.UniversalID

Quotedoc.PAFserver = db.Server     



For k = 0 To (rows-1)

	FieldName = "cat_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	If TField(0) = "" Then

		Exit For

	End If          

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	FieldName = "Desc_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	FieldName = "lot_" + Trim(Cstr(k))

	TField = doc.GetItemValue(FieldName)

	Call Quotedoc.ReplaceItemValue(FieldName, TField )

	

	NewPos = 0

	For Pos = 0 To (rows-1)

		FieldName = "pr"  + Trim(Cstr(k))   + "_" + Trim(Cstr(Pos))

		TField = Doc.GetItemValue(FieldName)

		If TField(0) = "" Then

		Else

			FieldName = "pr"  + Trim(Cstr(k))   + "_" + Trim(Cstr(NewPos))

			Call Quotedoc.ReplaceItemValue(FieldName, TField)

			

			FieldName = "vb"  + Trim(Cstr(k))   + "_" + Trim(Cstr(Pos)) 

			TField = Doc.GetItemValue(FieldName)

			FieldName = "vb"  + Trim(Cstr(k))   + "_" + Trim(Cstr(NewPos)) 

			Call Quotedoc.ReplaceItemValue(FieldName, TField)

			NewPos = NewPos +1

		End If             

	Next     

Next



Call QuoteUIdoc.reload

Call QuoteUIdoc.refresh

'-------------------------------------------------------------------------------------

uidoc.Close

hcl-bot · August 4, 2008, 4:29pm

Subject: Take a look at Andre’s response

hcl-bot · August 5, 2008, 8:17am

Subject: Take a look at Andre’s response

Hi Andre

Thanks for your solution ,

Secondly in the Sort button, I need to add this line of code

Call SortATable(uidoc, "cat,lot,cst,vb0_0,vb0_1,vb0_2,vb0_3,vb0_4, vb0_5,vb0_6, vb0_7, vb0_8, vb0_9,Desc, h0_0,h0_1,gp,PercentIncrease, pr0_0, “cat”, 10, “_”, True)

Your code was

Call SortATable(uidoc, "keys, "DueDate, “Comment,OrderAmt”, “Key”, 5, “_”, True)

What if I have DueDate0_0, DueDate0_1 , across (as columns ) and I have 10 rows , The 5 number I would replace it by 10, but how will I replace DueDate0_0 …and so on… DueDate0_9

My second row has Fields DueDate1_0, DueDate1_1 and so on… DueDate1_9

and I have 10 rows,(total)

My last row has columns DueDate9_0, DueDate9_1…and so on …DueDate9_9

Also my numbering starts with 0 to 9 and what you have an example , I guess it starts with 1 to 5

If I use the above code , how will my numbering be as it starts with 0 being the First row and 9 being the 10 th row

Please help

Thanks in advance,

ac ac

hcl-bot · August 5, 2008, 11:54am

Subject: RE: Take a look at Andre’s response

So you have to change the way the code I posted comes up with the field names. I got you 98% of the way there. You can manage this last little bit. If not, I will have to ask you to turn in your Domino Developer badge.

hcl-bot · August 5, 2008, 12:38pm

Subject: RE: Take a look at Andre’s response

Thanks Andre, for helping me quite a lot with the code, but I have never ever done multidimensional array sort and since I have so many Fields across I am confused …Do I have to capture all the fields in the Call SortATable(uidoc, “FIELDS” options ) and also my numbering of the row starts with 0 and ends at 9 …(whereas your code , I guess it starts with 1 and ends at 5…)

Please help,

Thanks in advance,

ac ac

hcl-bot · August 6, 2008, 6:56am

Subject: RE: Take a look at Andre’s response

Check out my latest blog entry then.

Andre Guirard, IBM/Lotus Development

Useful blog: Best Practice Makes Perfect

hcl-bot · August 6, 2008, 9:45am

Subject: Take a look at Andre’s response —I am getting this error —Type Mismatch

Hi Andre,

I created a Script liblrary of Tablesorter.lss that you posted , and on the click event of the Form Action Sort button , I have this code written,

I am getting an error “Type Mismatch”

Sub Click(Source As Button)

Dim session As New NotesSession

Dim ws As New NotesUIWorkspace

Dim db As NotesDatabase

Dim uidoc As NotesUIDocument



Set db = session.CurrentDatabase

Set uidoc = ws.CurrentDocument

Call SortATable(uidoc, "cat_#, lot_#, cst_#, vb#_0, vb#_1, vb#_2, vb#_3, vb#_4,  vb#_5, vb#_6,  vb#_7,  vb#_8,  vb#_9, Desc_#, h#_0, h#_1, h#_2, h#_3,h#_4, h#_5, h#_6, h#_7, h#_8, h#_9, gp#_0, gp#_1,gp#_2,gp#_3,gp#_4,gp#_5,gp#_6,gp#_7,gp#_8,gp#_9,PercentIncrease_#, pr#_0, pr#_1,pr#_2,pr#_3, pr#_4,pr#_5,pr#_6,pr#_7,pr#_8, pr#_9", "cat_#", 10, myMangle, True)

End Sub

When I run it < I get this error at the Call SortATable —line —Type Mismatch

Can’t figure it out whi I am getting this error…

I can’t even run the tablesorter2.lss in debug mode

Also the fields cst_#, h#_0 to h0_9 , gp0_0 to gp0_9, pr0_0 to pr0_9 are all number fields…

Please help,

Thanks in advance.

ac ac

hcl-bot · August 6, 2008, 10:19am

Subject: RE: Take a look at Andre’s response —I am getting this error —Type Mismatch

Your first problem is that you forgot to use Option Declare. I suggest you use the programmer pane properties to set the option that has this statement inserted for you automatically in new code. It will save you from many errors.

Second, you didn’t read everything that I wrote in my blog entry. myMangle is not a magic word. It’s a variable name. You didn’t assign it a value. The value you need to assign it is a object of a class you define. I showed the exact code for defining the class in my blog entry. I showed the assignment of myMangle.

I don’t understand why you should have any trouble in debugging the second lss. You must of course use the code in the second lss for this, because the first lss isn’t expecting any object as a parameter.

You really need to get someone there who knows LotusScript, to help you. We can’t look over your shoulder from here.

hcl-bot · August 7, 2008, 8:02am

Subject: RE: Take a look at Andre’s response —I am getting this error —Type Mismatch

Hi Andre,

I just read the code and my Script Library , I included the tablesorter2.lss in the Declaration statement …In my Sort Button on the form , I am not sure when I need to add Option Declare

Also as per your code , where do I need to add these statements …I see we do not have this class FNMSubZBase in tablesorter2.lss

Class FNMSubZBase

  Function Mangle(fname$, Byval row%) As String 

          Mangle = Replace(fname, "#", Cstr(row-1)) 

  End Function

End Class

Dim myMangle As New FNMSubZBase

Call SortATable(uidoc, “cat#, lot#, cst#, vb#_0, vb#_1, vb#_2, vb#_3, vb#_4, vb#_5, vb#_6, vb#_7, vb#_8, vb#_9, Desc#, h#_0, h#_1, gp#, PercentIncrease#, pr#_0”, “cat#”, 10, myMangle, True)

For i = 1 To m_rows

  Set record = New sortingRecord(Ubound(m_keys), Ubound(m_fields)) 

  For k = 0 To Ubound(m_keys) 

          fname = m_callback.Mangle(m_keys(k), i) 

          record.keys(k) = doc.GetItemValue(fname)(0)

  Next

Where do I put the above code?

If you could help me out… I will appreciate it…

Also What value do i have to assign myMangel , Is it "#" , not sure , because my other fields could be just Fieldname#…

Not sure , I am confused with myMangle

Please help

Thanks in advance

ac ac

hcl-bot · August 7, 2008, 9:42am

Subject: RE: Take a look at Andre’s response —I am getting this error —Type Mismatch

where do I need to add these statements …I see we do not have this class FNMSubZBase in tablesorter2.lss

Class FNMSubZBase

…

End Class

The above goes in the (Declarations) section of your code

Dim myMangle As New FNMSubZBase

Call SortATable(uidoc, “cat#, lot#, cst#, vb#_0, vb#_1, vb#_2, vb#_3, vb#_4, vb#_5, vb#_6, vb#_7, vb#_8, vb#_9, Desc#, h#_0, h#_1, gp#, PercentIncrease#, pr#_0”, “cat#”, 10, myMangle, True)

This goes wherever you want to sort the table contents.

The code that starts with For i = 1 To m_rows is already in the .lss file. I was just discussing these lines. You don’t need to copy them anywhere.

Also What value do i have to assign myMangel , Is it "#" , not sure , because my other fields could be just Fieldname#…

The statement Dim myMangle As New FNMSubZBase assigns the variable. That’s what the word New is for there. The variable is an object, like NotesDocument or NotesDatabase, but of a custom type rather than one of the builtin classes.

I’d like to suggest that you go over this code very carefully and figure out how it works. This will teach you a lot about LotusScript. It will also let you fix any further problems on your own, which is important because this is my last response in this thread. You’ve been doing Notes development for at least 3 1/2 years – this stuff should not be such a mystery to you.

hcl-bot · August 7, 2008, 2:56pm

Subject: RE: Take a look at Andre’s response —I am getting this error —Type Mismatch

Thanks Andre,

Now I understood, and it works …Your code works just fine , I did not had to change anything …

I owe U a big time treat …

Thanks for helping me with all the code…

I am little weak in classes …I have never understood classes…

Regards,

ac ac