Portals and the Almighty Value List

Posted on 2006-04-17
Medium Priority
Last Modified: 2010-04-27
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!

Question by:criss_mce
  • 3
  • 2
LVL 28

Expert Comment

ID: 16475828
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?

Author Comment

ID: 16478448
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.

LVL 28

Accepted Solution

lesouef earned 1500 total points
ID: 16479493
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.

Author Comment

ID: 16480196
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.
LVL 28

Expert Comment

ID: 16480532
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!

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

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

Pop up windows can be a useful feature of any Filemaker database.  Though best used sparingly, they can be employed in a multitude of different ways, for example;  as a splash screen at login, during scripted processes to control user input, as pick…
Having just upgraded from Filemaker 11 to Filemaker 12 over the weekend, we thought we would add some tips for others making the same move.  In general, our installation went without incident. Please note that this is not a replacement for Chapter 5…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Is your data getting by on basic protection measures? In today’s climate of debilitating malware and ransomware—like WannaCry—that may not be enough. You need to establish more than basics, like a recovery plan that protects both data and endpoints.…
Suggested Courses

839 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