I am very new to Lotus so this may be easy - I just can’t figure out how to do it.
I have a database that contains a field called Category. I am trying to create a view called Unique Category that will display only the unique category values in a single column view.
I have created a view that contains one column (category) and have used SELECT @All to get the values of the category column. How do I filter this to only return unique values?
Thank you.
Subject: Create View With Unique Values
Hey David,the thing that I don’t understand here is why you need that categories. For a export/import or synchronisation scenario a notes document would be the equivalent of a row in a SQL table and all the items of that notes document hold data which would in SQL table terms be the column-values.
I somehow got the impression that you are looking for some kind of unique key that you can use for synchronisation purposes. If that’s the case I’d suggest to use the UNID (32 char unique ID of a notes document).
The content of the category field is then just translated into a given data structure on your SQL server, regardless if it contains multiple values or not.
Just an idea…
Regards,
Th.
Subject: RE: Create View With Unique Values
Part of the functionality of the current application is that each record in the Lotus app has a “category” value. The original developer created different filtered views of the data based on the category value. During the migration to SharePoint, I want to create a lookup list for the categories and then as I migrate the rest of the data, I can use the Category list as a lookup value in the master list. This allows me the freedom to create Categories separately from the rest of the data and also to create a parent/child relationship.
I created a view which I believe contains all of the records with all of the fields and I can export that to SharePoint. I could then extract the Category information from there but I thought it would be trivial to create a filtered view in Lotus and export that separately.
Subject: RE: Create View With Unique Values
I see.
If you have a limited/small number of different categories, you could just use the view selection to filter documents.
Example:
SELECT (category = “mycategory1”)
Now this would mean, that for 10 different categories you’d build 10 different views. With cut&paste this is not the most beautiful, but a working solution which works very similar to a "select * from xxx where category=“mycategory1” statement in the SQL world.
Of course, up from a certain number, this solution becomes quite silly 
Isn’t there a functionality in the export/import tool to select documents based on item values? This should be a better way to do it, imho.
Exporting via Lotus Script and selecting documents via a db.search statement comes to mind, too.
Good luck with that project,
Th.
Subject: Create View With Unique Values
There IS a way to create a view with unique keys, but it’s not necessarily a good idea for a number of reasons. What you probably want to do is to simply mark the first column of the view as Categorized:
When you access that column programmatically (as through @DbColumn), it will return only a list of unique values.
Subject: RE: Create View With Unique Values
Thank you for the quick response. If I set this up the way you explained, how do I modify the view to select based on this categorized setting? As I said - I am very new to this. I have all sorts of SQL experience but Lotus is confusing me.
Thank you in advance for your patience.
Subject: RE: Create View With Unique Values
You don’t. Since the column will reflect unique values, there is no need to worry about which documents are used. The entire class of documents containing the values you are interested in can be included – the problem of value duplication is handled at the column level and not at the view (table) level. Being familiar with relational databases is a cognitive hindrance in the Notes world (except as it applies to importing/exporting data from relational stores) – it’s best to put aside any SQL notions you may have since Notes doesn’t work that way.
Subject: RE: Create View With Unique Values
it’s best to put aside any SQL notions you may have since Notes doesn’t work that way.
As I’m finding out Thanks for all your help.
Subject: Create View With Unique Values
Views contain document collections based on your view selection formula. If you only want docs that contain the value ‘Fred’ in the field ‘Category’, then Select Category = ‘Fred’ does the job.
Keep in mind, if Category is a multi-value field and you have docs containing more than one value in the field (ie 'Fred:Barney) then you’ll see both categories in the view and there’s nothing you can do about it.
If what you want is a view containing one document per category, each with one and only one value in the field, you’ll probably need to create some kind of ‘category tracker’ form and create one of these for each value of Category. You could do this whenever a doc is saved (check for new values, create a tracker doc if something is new). This seems like a lot of work.
Why are you doing what you’re doing? Since you’re new to Notes (welcome by the way), you may be going at a project in a round about way so it might be helpful if we understood what you actually need to accomplish.
Subject: RE: Create View With Unique Values
"Why are you doing what you’re doing? "
I have been given the task of migrating a few Notes databases to MS SharePoint. Our organization hasn’t used Notes in years but there are a few databases that people still reference (we’re using client version 4.6 ). Since nobody in our IT dept. has any Lotus experience - I’ve been tasked to migrate it. I am looking at using Quest Software’s Notes Migrator tool but I still need to create views of the data to help the migration. (Sorry for the longer than necessary explanation)
Subject: Easy Solution - To Create View With Unique Values
In the View Properties, using Designer, go to Advanced (Beanie Cap), Enable the ODBC Access property: “Generate Unique Keys in Index”.
This will cause Lotus Notes/Domino to produce a single row for each row with the same value(s). Don’t be mislead by the “ODBC Access” description. You don’t need ODBC to get the results. Just open the view in either a Lotus Notes client, or Web Browser, and you will see a single row for each unique row.
I presume you are trying to get a unique “List” for SharePoint. This should do it for you.
Subject: RE: Easy Solution - To Create View With Unique Values
I accidentally found out about this solution while trying to figure out a different way of getting to the data. I have the ODBC tool (Notes SQL) so I thought I needed to enable that to get to it via Access. I had hoped to link to the data via ODBC so I could export the raw data. The ODBC tool might suffice but I haven’t figured out how to get to any embedded objects in Lotus. So - for me, the Quest Notes Migrator tool is a great help.
Thanks for everyone’s help on this board.