CODEWITHSUNDEEP
lotus-noteslotus-formula
mjhenry
mjhenry

Reputation: 53

How to create a view using a count of certain field

I am trying to create a categorized view of all Notes documents that have a field with exact same value, i.e. there is a field for Contractor Name and I want to create a view that lists each Contractor and the documents that relevant to each. How do I do this? I have tried view formulas like the following but no success as yet

SELECT @IsAvailable(Contractorid) & @Count(Contractorid) > 1

SELECT @Count(@IsAvailable(Contractorid)) >1

Upvotes: 0

Views: 1123

Answers (2)

Frantisek Kossuth
Frantisek Kossuth

Reputation: 3524

This is similar to view to identify duplicates. My approach is to use folder.

Make a new folder with design of your view. Instead of (not working) selection formula use short LS code as an action, or QueryOpen event (make sure only one user runs the script).

The code should cooperate with another (hidden) view sorted by Contractorid. Make a ViewNavigator for that view and traverse through it with simple logic - if previous entry has the same Contractorid as the current one, put both documents into folder**. That way you will get list of all documents with duplicate Contractorid. Make sure to wipe all the current content from the folder at the start.

** This can be optimized further by slightly more complicated algorithm to handle first duplicate diferently - for more than two duplicates this algorithm makes extra calls of PutInFolder method.

Upvotes: 0

Tode
Tode

Reputation: 12060

Nsf databases are no relational databases. The count of different documents with a unique value in a specific field cannot be used to build a view selection formula.

You could write an agent, that runs through all documents and counts them and puts all with count > 1 in a folder, but this is quite a lot of LotusScript code and needs some advanced knowledge.

The other possibilit would be to categorize the view by ContractorId and add one column before that categorized column: simply use 1 as column formula and select "display totals" in the column properties as well as "hide details".

Then at least you have the information, how much documents are in each category, but unfortunately you cannot filter by it and you cannot sort by this column.

A third possibility would be to use an xpage interface, but that is even more work to do and a completely different story developmentwise.

Upvotes: 2

Related Questions