Portals and the Almighty Value List
Well I'm stuck again, any help would be GREATLY appreciated!
Lets start with the 4 tables
PurchaseOrder - Holds the Vendor Info, terms, dates and unique key
PurchaseOrderItems - Holds the items being purchased, includes Quantity, Category, description, part-no, unit value, total cost)
ProductList - Holds the Product Unique ID, Category, and Description.
ProductListPartNo - Holds the Product Unique ID, Vendor Name, Vendor Part No, Vendor Price
*I've had to create a portal to hold part numbers, because my client's have concerns that they have multiple vendors and want to track the product keys for each vendor*
So that's easy breezy,
The problem now lies with the almighty value list
PO_Category - Easy simply uses all the values in Product Category
PO_Description - Easy, relates to Category, and displays the related Descriptions.
PO_PartNo - Hard, here is the problem, this value list must display all the related PartNo's to the descriptions.
example
Category > Description > Part No
Pet Cat 1122 PJ Pet Store
2299 Petsmart
It seems that once i try to get 3 value lists deep, the relationship seems to break.
I'm kinda lost on this one I'm going to keep working on it, but i could use the help.
Oh one more thing, once i get this the next step is to have the PurchaseOrderItems Table Lookup the UnitCost of the item from the PartNo, Description, and Category.
Oh the joys!
Thanks for reading!
~Criss
Lets start with the 4 tables
PurchaseOrder - Holds the Vendor Info, terms, dates and unique key
PurchaseOrderItems - Holds the items being purchased, includes Quantity, Category, description, part-no, unit value, total cost)
ProductList - Holds the Product Unique ID, Category, and Description.
ProductListPartNo - Holds the Product Unique ID, Vendor Name, Vendor Part No, Vendor Price
*I've had to create a portal to hold part numbers, because my client's have concerns that they have multiple vendors and want to track the product keys for each vendor*
So that's easy breezy,
The problem now lies with the almighty value list
PO_Category - Easy simply uses all the values in Product Category
PO_Description - Easy, relates to Category, and displays the related Descriptions.
PO_PartNo - Hard, here is the problem, this value list must display all the related PartNo's to the descriptions.
example
Category > Description > Part No
Pet Cat 1122 PJ Pet Store
2299 Petsmart
It seems that once i try to get 3 value lists deep, the relationship seems to break.
I'm kinda lost on this one I'm going to keep working on it, but i could use the help.
Oh one more thing, once i get this the next step is to have the PurchaseOrderItems Table Lookup the UnitCost of the item from the PartNo, Description, and Category.
Oh the joys!
Thanks for reading!
~Criss
ASKER
I wish i could post the file,
the problem lies with the client purchasing the same part from more then one vendor.
Vendor ACB sells PartNo: 123 for $56.00
Vendor EFG sells PartNo: 123 for $29.00 (the same part but a different vendor and price)
So when my client creates a new Purchase Order they want to do it the easiest way,
first they select category that narrows the Part Descriptions down, once they pick the part description they wish to order, they would like to include a Vendor Part Number.
But say that part has two Vendor Part numbers and with different prices. you want to make sure that you have the right vendor part number for the right vendor. so i would like to have a drop down box that lists the available vendor Names and part numbers for the client to choose which would best suite there purchase.
Once they choose the Vendor Part number that suites them, it would automatically update the unit cost.
* I have a thing for making my programs as mousy as possible, i find a large portion of my clients are older men who are scared of the keyboard, or only hunt and peck. This type of thinking makes it quicker and easier for them to create orders.*
I believe what i have to do is create a calculation field in the ProductList that contains the fields from the ProductListPartNo and see if i can make a value list play nicely with the data.
the problem lies with the client purchasing the same part from more then one vendor.
Vendor ACB sells PartNo: 123 for $56.00
Vendor EFG sells PartNo: 123 for $29.00 (the same part but a different vendor and price)
So when my client creates a new Purchase Order they want to do it the easiest way,
first they select category that narrows the Part Descriptions down, once they pick the part description they wish to order, they would like to include a Vendor Part Number.
But say that part has two Vendor Part numbers and with different prices. you want to make sure that you have the right vendor part number for the right vendor. so i would like to have a drop down box that lists the available vendor Names and part numbers for the client to choose which would best suite there purchase.
Once they choose the Vendor Part number that suites them, it would automatically update the unit cost.
* I have a thing for making my programs as mousy as possible, i find a large portion of my clients are older men who are scared of the keyboard, or only hunt and peck. This type of thinking makes it quicker and easier for them to create orders.*
I believe what i have to do is create a calculation field in the ProductList that contains the fields from the ProductListPartNo and see if i can make a value list play nicely with the data.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Well i got it to work.
Thank you for the help,
I took your advice and created a Lookup field in the PurchaseOrderItems table to hold the Unique Product ID. i then created a value list off that Unique ID and the VenderNames and Part Numbers.
(Took a bunch of Playing around with new table relationship instances but it works!)
I then had to create more table relationship instances to pull the product Unit Price from the releated model number.
WOW just WOW, i LOVE relational Databases!
Thank you kindly for all the help.
Thank you for the help,
I took your advice and created a Lookup field in the PurchaseOrderItems table to hold the Unique Product ID. i then created a value list off that Unique ID and the VenderNames and Part Numbers.
(Took a bunch of Playing around with new table relationship instances but it works!)
I then had to create more table relationship instances to pull the product Unit Price from the releated model number.
WOW just WOW, i LOVE relational Databases!
Thank you kindly for all the help.
As far as I am concerned, I only "like" them and I think it's enough!
Actually, in servoy (which I also use), you would have loved the 3 concatenated fields popup lists, it would have fixed yr pb strait away.
But fm is not too bad on that, and their new link system in v8 is quite well, it is only the table multi-occurences graphic representation which is a bit hairy...
But if you're happy, so am I!
Actually, in servoy (which I also use), you would have loved the 3 concatenated fields popup lists, it would have fixed yr pb strait away.
But fm is not too bad on that, and their new link system in v8 is quite well, it is only the table multi-occurences graphic representation which is a bit hairy...
But if you're happy, so am I!
The workaround in yr case is probably to keep the part nb in the po items table, and I can't see what is difficult here.
Now, I am not 100% sure I understand yr problem! maybe posting a file would be a better idea?