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.
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!

Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Yes, cascading relationship only works if you force calculated fields to be updated, and they are only if you display their value.
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?
criss_mceAuthor Commented:
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.

my first idea is to make a popup from a calc field = part nb & supplier, or
a combined popup showing part nb as 1st field and supplier as 2nd. or
make a relationship between the part nb in the order and the parts table to show suppliers of the current part.
I still think it would be easier with the file.
how big is it? under 5MB, you can mail it to me s at lesouef dot net.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
criss_mceAuthor Commented:
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.
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!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
FileMaker Pro

From novice to tech pro — start learning today.