Portals and the Almighty Value List

Posted on 2006-04-17
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
    LVL 28

    Expert Comment

    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?
    LVL 1

    Author Comment

    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

    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.
    LVL 1

    Author Comment

    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

    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

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Conversion Steps for merging and consolidating separate Filemaker files The following is a step-by-step guide for the process of consolidating two or more FileMaker files (version 7 and later) into a single file with multiple tables. Sometimes th…
    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…
    Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custo…
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    731 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now