Solved

Populating a field with dbcolumn

Posted on 2004-08-28
17
282 Views
Last Modified: 2013-12-18
I have a combo box that I populate using dbcolumn for a description. When the person selects the description, I want to put the price in the next box. I know how to do this in JS, but not formula

Thanks
0
Comment
Question by:GaryZ
  • 10
  • 7
17 Comments
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11924803
If you have the description and it's unique and the view's first column is sorted, you can get the price using
    price:= @dblookup("":"NoCache"; ""; "yourview"; description; "pricefieldname")
or if the price is in column 4 of the view, you can use
    price:= @dblookup("":"NoCache"; ""; "yourview"; description; 4)

You need to set the property Refresh fields on keyword change in the combobox.
0
 

Author Comment

by:GaryZ
ID: 11925337
I had tried that but it doesn't change, I must have something coded incorrectly.

For the view I have the following columns, the first column being sorted:

Stock Number
Description
Price
Description Price which has the formula   Description + "|" + Stock Number

So the combo box shows the Description Name, but has the Stock Number as the value


My combo box has the following code and I set the Refresh fields on keyword change

lutype := "Notes":"NoCache";
db :=  "";
View := "inventory";
col := 4;

@DbColumn(lutype;db;view;col)


The price field has the following code

lutype := "Notes":"NoCache";
db :=  "";
View := "inventory";
key := Description;
col := 3;

@DbLookup(lutype;db;view;key;col)


If I tried your code price := @DbLookup(lutype;db;view;key;col)  I get an error
"No main or selection expression in formula"
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11925426
Impossible. @DbLookup can only search the FIRST sorted column of a view, and description is NOT the first column. It's best to make a hidden view for this purpose, first column to contain description, second column the price.

If you use just my formula, you will indeed get an error. If it's the formula in the Value-event, it is better to use
    price:= @dblookup("":"NoCache"; ""; "yourview"; description; "pricefieldname");
    @If(@iserror(price); 0; price)
0
Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

 

Author Comment

by:GaryZ
ID: 11925515
Note:

So the combo box shows the Description Name, but has the Stock Number as the value


So it is searching the first sorted column
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11925586
Actually, you should be doing this differently. Everything goes by Stock Number, and it's unique? Then you should store that number in the document, and the other info (if necessary) as well.

Make a view, sorted by Description, with a second column containing the Stock Number. Use a @DbColumn in the combobox for selection purposes. Make a computed field StockNumber, so when the user selects a description, hence a document, you can lookup the Stock Number in the same view and store it in the document.

Make a second (hidden) view with StockNumber as first column (assuming StockNumber is NOT a number but a text field). Then lookup the price using the stocknumber in this view using @dblookup(...; stocknumber; price_field_or_column). If you ever have to refresh the document's stock info, you can reuse the Stock Number. Using a Description as a permanent key is "not done".

Thus:
- combobox shows descriptions
- find the stocknumber using the description (@DbLookup(...view1...)
- find the price using the stocknumber (@DbLookup(...view2...)
0
 

Author Comment

by:GaryZ
ID: 11925611
Can I send you the database?  I really think I am really doing what you say.

You can answer to my email address   gmorrison@myway.com
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11925853
Would not be fair towards the others, normally. When you send me the database (see my EE-profile for the address), then you automatically agree to send it to anyone who asks for it in EE. Be my guest, you can also prepare a db with only the form, and the views you prepared for the form.
0
 

Author Comment

by:GaryZ
ID: 11925880
Yes I agree with that
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11926175
Got your db. Where is the formula to compute Price? The field Price should be Computed, not Editable, unless you want to allow your users to change the price. The formula should be like the last one I gave above. Also, TotalPrice should be a Computed field, with an appropriate formula. LBNL, I think you have to retrieve the StockNumber somewhere, since that is the official key to the item.

Read the documentation on @PickList, maybe that's something for you. One parameter of @Picklist is the column that should be returned after selecting something. This could be the stock number.
0
 

Author Comment

by:GaryZ
ID: 11926340
Sorry I was playing with that and deleted the formula, here is what I had

lutype := "Notes":"NoCache";
db :=  "";
View := "inventory";
key := Description;
col := 3;

price:= @DbLookup(lutype;View;key;col);
@If(@IsError(price); 0; price)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11926404
And you changed the field to Computed, with the formula above? Please change the word "price" in the formulae to something else, e.g. lu_price or so, so it won't interfere with the name of the field itself.

Eh, dinnertime here. Be back later.
0
 

Author Comment

by:GaryZ
ID: 11926455
I sent you a new copy of the database. I played around with Picklist, but couldn't quite get it to do what I wanted.
0
 
LVL 46

Accepted Solution

by:
Sjef Bosman earned 500 total points
ID: 11926856
You were so close! You left one parameter out in the The @DbLookup-formula in Price: db (2nd parameter). By the way, the field Price is okay, the formula inside Price should be

    lutype := "Notes":"NoCache";
    db :=  "";
    View := "inventory";
    key := Description;
    col := 3;

    luprice:= @DbLookup(lutype;db;View;key;col);
    @If(@IsError(luprice); 0; luprice)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11926861
Please add Foghorn Leghorn as customer... My favourite!
0
 

Author Comment

by:GaryZ
ID: 11926895
Excellent I raised the points to 500, I think you went above and beyound on this one.

I will add Foghorn Leghorn as a customer :-)
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11926948
Thank you! *bow* Most kind!

Eh, I don't want to discourage you, because the question isn't all that difficult. I think I should have seen the mistake without opening the design... You're learning Notes the right way: trial and error. One tip: grab a book about the Notes formula language, and read ALL functions that are available, so you'll know their names. The Designer Help database contains them as well.
0
 
LVL 46

Expert Comment

by:Sjef Bosman
ID: 11926965
Last encore: @PickList will only work well from a button. You need to add a button per line, each with a different formula. The end of the button formulae is to assign a value to Description using @SetField("Description"; descr). Once again, RTFM...

Sjef :)
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
CopyAllItems causing problem with permissions 3 94
Lotus notes VB code 4 209
Lotus Domino 9.0 install on same pc with 8.5.3 9 134
Switching from Lotus Notes to Outlook 4 54
For Desktop Techs: How to retain a user's Notes configuration data when swapping out the end user's computer. (Assuming that you are not upgrading to a completely different version of Notes client) All you need to do is: 1) install Notes o…
Article by: Rob
Notes 8.5 Archiving Steps and Tips This article covers setting up a Notes archive, and helps understand some of the menu choices making setting up and maintaining a Notes archive file easier.
Established in 1997, Technology Architects has become one of the most reputable technology solutions companies in the country. TA have been providing businesses with cost effective state-of-the-art solutions and unparalleled service that is designed…
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

856 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question