Collection Handling - Need Help with Logic

HCL Domino Domino Forum
1

I’m looking for suggestions for handling a data collection of documents in a view with the goal of performing a sorting function based on a few fields in each document. Each document contains product information (serial number) as well as two values: length and weight.

The goal of the database is to have a button at the top of a view which will run on all documents in the view, then create (flag and identify) groups of 3 documents where the sum of the weights are between 9 and 12 with as little variation in length between the three as possible. Each group of 3 products will then be shipped together as a set. Once a set is made, the associated records are removed from the view, and perhaps a new record is created with the serial numbers, lengths and weights of the set, listed in a new “to ship” view.

What approach would you take to performing the analysis and grouping? Once I have the logic, I can probably write a script to run on the collection each time the button is pressed, or even have a scheduled agent run each day.

Thanks for any tips you can think of!

2

Subject: Collection Handling - Need Help with Logic

Sounds like you’ve been kippered by a business manager. This is a logic/maths problem, not a Notes one.

Get your shipping manager to give you the algorithm, and only code it up yourself.

From the details already given its not fully solvable.

  1. What happens if you don’t have a multiple of 3 product items?

  2. What happens if there is no combination of product items which totals between 9 and 12?

  3. What’s the target for minimum variability of lengths? It could start well, until you are left with the last 3 items and they could be wildly different. If there’s a maximum length variability it needs to be stated. Or if the ideal is, like a line of best-fit, the root of the sum of the squares of variability, then that needs a different solution.

Currently to solve this you need to consider all factorial (n x n-1 x n-2 ) / factorial 3 possible combinations of 3 documents, select out the ones which fail the 9-12 weight test, and allocate a “goodness” length variation weighting for the remainder.

This is therefore n-cubed difficult. Dependant on how large n is, this may be computationally slow.

3

Subject: RE: Collection Handling - Need Help with Logic

Thanks for the reply…I was thinking it would not be easy with a Notes script, but I should be able to figure something out.

If no match is found or there are no multiples of 3 left, the records remain in the view until more records are created and the button is pressed again, so no result set is ok.

The length doesn’t matter as much as the weights…the “set” would be billed at the shortest length and the rest (where the shortest product runs out) the customer could throw out as it would not be useable. Typical lengths of each product we’re trying to match is 900’. Variability could be set to +/- 100’ each set.

Assuming these rules, how would the script flow? Maybe the view could be sorted by weight in ascending order and have the script assign variables to the weights of the first and last record in the view using getfirstdocument and getlastdocument, then loop through the remaining records until the third variable fits the conditions. If so, create a new record using a different form, and flag the three documents making a set to remove them from the collection. COntinue the script until all records are either flagged as a set or flagged as “analyzed” with no resulting set. These would remain in the view. Each time the button is clicked, the anayzed flag would be reset before running the new analysis.

Is there a better way to do this? The view wouldn’t have more than a few hundred records at a time, so I’m not too concerned about performance.

4

Subject: RE: Collection Handling - Need Help with Logic

I suggest:

  1. Write this in LotusScript classes, as objects that reflect the real world things, so that when the inevitable happens, and the shipping manager’s requirements change, its easier to change the algorithm being used.

  2. Don’t manipulate the documents directly in code, because these are on-disk objects which are slower to access. Instead, iterate across the documents, reading their properties from a length-sorted view, and storing them in simple LotusScript objects stored in a List.

  3. You’ll need at least these classes:

    Class Record

      m_strDocumentUniqueID as String

  m_lngWeight As Long

  m_lngLength As Long

  .....

  Sub New(doc as NotesDocument)

  .....

    End Class

    Class DespatchSet

      m_RecordsList List as Record

  ....

  Sub AddRecord(rec as Record)

  ....

  Property Get TotalWeight As Long

  ....

  Property Get LengthRange As Long

  ....

    End Class

    Class DespatchSets

      m_DespatchSetList List as DespatchSet

  ....

    End Class

  4. The algorithm could be:

    a. Scan all (say 100) items, sorted by length. Start with the shortest item - item 1

    b. Get item 2

    c. If items 1+2+3 weighs 9 or more, and items 1+ 2 + 100 weigh at least 12, then continue. Otherwise replace item 2 with next item - item 3, and repeat this step.

    d. Find lowest item that, when used with items 1+2, sums to a valid weight.

    e. When a set is found, remove the three Objects from the list and create a new DespatchSet object that contains them.

    f. Loop back to the start, and continue until no more sets can be made.

  5. This algorithm is similar to your algorithm for scanning getFirstDocument + getLastDocument, but should result in a smaller average length disparity.

  6. I just know that someone smarter will come along and do this in 2 lines of formula language now…Go for it !

5

Subject: RE: Collection Handling - Need Help with Logic

Thanks Martin,

I still a LotusScript novice, so you lost me with “Write this in LotusScript Classes”, but the step through logic you mention at the bottom seems to make sense from a sorting perspective. I’ll try to write a script in a view button and loop through the collection with a flagging method (it’s the only thing I understand at the moment) and see if it works. Might have to sub this one out if it gets too crazy.

Really appreciate your comments though…thanks!

6

Subject: RE: Collection Handling - Need Help with Logic

How would you loop through a list and evaluate all possible combinations of any 3 elements in the list? Would this require be 3 nested loops? If the sum of the weights of any 3 falls in the range, keep the element, otherwise discard. Then I could create a new doc for each “keeper” and analyze length differentials at that point. Would this make any sense?

7

Subject: RE: Collection Handling - Need Help with Logic

OK, did I mention I’m a novice? I took a different approach and attempted to obtain all possible combinations to create 3 sets from the initial collection in the view, and create a separete “set” document for each good combination. The script runs, and sets are created and listed in a new view with descending lengths of “wasted footage” based on a field formula in the “set” form.

Next thing I have to do is figure out how to prevent creating a “set” record if an item was previously included in a “set” document. I figure for each value written to the “set” document, I can maintain a list array or variable in the script and check against the list as the loops are navigated. If the item is in the list, skip the “set” creation and resume loop.

Does anyone know how to do this? I guess appendarray would be one function, but how to you check a value against the array list?

Here’s what I have so far…I had to change weight to flow, but same logic. Don’t laugh…this stuff is not easy for me =)

Sub Initialize

Dim session As New notessession

Dim collection As NotesDocumentCollection

Dim db As notesdatabase

Dim doc As NotesDocument

Dim first_doc As NotesDocument, second_doc As NotesDocument, third_doc As NotesDocument

Dim countdoc As NotesDocument

Dim i As Long, x As Long, y As Long, num As Long, counter As Long

Dim countertext As String, partnum As String

Dim sernum_1 As String, sernum_2 As String, sernum_3 As String

Dim flow_1 As Double, flow_2 As Double, flow_3 As Double, flow_sum As Double

Dim length_1 As Integer, length_2 As Integer, length_3 As Integer

'set ID counter lookup - access counter document and get value

Set db = session.CurrentDatabase

Set view=db.getview("(counter)")

Set countdoc=view.getfirstdocument

num=countdoc.label_count(0)

counter = num



Set collection = db.UnprocessedDocuments



For i = 1 To collection.Count  

	For x = 2 To collection.Count

		For y = 3 To collection.Count

			countertext = Right$("0000" + Cstr(counter),5) 'pad counter with leading zeroes to 4 places

			

			Set first_doc = collection.GetNthDocument(i) 'obtain a record in the view

			partnum = first_doc.part_num(0)

			sernum_1 = first_doc.ser_num(0)

			flow_1 = first_doc.inv_water_flow(0)

			length_1 = first_doc.length(0)

			

			Set second_doc = collection.GetNthDocument(x) 'obtain another record

			sernum_2 = second_doc.ser_num(0)

			flow_2 = second_doc.inv_water_flow(0)

			length_2 = second_doc.length(0)

			

			Set third_doc = collection.GetNthDocument(y) 'obtain another record

			sernum_3 = third_doc.ser_num(0)

			flow_3 = third_doc.inv_water_flow(0)

			length_3 = third_doc.length(0)

			

			flow_sum = Format((flow_1+flow_2+flow_3),"0.00") 'add the three values

			

			If flow_sum > 9.1 And flow_sum < 12.2 And _  'verify sum of values is within range

			sernum_1 <> sernum_2 And _ 'make sure items are distinct

			sernum_1 <> sernum_3 And _

			sernum_2 <> sernum_3 Then

				

				Set doc = New notesdocument(db)  		 ' create a document for each acceptable set

				doc.form="Set Data"  								 ' set the form name

				doc.part_num=first_doc.part_num(0)		 'populate fields

				doc.ser_num_1=sernum_1

				doc.ser_num_2=sernum_2

				doc.ser_num_3=sernum_3

				

				doc.inv_water_flow_1=flow_1

				doc.inv_water_flow_2=flow_2

				doc.inv_water_flow_3=flow_3

				

				doc.length_1=length_1

				doc.length_2=length_2

				doc.length_3=length_3

				doc.set_id=countertext

				

				Call doc.ComputeWithForm( False, False )  'compute sum of flows and length wasted

				Call doc.Save( False, False )

				

				counter = counter+1    'increase unique id counter for sets

				countdoc.label_count=counter

				Call countdoc.save(True,False)

			Else

		'do nothing

			End If

		Next y

	Next x	

Next i

End Sub