AnsweredAssumed Answered

Combine two tables data for value list?

Question asked by sidetrack_ca on Feb 5, 2018
Latest reply on Aug 10, 2018 by nickosmh

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:, 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:, 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.