Can't show related portal records

[Rob Ross]

I need some help with my relationships to display the correct information in a portal. I setup this test file for anyone who is willing to take a look at it for me.  This test file is setup with dummy restaurant data to make more sense.

I have a ::Products table where every item that we sell will ultimately be kept.  There are items like pickles, mayo, hamburger bun, meat patty, plastic cups, etc.  Some of these items are sold to the public (i.e. Apple Pie, Packet of Ranch Sauce, or an extra meat patty).  Others are not sold as separate items but will be used to make up a bundle.

Next table is ::Bundles.  It only has three fields BundleName, Qty, and AssociatedProductName.  Each bundle will contain items from the ::Products table (i.e. BundleName = "Hamburger" and it would contain items like meat patty, pickle, mayo, lettuce, Hamburger Bun.). Each of those items will have an associated costs and sell price that I want to use in calculations.

From a user perspective, they will create product into the ::Products table using the Products » Entry layout.  They will create bundles into the ::Bundles table using the Bundles » Entry layout.  When they enter a bundle and then an Associated product I want the portal (located on the same Bundle » Entry layout to show that a product has been assigned to that bundle by displaying the fields I want in the portal.

Because the ::Products table stores master product data, I don't want to create thousands of records that relate to bundles in there as that would get confusing when someone wanted to search for a product to edit it.

I have tried creating more T.O.s in an effort to get the portal to work the way I want it, but I am missing something here.

I have included screen shots of the tables, layouts, and relationship graphs as well as the file itself.  Any help would be greatly appreciated.

 

Thank You!

Rob

Bundle.fmp12

[rwoods]

Hi Rob

You need to rethink your tables in my opinion. Normally it would be setup like this :-

PRODUCTS (one record per product)
id_pk
productName
cost
price
etc

BUNDLE (one record per bundle, rather than multiple per bundle in your setup)
id_pk
bundleName

BUNDLE_ITEMS (one record for each occurrence of a product in a bundle, it's a JOIN table)
id_pk
id_product_fk
id_bundle_fk
qtyInBundle

So BUNDLE_ITEMS is a join table between products and bundles. You have one record in the BUNDLE table for each bundle, and then create join table records whenever a product features in a bundle.

I've amended your sample file with this setup, you can add products to bundle's just by choosing from the popup list!

I also added a qty field so you can have multiple qty's of a product in a bundle, and totals for each bundleItem, and a grand total for each bundle.

Enjoy.

Bundle.fmp12

Edited November 29, 2017 by rwoods

[Rob Ross]

Rwoods,

Thats perfect.  This was a sample and I was able to take the logic that you laid out and add it to my project without any hassles at all.  It works exactly as I wanted it to.  I can't thank you enough for taking the time to help me, I have learned a lot from this help.

thanks,

Rob

[rwoods]

No problem, I got a bit carried away once I started on it, I love how well join tables work once you get your head around them!

[Rob Ross]

I still am very confused by the join tables and how they work.  I am working on a project for my company and although I am learning a ton, I get frustrated at taking 20 hours to find an answer to why something does not work the way I expect only to find some little silly 5 minute thing is the reason.  I am truly thankful for experienced people like you that are willing to help a noob like me get through this.  I am sure everyone starts out like this.  I only wish there were a formal mentoring environment that would make learning quicker.  I have been going through online courses and training, but it's so much better to be able to "ask a question".  

Thank you again!

Rob

[rwoods]

Well Rob, feel free to ask for any clarification on join tables.

They are used when you have a MANY to MANY relationship. That is, in your case, where one product can be in many bundles, and each bundles can contain many products. You cannot simply join product and bundles using a direct primary key -> foreign key relationship (which is a 'normal' one to many relationship), because then one product could only feature in one bundle, or one bundle could only feature one product.

Your join table that I created has one record for each time the Product and Bundle tables need to be joined, that is when a product features in a bundle.

Join tables always have at least two foreign keys, one relates to a product in your example, and one to a bundle. Join tables should also have a primary key, allowing you to specifically reference that join table record when you need to.

Your join table should have fields that are specific to that instance of a product being in a bundle. For instance, the 'qty' field. You want to be able to have a different quantity of a specific product in each bundle. You might want one beef patty in a standard burger, but two beef patties in a quarter pounder burger.

I hope this helps, but feel free to ask other questions.