• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1419
  • Last Modified:

relational operators not supported?

Guys, I have a big problem.
I have written an application which I am to demostrate to the company out our annual conference tomorrow afternoon.
I have created a local replica on my laptop to demonstrate with as the conference center will not have sufficient connectivity with out HO.
As part of the demonstration I will be showcasing the search functions of Notes. When using the server copy and index, I am able to search for docs if the contents of a number field exceeds a therotical value, however my local copy will not behave the same. I have created a local replica and local full text index, but now search function doesn't recognise the field is a number field, i.e. it wont give me usual options (is equal to, greater than etc etc) instead just offers text feild options (contains, does not contain)
Also using a previous saved (shared) search which uses this number field to find a value greater than x, notes reports the error "relational operators not supported in text fields"
What have I done wrong? is this something that cannot be done with a local replica's index?

Any help would be great, even if it is bad news. What a bombshell the night before my presentation!!!!

~scribla.
0
scribla
Asked:
scribla
  • 10
  • 5
  • 4
2 Solutions
 
Sjef BosmanGroupware ConsultantCommented:
Can you give the essential parts of the code? And what are the Notes and Domino versions of PC and server?
0
 
Sjef BosmanGroupware ConsultantCommented:
Somewhere in the back of my mind is something that says to me that if the field you refer to isn't numeric in ALL documents it appears in, search won't work. Do you have an EXACT replica of the database on uour local system?
0
 
jogii2Commented:
This can be difficult.
Somewhere in your application (Design and/or Data) the field you search for is defined as text. The first time the Indexer finds a field, it assigns a datatype to be shown with the FT-search-editor.
Getting rid of this can be outmost difficult. Bach with V3/R4 it involved agents to reasign fields, design editing of forms, compactions, new replicas etc.

Have you tried to use the [ ] FT-notation. E.g.
      [PostalCode] > 20000 & [PostalCode] < 30000
entered directly in the search field?

It's the same as
      FIELD PostalCode > 20000 & FIELD PostalCode < 30000
0
Cloud Class® Course: Certified Penetration Testing

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

 
scriblaAuthor Commented:
FYI:The field is called QuoteValue and is a numeric currency field, no decimals. It is also set to puncuate at 1000's.

If I search for:

[QuoteValue] > 1000

It causes the same error on the local replica as the saved search, but works fine on the server copy. In an effort test the theory of an incorrect datatype stored during the FT index, tried the following on the server copy of the database:

Compacted (created new copy, discarding built index views)
Deleted the FT index
Compacted (created new copy, discarding built index views)
Fixedup
Created a new FT index

The server copy still works correctly but replicas will not. No change there.
Maybe the above is not an effective method? Maybe create a new copy elsewhere on the server be a better test?

0
 
Sjef BosmanGroupware ConsultantCommented:
That doesn't help. I think there is one document (or maybe there are some of them) that still has a textvalue in that field, or some other value that is incompatible with the search. The best you can do is to put your current replica aside, and make a new replica from the server on your local pc. So:
- shut down Notes
- rename yourdb.nsf to yourdb.old
- start Notes
- create a new local replica

Then try again.
0
 
Sjef BosmanGroupware ConsultantCommented:
All field values for the field QuoteValue have to have the same definition. If, some years ago, QuoteValue used to have a different definition in the form, and one document was created with that form, the type of that QuoteValue item is different from the current items. FtSearch doesn't accept taht.
0
 
scriblaAuthor Commented:
Thats seems to be the case. I created a new copy on the server but excluded the documents. Created a few test docs, created an index, then created a local replica & index = all is well.

There are only about 100 or so docs in the database, it is all real data captured from employees and I would like to include in my presentation. Whats the easiest way of cleaning these documents, or finding the rouge? My only idea was to create a view that contained every single field and the export to CSV and then import into the new copy. Would that work? or is there some way I could do this with an agent?
0
 
Sjef BosmanGroupware ConsultantCommented:
Just help me out here: you say the server replica works, you local one doesn't, right? Then why didn't you make a new local replica form the server??

In any case, you could do the following (or something equivalent):
- make a new view, with selection formula
    SELECT !@SIsNull(QuoteValue)
- make a formula agent, that does
    x:= @TextToNumber(QuoteValue);
    Field QuoteValue:= @if(@IsNumber(QuoteValue); QuoteValue;
                                               @IsError(x); 0;
                                                x);
    ""
0
 
jogii2Commented:
Remember Design elements and documents are assigned NoteID's which are unique to the local database only, not accross replicas. I.E. a Note might have one NoteID in one database and another in the replica copy. But a lot of indexing functionality runs on NoteID sorting and hence your milage might vary from replica to replica.

And for g... sake don't mess with the server copy of the database. You don't want it to break as well, do you?

You have to doublecheck all occurances of the field in all documents and all design elements.
I also remember deleting Deletion Stubs was an additional task to perform back then.

This is not a list that allways works. It might even matter in which sequence you perform the different actions. E.G. experiment with deleting the FT-index at odd times (not to start with as I remember).

Oh, I also remember that the FT-index is bound to the UNK table.
Maybe doing gymnastics around the UNK issue will help you.

0
 
Sjef BosmanGroupware ConsultantCommented:
Typo:
    SELECT !@IsNull(QuoteValue)

Oh, and run that agent in the view you just created, with all documents selected. You could start with a few to see if nothing goes wrong. Hopefully, after that, it will be okay. If not, you might need to compact again, to get rid of the superfluous field definition.

Rouge? Rogue...
0
 
Sjef BosmanGroupware ConsultantCommented:
Before you compact, remove the FT index.
0
 
jogii2Commented:
btw, I'd never use the currency data type. How do you think your application looks outside your country?

If I remember right, currency is Type Number, just with a visual twist to show the number according to curerncy settings, but the value stored is still an ordinary number.
Try to use
     SELECT QuoteValue != "" & @TextToNumber(@Text( QuoteValue )) != QuoteValue
This way you would not run the agent against documents with values that are OK and keep replicationconflicts at minimum.
0
 
Sjef BosmanGroupware ConsultantCommented:
1) Who cares, for 100 documents in a demo database?
2) True, but...
3) if QuoteValue doesnt' contains a number-string, then @TextToNumber would generate an error and the Select would fail.

0
 
Sjef BosmanGroupware ConsultantCommented:
Maybe
    Select !@IsNull(QuoteValue) & !@IsNumber(QuoteValue)
0
 
jogii2Commented:
>sjef: Right, @Error migth disturb the selection. Your last SELECTION is better :-)

if it's a demo db do whatever neccesary, but then why create a replica? DBCopy would be ok then.
Leaves me to another idea:
Try to create a dbcopy without any documents.
Create 1 document.
FT-Index the database
Examine the result
If ok, copy/paste the rest of the documents
0
 
scriblaAuthor Commented:

With only a hour to go before I set off, my quick solutuion has been:
I have created a new (design only) copy on the server and then created a local replica on my laptop from this new copy.
I then created one document on the local replica and created a local FT index. then I have copied and pasted the other 100 or so docs from the original database and updated the index.

For now I have a local FT index built on healthy field definitions.
0
 
scriblaAuthor Commented:
lol jogii2 I did just that freaky!
sorry I did not see you post beforehand.

Sorry guys none of those selection formulas return any docs, I can't work it out.
0
 
Sjef BosmanGroupware ConsultantCommented:
It works, so don't worry. Now it's presentation time, go for it!
0
 
scriblaAuthor Commented:
Thanks guys, it's time to go. I will close this and award points but may ask how best to deal with this problem when I return next week for some more points ;)

Thank you both a bunch, I really appreciate it!

scribla.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 10
  • 5
  • 4
Tackle projects and never again get stuck behind a technical roadblock.
Join Now