Joining two related tables

[stuartp]

Hi

I'm trying to join two tables, hopefully via a drop-down menu. As an example the table structure can be reduced down to the following

tableA

tableA_ID: Number (a PK field)

tableB_ID: Number (a FK field)

tableB

tableB_ID: Number (a PK field)

name: String (some text)

There is a relationship between them defined as tableA::tableB_ID = tableB::tableB_ID

I have a layout for tableA and I would like to put a popup or drop-down menu in it that displays all the values from tableB::name, and when something is selected from this menu, it sets the tableA::tableB_ID to the corresponding tableB::tableB_ID value.

Currently I can achieve this via a button that performs a script that is as follows:

New Window [ Name: "TABLE B WINDOW"; Height: 500; Width: 500; Top: 50; Left: 50 ]

Select Window [ Name: "TABLE B WINDOW"; Current file ]

Go to Layout [ “TABLE B LIST” (TableB) ] // a layout that shows a list view of all the values of tableB::name

Pause/Resume Script [ Indefinitely ] // So the user can select a value from this list

Set Variable [ $tabB_ID; Value:tableB::tableB_ID ]

Close Window [ Current Window ]

Go to Layout [ original layout ]

Set Field [ tableA::tableB_ID; $tabB_ID ]

but this is a cludge - it looks unintuitive and awkward, and the user is required to click the continue button to resume the script. Any better solutions out there?

cheers

Stuart

[aldipalo]

Welcome to the forum.

Generally, it's easier to understand your request when you are more specific about your tables and fields.

CustomerID

JobID

ClientID

ContactID

Customer Layout, etc.

Currently, there is no way in Native FM to trigger a script upon entering or leaving a field. It sounds as though you need a plugIn like Event Script (Free) or Script Fire (Shareware). If this is what you are asking?

[comment]

I'm not sure I understand your question either. Selecting a parent for a child from a drop-down menu is rather trivial (see for example:

http://fmforums.com/forum/showtopic.php?tid/198949/post/307130/#307130

), so if you need something more complex, please clarify.

[stuartp]

Thanks for the reference. Even though it worked quite well I found the first example rather complicated and fiddly - not the sort of thing you want to do if you're planning to do a lot of work with relationships. The other examples seem to work a little cleaner - they are essentially hiding a field that is set to a drop-down list behind a field that is an edit field. Both display artifacts (switch field display when the user clicks on them) but will work for what I need, so thanks again.

Its interesting to see the effort required to get this to work - is FileMaker likely to address?

[comment]

You can also use a pop-up instead of a drop-down. This will hide the ID completely from the user, so there's no need to cover it with a related field. Again, this is suitable only if the 'second field' is unique.