Value List Formatting (with Calculation)

[Rob Ross]

I have two tables named "Products" and "Bundles" . I needed to have a user select from a drop down list either a product or a bundle by name.  I also wanted a separator inside the drop down to show the user which items where products and which were bundles.  Finally I wanted to sort each grouping (products or bundles in alphabetical order).  Here is what I want the drop down to look like and below that is where I am currently at in the process.

 

Drop Down Box

--------------------

Products

--------------------

Apple

Banana

Carrot

Hamburger

Lettuce

Onion

Tomato

----------------------

Bundles

----------------------

Apple Pie

Carrot Juice Bar

Hamburger

 

Currently, I created a third table PB_Combined which has two fields ID.pk and Name.  I have a calculation that loops through each of the tables (Products and Bundles) and populates copies the ID.pk and Name fields from both of those tables into the PB_Combined table.  The result is a single table with all of the products and bundled items with their respective ID.pk numbers.  So here is where I am stuck.

 

I want to have those items end up in a value list however I want the value list to be formatted similar to the one above or, I could prepend the items inside the value list with Bundle: or Product: so the user would know which item listed is either a product or a bundle.  I think the best way to do this is to use a calculation to populate the PB_Combined list with the text just like I have it above.  this is working today.  It requires a lot of maintenance and overhead to create and fill in the data in that PB_Combined table each time the layout is loaded or using an OnEnter script trigger where the data is created when the drop down field is entered. 

 

I have MBS Filemaker plugin and I have read about Quicklist function, but I don't seem to understand how to implement it using a script.  It seems to be exactly what I need.  use the calculation to add items to a virtual list and then show that list using a value list drop down.  Not sure how to accomplish this?

 

Any thoughts or ideas or script help I could get would be much appreciated.

 

thanks,

Rob