Linking many to many and excluding portal records

[dejswa]

Hello.

Some background - I have fairly advanced experience in SQL language on traditional SQL servers such as SQL server and MySQL plus experience in dealing with programming / DB's through VB.net and others. I have been thrown into a project that requires that I use FMPro version 11 - nice, but need to get up to speed quickly.

I have created a small demo program to produce functionality that I will reuse in this project. Here is the situation - just an example

A simple many to many relationship with a join table with a unique key created by two foreign keys of the two joined tables. I have enabled cascading create (but not delete) in both directions for the relationship properties. And I need to maintain referential integrity and uniqueness with my keys. I currently have a valuelist called 'foodlist' tied to the dropdown consisting of a selection of all items in the food table although this is not exactly what I want at the moment.

I created a layout based on table 'menu'. I added a portal to table 'food' to show the many items in 'food' that are linked by 'menufood'. When I manually add some data, it displays properly. My task is to allow me to do two thing in the 'menu' layout table:

1) Create a new record in 'food' and 'menufood' when I enter a new value into a dropdown field.

2) I want the dropdown to display 'food' items NOT selected already . . . to be selected by clicking the item in the dropdown. That is, for a menu containing apples & oranges, I want my dropdown to only display ONLY bananas if the food table as apples, oranges and bananas.

First, let me say that #1 above works. When I type new text in the dropdown box and hit enter, a record is both properly created in 'menufood' and in 'food'.

However, as it stands, when I click an item in the dropdown, a new item is created in both 'menufood' and 'food' and I'm sure this is functioning as expected, although not what I want. I would hope to only create a NEW record in 'food' if I type new text into the dropdown text box.

Normally I would expect to have some sort of query produce the dropdown items I want . . . such as

"SELECT food.id AS f_id, fooddescr AS descr FROM menu JOIN menufood . . . . WHERE f_id NOT IN (SELECT food.id FROM menu JOIN menufood . . . AND menu.id = 'selected _menu_id'"

That is, my selected list excludes those already selected.

I don't so much need the exact answer as I need to know where to start to learn how to script or program this. Looking again at FMPro, I get the feeling that the answer to above query for exclusion is somewhere in the 'portal setup . . . / filter portal records' area, but I cannot quite figure it out. Well, OK, maybe an exact answer would be a good start. But also need good reference book.

So . . .

1) Specifically, if the answer is simple, what is it?

2) What is a good reference (book, etc.) re doing this sort of stuff based on what I understand now?

Regards,

DJ

[comment]

First, let me say that #1 above works.

Well, not really: you can either select from existing foods only (auto-creation disabled) OR create a new food every time. It's not possible to have both, using only the built-in auto-creation method.

Re #2, see:

http://fmforums.com/forum/showpost.php?post/274656/

http://fmforums.com/forum/showpost.php?post/233897/

[bcooney]

Wish we could avoid the need for the Refresh.

Also, for the benefit of the OP, FM has both a drop-down list and a popup menu. A popup menu has the ability to show the second value only and hide the key. Drop-down lists are nice, in that they scroll (when lists get long, that is valuable). However, drop-down lists do not have the ability to hide the key, and without validation, will allow the user to type anything they want.

Often when the list choice becomes lengthy, a popup selection window is used. However, you'd have to script the validation that does not allow for duplicates in the join table. Popup Select

[dejswa]

Well, not really: you can either select from existing foods only (auto-creation disabled) OR create a new food every time. It's not possible to have both, using only the built-in auto-creation method.

OK, that explains some things. It also offers some ideas. I may be able to have the user first create the record on one side and then go to the other side and select it. My original plan was to have him create and link in one layout then go to another layout to edit all the fields of the newly created record.

Thanks again for your time to look at this. I'll take another look at and update.

DJ