A portal that shows info based on a selection in another portral

[laker_42]

Ok, i thought I could do this but need some help.

I have the following tables:

TableMetaData

ColumnMetaData

It is a one to many join between table & column meta data tables. The tablemetadata table contains all of the names of our tables in our sql database. the columnmetadata table contains all of the columns of the tables in the tablemetadata table.

Here is what I want to do:

On my tablemetadata layout, I have a portal showing the list of columns for the selected table. It is just a one column portal. I would like to have another portal on the layout that would show the details of the column selected in the first portal that lists the columns.

The second portal would show description of column, data type, etc...

I tried doing a self join to the columnmetadata table to get the second portal to work but that didn't do the trick. i know I must not have the relationship set up just right.

Can anyone point me in the right direction?

Thanks!

John

[mr_vodka]

You will have to create a new relationship from a global field to a table occurrence for ColumnMetaData.

Then when the user selects the portal record from the TableMetaData portal, it would set the global field with the selected value.

Here are some examples to get you started.

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

http://www.fmforums.com/forum/showpost.php?post/289092/

[comment]

You need to select a table actively, by placing its name (or ID) into a global field. Then use a second relationship, based on the global, to display data from the related column. See a demo file here:

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

---

Sorry, John - didn't see your post.

Edited September 23, 2008 by Guest

[laker_42]

Thanks for the quick reply & samples.

i left out one bit of info that I believe makes this more difficult. The 2 tables I referenced in my post, I am pointing to them using direct sql. So they are not FMP tables. Now, I do have some local tables that I am using for other variables. I tried creating my globals vars here & creating the relationship from there to my column table but that didn't work. Is there another way to get this info to show up or am I up a creek using the direct SQL?

[laker_42]

Any more suggestions from anyone? I am still unable to get this to work.

John

[mr_vodka]

You should still be able to create a relationship keyed from your global field in the local FileMaker table occurence to the ESS table occurrence.

[laker_42]

I thought it should work but it isn't.

Here is what I had:

TableMetaData -> columnmetadata (1st portal)

localfmptable -> columnmetadata2 (2nd portal)

localfmptable contains the global variables

I then created a button like you did in your example to populate the global variables but nothing would show up in the 2nd portal still.

Obviously I must still not have something quite right.

THanks,

John

[mr_vodka]

First, they are global fields not global variables. They are two different things.

Are you certain that the field is set to global?

[laker_42]

You are correct, global fields. Yes, they were global fields.

I don't need a meaningful relationship between the tablemetadata table & localfmptable, right? It only has to be between the localfmptable & the columnmetadata table.

[laker_42]

Nevermind, it is working now!

I went through & cleared out everything I had setup (global fields, to & relationships) and started over. It is now working like it should. Must be something was getting in teh way.

Thanks for your help & patience!

John

[mr_vodka]

No you shouldnt have to if global. Try puting a copy of your field on the layout temporarily and then manually set it with a value.

[IdealData]

There is a SYNC option when dealing with ESS tables.