I need a computed field to look at another field on the same form and return data from a separate database. The following formula functions, but instead of returning a single value from the view column using the key, it returns ALL values from the view column. Any ideas?
@DbLookup(“Notes”:“NoCache”;
“servername”:“databasename.nsf”;
“viewname”;
“key”;
Subject: @dblookup key
if there are multiple documents matching the key, it will return multiple values.
Subject: RE: @dblookup key
There is only one document that matches the key.
Subject: RE: @dblookup key
You should tell a little bit more about this. What you are describing is not at all the normal behavior of @DbLookup. In fact, I would go so far as to say that based on what you’ve said so far, your problem is definitely not what you think it is. Please supply the following information.
What is your actual formula? Obviously the key value in your documents is not really the literal “key”, so you haven’t given us the formula you’re really using.
What is the first sorted column in your view (whether visible or hidden)? What is the formula for this column? Can you see the values in the view? Are they the values of your key?
What’s the value of the key you’re looking up? Can you prove it by using @Prompt to display its value prior to the lookup? This will also show when your formula is running, which might not be happening when you think it is.
What’s the formula for column 2 of the view, which you are trying to retrieve?
Use the document properties infobox from the view to display the field values of the one document that you believe matches your key.
You’re judging what @DbLookup returns by the value that ends up in the field. I need you to add an @Prompt right after the @DbLookup that displays the result of the @DbLookup (which you have stored in a temporary variable). Depending on the logic of your formula, the field value might not be coming just from the @DbLookup.
Subject: RE: @dblookup key
The formula is on a computed field. It reads like this:@If(ProjectNameTX = “”; “”;
@DbLookup(“”; “Notes1”:“Engineering\EngineeringCombo.nsf”; “Project Time”; ProjectNameTX; 2)
ProjectNameTX is the first field on the same form as the above computed field. It will be null or it will contain data that will match the first sorted key in the Project Name view.
Notes1 is the server name.
Engineering\EngineeringCombo.nsf is the database name where the data that I want to retrieve is stored.
Project Time is the view in the Engineering\EngineeringCombo.nsf database.
In the Project Time view, I want to retrieve data from the 2nd column.
So, basically, I want to look at the Project Time view from the Engineering\EngineeringCombo.nsf database on the Notes1 server and retrieve the data from the 2nd column when the 1st column matches the data I currently have in the ProjectNameTX field.
Right now, the formula is returning a list of ALL of the values stored in the 2nd column of the view(separated by a semi-colon).
Any thoughts?
Subject: RE: @dblookup key
What is the first sorted column in your view (whether visible or hidden)? What is the formula for this column? Can you see the values in the view? Are they the values of your key?What’s the value of the key you’re looking up? Can you prove it by using @Prompt to display its value prior to the lookup? This will also show when your formula is running, which might not be happening when you think it is. Specifically, you could use the following formula:
@Prompt([ok]; “Key lookup”; “key = <” + @Implode(@Text(ProjectNameTX); "; ") + “>”);
@If(ProjectNameTX = “”; @Return(“”); “”);
_tmp := @DbLookup(“”; “Notes1”:“Engineering\EngineeringCombo.nsf”; “Project Time”; ProjectNameTX; 2);
@Prompt([ok]; “Lookup result”; “<” + @Implode(@Text(_tmp); "; ") + “>”);
@IfError(_tmp; “No match found”)
What’s the formula for column 2 of the view, which you are trying to retrieve?
Use the document properties infobox from the view to display the field values of the one document that you believe matches your key. The field values are displayed on the 2nd tab. Find the field that’s displayed in column 2 of the view. What values does it contain?
Right now, the formula is returning a list of ALL of the values stored in the 2nd column of the view(separated by a semi-colon).
The @Prompt statements will show when the formula is actually executing. Right now, the top possibilities are:
The formula is not executing because the field already has a value. Until you have the document in edit mode and either refresh (F9) or try to save, it will not be recalculated.
ProjectNameTX actually contains a list of all the project names.
Your lookup is returning data from one document, which happens to contain multiple values in the field you’re looking up.
I swear to you that there is no way @DbLookup is returning data from more documents than the number of keys that you pass to it.
Subject: RE: @dblookup key
check to see if the field is multi-valued and that the various documents you see in the view is just because of the setting - show multiple values as seperate entries.
Subject: RE: @dblookup key
Hello,
if the value you want the DBlookup to return is stored in a field, try giving the name of the field instead of the column number. @DBlookup(“”:“”;Server:NotesDB;view;key; “Fieldname”)