Trimming characters from search

HCL Domino Domino Forum
1

I need to perform a search where the name of a user is compared to a list of values from a field for possible matches and returns the results via email. I created an agent in my mail file to run the query and everything should work fine once I get around the search problem. The only field I can use for the search has the canonicalized user name followed by a random set of 8 characters, so I never get a match when compared against the name I select. Since the random characters are fixed at 8 and appended at the end, is there a way for me to modify the search below to account for those extra characters. I don’t have rights to this database so I cannot create a view or anything like that.

The OwnersList field is the one I need to “trim” the extra characters from in order for the search to work.

searchFormula$ = {Form = “frmMAcct” & @Ismember(“} +picklist(0)+{”;OwnerList)}

PZ

2

Subject: Trimming characters from search

Look at @Leftback to remove the last 8 characters and return the remainder

3

Subject: RE: Trimming characters from search

Thanks Q, but do you know the equivalent in LotusScript? i’m trying tweak some existing code and was hoping it was a matter of syntax. The only thing I could find in help was “leftb” but it says not to use and doesn’t include the details for it. And it looks like the “StrLeftBack” function needs to compare two strings and I’m not sure how to accomplish that with the code I have.

4

Subject: Trimming characters from search

Your search formula is in formula language; hence why I gave you the @ function (you can put that @ function into your search code around the value you need to trim down). If you want to do it in script, you get the length of the string (Len), and then subtact 8 from that number, and perform a Left on the string for that new number (Len - 8). HTH.

5

Subject: RE: Trimming characters from search

Q,

It’s definitely script, so I don’t know why @IsMember is in there, but that works for a different query I have to run. I’m recycling code that was left behind to save me from manually having to lookup this info directly in the database. The actual search code is below. Line 4 gives me the correct search argument using picklist from the address book. But line 6 returns no matches because of the extra characters. I was hoping there was a way to tell it to exclude the extra characters from the “owners list” field right in line 4.

Set db = session.CurrentDatabase

picklist = workspace.PickListStrings( PICKLIST_NAMES )

result = picklist(0)

searchFormula$ = {Form = “frmMAcct” & @Ismember(“} +picklist(0)+{”;OwnerList)}

If MAdb.OpenByReplicaID(server, replicaid) Then

Set collection = MAdb.Search(searchFormula$, Nothing, 0)

For x=1 To collection.count

	Set doc = collection.GetNthDocument(x)

Thanks again for your response. It looks like I will need to add a few more lines of code but that is probably beyond my skill set. I will try what you suggested to see if I can make any headway though.

Thanks

6

Subject: RE: Trimming characters from search

@IsMember is there because NotesDatabase.Search uses a Formula written in Formula Language to do the searching. See my answer if you actually want a solution to the problem.

7

Subject: Trimming characters from search

Use @Begins instead of @IsMember. Q’s response would be great if you needed to trim the return from the picklist, but in your case the picklist value is the “clean” value and the documents contain the “dirty” value. If the names are truly canonical (ie, if they are “CN=Jane Smith/OU=Sales/O=ACME”), then @Contains would also work. @Contains, though, may return unwanted documents if the names are abbreviated (“Jane Smith/Sales/ACME” is contained by “Mary Jane Smith/Sales/ACME”).

8

Subject: RE: Trimming characters from search

Stan,

I replaced @IsMember with @Begins and it finally returned some hits, but the results were totally off and none of the hits were valid. But looking at the syntax for @Begins, I noticed the string and substring were in reverse order. I switched like below and it works fine now.

searchFormula$ = {Form = “frmMAcct” & @Begins(OwnerList; “} +picklist(0)+{”)}

Thank you so much!