?
Solved

DBlookup for populating fields

Posted on 2006-05-15
20
Medium Priority
?
344 Views
Last Modified: 2013-12-18
Hey Experts, this should be simple but I can't figure it out....
I have a form "Order Items" in Orders DB, I'm trying to set it up so that when the user selects a vendor (productsdb) the next picklist shows the products that vendor has to offer, here's the catch Vendors is one form in the products db while Products is a different form, the vendor and product db are tied by a vendor ID number which exist in both forms. Currently I have field ProdcutType which has this formula:  @Sort(@Unique(@DbColumn(orllnd01;"":"Products.NSF";" Product Lookup ";1))) this them fields the Item field via this formula:  @Sort(@DbLookup( orllnd01;"":"Products.nsf"; "Product Lookup"; POType;2 )) works great but it displays all products I want to be able to isolate the products by vendor. I also need to grab all the vendor information and populate a vendor information fortion of the Order Items form for printing purposes.....Ideas?
0
Comment
Question by:padillrr
  • 11
  • 9
20 Comments
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16685151
Instead of 2 in the dblookup (where u are refering a column), specify the vendor field name.

eg;
@if(vendor != "";
@Sort(@DbLookup( orllnd01;"":"Products.nsf"; "Product Lookup"; POType;vendor)); "");

~Hemanth
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16685181
oops

u had the formula right
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16685206
I am kind of confused here, u need product list by vendor selected or u want to discard this vendor provided products and list others ?

>I also need to grab all the vendor information and populate a vendor information fortion of the Order Items form for printing purposes.....Ideas?

Probably u need to setup a computed field using DBColumn and populate all the vendors list, and unhide this for print mode !

0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:padillrr
ID: 16685350
I may have not explained my self properly, The person ordering will select a vendor, based on the vendor selection then the other picklist will be based on the vendor slection, the intent is to minimize the size of the products, currently the picklist is huge because I am only separating the picklist by Type, Example:

Picklist - Vendor select Dell
Then Type will display only the products for Dell

Currently type displays all products
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16685411
Then use DBLOOKUP to use vendor as the key in the list formula

@DbLookup( orllnd01;"":"Products.nsf"; "By Vendor Lookup"; vendor; "TYPE")

The view By Vendor Lookup has to be set with vendor id as the first column (sorted)
0
 

Author Comment

by:padillrr
ID: 16685748
Tried that but keep in mind that the vendors and products are in a different db and they are different forms (this was an import from an access db the import agent creates a db/nsf file for each table in an access db) I then combined the files in both the dbs created by the import agent renamed the forms to Produts and vendors respectively. In the Product form there is a field VdrID which is also on the Vendor form. So my Vendor field is grabing data from the vendor view while the ProductType field is grabing data from the product view. There in lies my problem. The products are not reponse docs to the vendors, they simply carry the vendor ID as a field but this is not relational so I am a little confused in how to get the dblookups and dbcolumns to wrok properly
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16690988
ok, but why do u have several product dbs ? Doesn't one solve the purpose ?

Anyway, if the vendor info and product info are in two different dbs, and they are linked thru vendor id. Then to facilitate dblookup to extract product , you have to update the vendor document with the product list.
i.e. you have to update vendor doc pertaining to the vendor id when a product doc is saved !

Thus the view can show the product ids or use field name to do the lookup

0
 

Author Comment

by:padillrr
ID: 16691041
Vendor and product are on the same db, they are based on 2 different forms in that db what ties them together is the VdrID butthat is a number I want to display the actual vendor name instead of the ID which is a number, I created a view and have a column with VdrID+VdrName but I only get VrdID, I know it's because VrdName does not appear on the Product form What I need is to display the VdrName on the Product form then I think ti will work, but how do I do that when they are from 2 different forms?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16691509
Then no problem

The product view with vendor id in 1st col can be used to lookup. They can be two diff forms, but it is the view which the lookup relies on , as long as the key specified in the lookup matches view lookup will not throw the error
0
 

Author Comment

by:padillrr
ID: 16692103
I have this so far but not hte results I'm looking for:

Field POVendor formula = @Sort(@Unique(@DbColumn(orllnd01;"":"OrdersProducts.NSF";" LUChoicesForVendor ";1)))

Field POType formula = @Sort(@Unique(@DbColumn(orllnd01;"":"OrdersProducts.NSF";" Product Lookup ";1)))

This gives me C (CPU) H (Hardware) S (Software)  etc.

Field POItem formula = @Sort(@DbLookup( orllnd01;"":"OrdersProducts.nsf"; "Product Lookup"; POType;2 ))

POItem field is filtered by POType, I need the same for POType from PO Vendor.

The LUChoicesForVendor views first column is VdrName +"|"+VdrID
The Product Lookup views first column is PrdType Sorted and Categorized

I have tried to combine the product and vendor information into one view with very ugly results, what am I missing?
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16692949
is LUChoicesForVendor  & Product Lookup, is in same db ? confusing !

You mentioned that there are several product dbs and one vendor db !
0
 

Author Comment

by:padillrr
ID: 16693246
THe product and vendor db are the same both those views are in the ordersProducts db
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16699004
man, you are confusing me

Create a view which list product docs , and make the Ist col the vendor id ? Is this is a trouble ?

0
 

Author Comment

by:padillrr
ID: 16699225
Sorry to be conbfusing, I have that already set.
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16702175
Use this view to do a lookup for POType Field ? Do u have problem here ?
0
 

Author Comment

by:padillrr
ID: 16702352
here theres a problem, in this view I have vendor information, POtype requires product information which is in a different view
0
 
LVL 24

Expert Comment

by:HemanthaKumar
ID: 16703163
Paste down the selection formulas here
0
 

Author Comment

by:padillrr
ID: 16708708
This is the selection formula for that view:
SELECT Form = "Vendor"
0
 
LVL 24

Accepted Solution

by:
HemanthaKumar earned 500 total points
ID: 16736811
Did u create a new view which has selection formula for product information and Ist column sorted by vendor id ?


Ist col - vendor id
2nd col - Product information

So extract 2nd col by using vendor id !,

0
 

Author Comment

by:padillrr
ID: 16736897
Herman, I figured it out, it was a great learning experience for me.....thanks for your help gonna give you the points!
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This is an old article, please see an updated version of this article, located here: http://www.experts-exchange.com/articles/23619/Notes-8-5x-Windows-7-Notes-info-and-tips.html
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.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Despite its rising prevalence in the business world, "the cloud" is still misunderstood. Some companies still believe common misconceptions about lack of security in cloud solutions and many misuses of cloud storage options still occur every day. …
Suggested Courses

807 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