Subject: Need a view to return only entries with the higest value
You should capture the names and values as a separate field. Something like this:
dim uniquenames as variant
dim newvals() as string
dim itemName as notesitem
dim itemWeek as notesitem
dim itemSales as notesitem
dim i as integer, n as integer
dim h as integer, f as integer
dim salesmax as double
dim t as integer
dim week as double
with doc
set itemName = .getfirstitem(“Name”)
set itemWeek = .getfirstitem(“Week”)
set itemSales = .getfirstitem(“Sales”)
end with
t = 0
uniquenames = Evaluate(“@Unique(Name)”,doc)
n = ubound(uniquenames)
for i = 0 to n
salesmax = 0
’ Step through each name value, testing the ones that match this name.
h = ubound(itemName.values)
for f = 0 to h
if itemName.values(f) = uniquenames(i) then
if itemSales.values(f) > salesmax then salesmax = itemSales.values(f):week = itemWeek.values(f)
end if
next f
’ Drop that name and sales value in the array
redim preserve newvals(t) as string
newvals(t) = uniquenames(n) & " Week " & cstr(week) & " $" & cstr(salesmax)
t = t + 1
next i
Then stick the newvals array into an item to use in the view.
Of course this arrangement is very crude an inefficient because it updates each doc every time even if no data has changed. That is if Joe smith’s highest sales number is still Week 2, the document gets updated anyway. This is a bad practice. It would be better to introduce an intermediate step where the name/week/sales number trios are captured as a separate item. For example item SALES_SNAPSHOT could hold values {“Joe Smith$$2$$150”;“Larry Smith$$1$$200”} and so on. As you run through your big multivalue item, you compare each salesmax-and-week pair to the values from sales_snapshot for that name, and update the snaphot (and the newvals array etc) only if the week or salesmax has changed.
In sum, you’re trying to capture structured data in Notes and there’s no good way to do that. But as you can see, capturing the data in a big multivalue field up front is the wrong way to go. You should figure out a way to capture the data in structured form first, then use your multivalue field (or whatever) to display a synopsis of that data. What you have now more closely resembles the presentation of structured data than it does the storage of that data, and that’s why this seems so hard.