How do I build the relationship between these three tables?

[North2Alaska]

I have three external ODBC tables, TOOLS, ATTRIBUTES and TOOL_ATTRIBUTES. ATTRIBUTES is a table of all possible attributes and the TOOL_ATTRIBUTES tables assigns an attribute to a tool and captures the value of the attribute.

For example I could have a Hammer with an attribute of claw. Other attributes might be wooden handle and fiberglass handle.

Now what I would like to do is build two portals, one to display the tools and the other to display the attributes. But in the second portal I would like to display all possible attributes and then change the text (or highlight the row or some other indicator) on the rows that are in the assigned to the tool. So from the example above the second portal would list all the attributes, claw, wooden and fiberglass but only claw would be highlighted (I think I can do that with conditional formatting).

In addition I want to be able to edit the value stored in the TOOL_ATTRIBUTES table.

I've tried multiple different configurations with different TCs but for the life of me I can't see how to link the three tables together to produce the desired results. And it seems that because they are external tables my options are limited.

Any help would be appreciated.

[comment]

You need to select the tool by placing its ID in a global field in ATTRIBUTES. Then define another relationship between ATTRIBUTES and a second occurrence of TOOL_ATTRIBUTES, matching on both ToolID and AttributeID. Highlight the ATTRIBUTES records that have a related record.

I don't know if you can do all that with external tables. Also, you indicate version 8.5, but both external sources and conditional formatting are not available in that version.

[North2Alaska]

You need to select the tool by placing its ID in a global field in ATTRIBUTES. Then define another relationship between ATTRIBUTES and a second occurrence of TOOL_ATTRIBUTES, matching on both ToolID and AttributeID. Highlight the ATTRIBUTES records that have a related record.

The only options I have it to create a calculation (or summary) field in the external table, so I don't see how that would work.

I don't know if you can do all that with external tables. Also, you indicate version 8.5, but both external sources and conditional formatting are not available in that version.

Yes, that was an oversight on my part. I am using 10 advanced.

[comment]

OK, then how about this way?

HighlightAttributes.fp7.zip

[North2Alaska]

Very close. Thank you.

I have modified the file to add assignment_value to the assignments table and I would like to display it next to the attribute. When I add the field to the portal only the first values is displayed.

[North2Alaska]

Sorry, didn't attach the file. :-(

HighlightAttributes.fp7.zip

[comment]

I am afraid that's not possible using the same method. You want each attribute row to display the value corresponding to THE CURRENTLY VIEWED OBJECT - but the attribute records do not "know" which object you have selected for viewing. For this, the gObjectID field must be located in the Attributes table - as per my first suggestion.

---

EDIT:

Perhaps you can use a calculation field instead, to get the value from the global field in the Viewer table:

HighlightAttributes2.fp7.zip

Edited February 27, 2009 by Guest

[North2Alaska]

That was the answer. The last change I had to make on my version was that I had the calculation as a global variable where yours was not. When I turned off the global option it worked.

When I move this to my Windoze box, I get a lot of screen flashing. Is this a side affect of what is being done?

Edited February 27, 2009 by Guest

[comment]

Try starting the script with a Freeze Window step. Also look for a fairly recent post by LaRetta regarding minimizing flashing in general.

[North2Alaska]

Yes, I see there is a lot of discussion about this issue. I'll dig into.

And thanks again for the help.

[North2Alaska]

I would like to followup with another requirement.

The attribute value is stored in the joining table and on occasion the attribute can be repeated with a different value. An example would be color. It could be red or blue.

The combination of things that I would like to provide in the portal are:

1) list of every attribute (done)

2) ability to highlight the ones assigned (Almost. I can only see the first assigned attribute. see #4 below)

3) ability to edit the attribute value (done)

4) see all the values if multiples of the same attribute are assigned.

I've tried several things over the last couple of days and have not been able to make all these work. Any help would again be muchly appreciated.

[comment]

If I understand you correctly, you still want to to see ALL attributes in the portal - but some attributes would appear twice (or more)?

[North2Alaska]

Yes.

[comment]

I am afraid that's not possible: there is no table that has all the required records. The Attributes table has all the attributes - but only a single instance of each. The Assignments table can have duplicate attributes - but it doesn't have the unassigned ones.

Theoretically, you could assign all attributes to all objects, then add a mark to those that are assigned "for real" - and, if necessary, duplicate some of these. But I wouldn't recommend it, because it subverts the data structure in order to accommodate display requirements. And, IMHO, it wouldn't be good UI anyway.

Perhaps you should consider another approach for this, such as the attached:

PortalToPortal3.fp7.zip

[North2Alaska]

... Perhaps you should consider another approach for this...

I'm coming to the same conclusion. As always, thanks.

[North2Alaska]

I was thinking about how to re-design the interface and what you said about having all combination of values in one location. Then I started thinking about how part of the struggle is that the tables are linked to an ODBC data source. Then it dawned on me that that might also be my solution. I knew I could present the data in the form that I wanted using an SQL Statement. So, I built a view on the SQL side and linked that into FM. So far, so good.

[comment]

Well sure, SQL does unions much better than Filemaker. Still, as a user I would find this form of presentation confusing.