Displaying a field from a related table

[stefangs]

Hi all,

I have set up a file that lets me quickly filter out records in 3 hierarchy levels. All records are shown in portals. I like this sort of setup, because I find it quick and easy to navigate compared to the find function (at least if the amount of records isn't massive). I've attached an image of the main view which explains much better what I'm doing.

So I select a main category (which highlights) in portal 1, then select a subcategory (which highlights) in portal 2, and then I see the relevant records.

I want to be able to view the comments field for each type of record. It works with level 1 and level 3, but not with level 2, because I obviously have the dreaded wrong relationship here (which always shows the first record). I've tried a separate relationship, even showing just the comments field in a one line portal, but it doesn't work. The layout's focus is on the main category.

Funny it works if I do it for level 3, but that's beside the point.

Any idea of what the relationship should look like?

Thanks in advance,

Stefan

[stefangs]

More of a push really....

I now came up with a workaround - when I click the subcategory while holding a modifier, I get a pop up window where I can enter comments. That's better than nothing, but it sure would be nice to display the field on the layout. I also tried a dynamic multiline key between main and sub category but that didn't work either.

Anyone with an idea what the relationship needs to be so I can put that field on the layout?

Thanks,

Stefan

[Ron Cates]

When you click the Main Catagory it should be setting a global field with the id of the catagory. Your relationship would then be that global ID field = to the FK Catagory id field of  a table occurance of the sub catagory table. Then make sure the subcatagory portal is based on that TO. In fact, the irst thing you might want to check is that the subcatagory portal is based on the correct table poccurance in the solution as you have it. Not sure how your table occurance group and relationships are set up now but if all else is set up correctly the first thing to check is what table the subcatagory portal is based on.

[stefangs]

Hi Ron,<br /><br />thanks for jumping in. Actually, the subcategory portal shows all the correct records and the relationship works just as you outlined above. If you look at the screen shot I posted above, you'll see how it looks. The problem is the comments field which is too small inside a portal row. Would it fit in the portal, I'd be all set.<br /><br />I would like an enlarged version below the portal (where the red text in the screen shot appears that is the field I'm interested in). I'm sure I'll need an extra key, but don't know how to construct it. I tried some concatenated key using the main and subcategory field, but that failed, too!<br /><br />Stefan

[Ron Cates]

Similar concept. Set a global field with the id of the sub catagory record, create a table occurance based on subcatagory ( such as Comments_View ). Create a relationship to that TO,  global id = id then place your comments field on the layout outside of the portal based on the new table occurance. The relationship will create a 1 record result as long as you are using unique id fields in all your tables for each record. So when you place the comment field on your layout it will show the first record from the relationship which will be the only related record and thus the one you selected.

[stefangs]

Oh a global from the SUBCATEGORY to its own ID! Yep, didn't see the forest for the trees. Yes, Sir, now it works!<br /><br />Thanks a bunch!<br /><br />Stefan