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

DBlookup for populating fields

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
padillrr
Asked:
padillrr
  • 11
  • 9
1 Solution
 
HemanthaKumarCommented:
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
 
HemanthaKumarCommented:
oops

u had the formula right
0
 
HemanthaKumarCommented:
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
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
padillrrProject CoordinatorAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
HemanthaKumarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
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
 
HemanthaKumarCommented:
is LUChoicesForVendor  & Product Lookup, is in same db ? confusing !

You mentioned that there are several product dbs and one vendor db !
0
 
padillrrProject CoordinatorAuthor Commented:
THe product and vendor db are the same both those views are in the ordersProducts db
0
 
HemanthaKumarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
Sorry to be conbfusing, I have that already set.
0
 
HemanthaKumarCommented:
Use this view to do a lookup for POType Field ? Do u have problem here ?
0
 
padillrrProject CoordinatorAuthor Commented:
here theres a problem, in this view I have vendor information, POtype requires product information which is in a different view
0
 
HemanthaKumarCommented:
Paste down the selection formulas here
0
 
padillrrProject CoordinatorAuthor Commented:
This is the selection formula for that view:
SELECT Form = "Vendor"
0
 
HemanthaKumarCommented:
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
 
padillrrProject CoordinatorAuthor Commented:
Herman, I figured it out, it was a great learning experience for me.....thanks for your help gonna give you the points!
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

  • 11
  • 9
Tackle projects and never again get stuck behind a technical roadblock.
Join Now