Merge content from two tables or SQL View?

[Rob Ross]

I am not sure if I posted this in the correct place, so please forgive me if not.

I have: FM16 Pro Advanced (Have both Mac and Windows, but I use a Mac to develop), FM16 Server.

I am trying to have a drop down value list populate with information from two different tables.  Let me explain:

I have a table called Products.  this table contains information required for an individual product that we sell.  Some of the fields inside the products table are: ID.pk, name, cost, price, etc..

I have a second table called Bundles.  This table contains multiple items from the products table that become a single sellable bundled item.  Some of the fields inside the bundle table are: ID.pk, name, BundleCost, BundlePrice, etc.  As an example a product would be any one of these items (only one): Hamburger Patty, Lettuce, Pickle, Cheese.  A Bundle would be called a Cheese Burger and it would be made up of several products like Hamburger patty, cheese, lettuce, pickle, etc.

 

I have sales people and they need to enter the items that they sell.  I have a value list (drop down) that populates from the product name field.  this is working perfectly, however I have two additional needs that I am struggling with.

 

1. Each product within the product table has a number field called (sold individually).  i use it as a true / false field.  1 = true 0 = false.  some of the products we carry can not be sold individually but are still products.  Similarly you would not sell a piece of lettuce individually, but you would still need that product on a cheese burger.  I need to populate the value list with products that have the "individual sale" field set to 1.

2. I want to include all bundle names within the bundles table in the value list as well.

A salesperson will click on the drop down and get presented with a list of all Products that contain a 1 in the "individual sale" field and all bundle names.  Alternately I want to add some additional text in the value list to show a separator to delineate between products and bundles. This is optional at this point.

 

Any direction you can point me in would really help out.  My initial thought process is to create a table that I can populate with all of the products that have the "individual sale" flag and all of the bundle names by looping through the table assigning everything to variables to be displayed.  I think this is a lot of work and could be prone to errors.

 

thanks for your direction in advance.

Rob

[Fitch]

Have you thought about adding your bundles to the product table? You could add a field to flag them as bundles.

[Rob Ross]

I have a layout where bundles are created.  You create a bundle by selecting multiple products from the product table and then saving that as a bundle.  I have provided a screen shot for better reference.  When you select a product and a quantity the product is moved to a portal and it is saved in the bundle.

thanks,

Rob

[Fitch]

That's fine, you can still build your bundles and store them that way, but also add them as products to the product table. 

[Rob Ross]

Fitch,

That might just work.  I am exploring that now and will report back.  Thank you for the suggestion.

Rob