DBLookup and refresh in the UI

hcl-bot · January 24, 2006, 2:15pm

Hello All,

I am sure this is an easy fix, but I have an issue that I thought this code would do. Here is the code:

View := “(List Name View - All Names)”;

FIELD ChosenName := @If((@IsNewDoc & ContactPerson=“”);@Name([CN];SRAuthor);@Name([CN];ContactPerson));

REM;

cname:=@DbLookup(“”:“NoCache”;“”;View;ChosenName;2);

@SetField(“Comp”;@Subset(cname;1));

sname:=@DbLookup(“”:“NoCache”;“”;View;ChosenName;3);

@SetField(“SignName”;@Subset(sname;3));

@Command([ViewRefreshFields])

this is in a computed when composed field. I have buttons on the form that basically do the same thing to computed fields, with no problem. But this field here gives me a @Command UI error. I changed the filed to computed and still get the error. This field does not allow multi values, and pulls names from a view WITHIN the database, just like the other buttons on the form do. They want me to change this field so that it populates automatically, with either the SRAuthor or the ContactPerson if its filled in. What the heck am I doing wrong??

TIA

Mick

hcl-bot · January 24, 2006, 2:36pm

Subject: Field formulas

You can’t use @Command() in field formulas.

Why not put the formulas in the respective fields. E.g.,:

for field ChosenName:

@If((@IsNewDoc & ContactPerson=“”);@Name([CN];SRAuthor);@Name([CN];ContactPerson))

for field Comp:

View := “(List Name View - All Names)”;

tmp:=@DbLookup(“”:“NoCache”;“”;View;ChosenName;2);

@Subset(tmp;1)

hcl-bot · January 24, 2006, 3:45pm

Subject: RE: Field formulas

Thanks Lawrence,

Yea, I already had tried something like that, and of wich I can get one of the three fields I need, but it blows up on the next field (the one with the dblookup in it saying entry not in index)?? This is an enherited database that was given to me to maintain. Originally, in a button was the following code:

REM;

View := “(List Name View - All Names)”;

FIELD ListName:=“”;

@DialogBox(“ListNames”; [AutoHorzFit] : [AutoVertFit] );

FIELD ChosenName := ListName;

REM;

cname:=@DbLookup(“”:“NoCache”;“”;View;ListName;2);

@SetField(“Comp”;@Subset(cname;1));

sname:=@DbLookup(“”:“NoCache”;“”;View;ListName;3);

@SetField(“SignName”;@Subset(sname;1));

@Command([ViewRefreshFields])

So… when a user opens this form it would fill in the appropriate fields. Works fine this way. Now they would like to have it done automatically (All three fields ChosenName, Comp, and SignName). So I figure I should be able to update these fields individually, using the same type of code (and also, what you have mentioned), and it will not work!?! For the life of me I cannot get this to work, and I am getting very frustrated… I know it has to be something simple… thanks for your response…

Mick

hcl-bot · January 24, 2006, 4:40pm

Subject: RE: Field formulas

Your answer is in the error message “…Not found in Index…”

Look at what your key is that your working with (the string in this case), then in designer, open the View its trying to find the match with.

Make sure the match exists in the view
Make sure the View’s first column is SORTED by ascending
You should be using (hiddenviews) to run your DBLookups so that users are not tinkering with the sort(s) through the UI.

HTH

ST

hcl-bot · January 24, 2006, 9:45pm

Subject: RE: Field formulas

Thanks Stephen,

For #1 you are correct, and that is why that error is displayed. #2 already done. #3 already a hidden view with the parenthesis around them. I have checked all of these and it is not comparing the values correctly. This code basically has to look at the ContactPerson field FIRST, and if somebody is in there it uses that name else it uses the Author as the KeyValue. Then it will do a lookup on this view to find out if the person has an email address other than a notes email account (home address, other vendor ← basically these people have a Notes ID, but do not have an email account at my company, its elsewhere and they can create documents (Author access)). This is the code I have now for the ChosenName field:

KeyValue:=@If((@IsNewDoc & ContactPerson=“”);@Name([CN];SRAuthor);@Name([CN];ContactPerson));

@Prompt([OK];“Key Value”;KeyValue);

View := “(List Name View - All Names)”;

lookup := @DbLookup(“”:“NoCache”;“”; View ; KeyValue; 3);

@If(@IsError(lookup); @Return(@Prompt([Ok];“Error”;“Error locating the Service Request Author. Aborting lookup”)); lookup)

the prompt says that it is blank?? The ContactPerson field is a Names field, no multi-values pulling from the address book. I wrote some lotusscript (button) to track to see what is in the ContactPerson on the form and how it looks in the debugger is the full-blown name (i.e. CN=John DoeOU= blah, blah blah…) in the doc.value.text propertyand what I find more bizarre is that the Default field value is @Name([CN];ContactPerson)??? So basically, in that text.value field property should be “John Doe” and not the full canonalized name, and why nothing is being populated in the ChosenName field is beyond me. Also, the property value of the ChosenName field is “Text”, and not “Names”!!! Very strange…

If anyone has any ideas on how I can fix this… Thanks for the response Stephen…

Mick

hcl-bot · February 16, 2006, 10:12am

Subject: RE: Field formulas

Sorry Mick for the late response! Have you figured it out yet? I’m going to see if I can find out what the root of the prob is and post back within a days time.

ST