I cannot put a related field from topics_table in my layout of relationship_table, because it cannot understand that I have two different related records.
And what then do you want to see? If you want to see both related records, a portal can be used. If just one of the two, which one do you want? How do you specify that?
All in all, you need to post a much more detailed description of your relationships and table(s) before we can offer much advice.
Sorry. Here goes the more detailed description of the tables:
The idea behind my project is that of being able to make a database of knowledge that is printable in a book-like way. People would be able to just put knowledge from whatever they think it's relevant and, in the end, we'd have an option to print it for everyone to read.
The idea goes a little further when we realize that some part of the knowledge needs different fields than some other. For example, we have part of our knowledge that comes directly from a book and part of it comes from a set of rules. For the book part, the source of the knowledge fits correctly the fields "author", "year of book", "name of book", "pages", etc, but for the rules part we need some different fields, for there needs to be a "body of rules" field, which the small rule (unit of knowledge) relates to, a "date" field, for us to know if there is a newer "rule", and a "law_maker" (sorry if wrong english) field, which describes the group of people who really voted the set of rules (local, regional or state group).
Also, all this knowledge is not ordered. We could just print it in one unordered way, but it would make the book hard to read. So the idea is to also make a field name "topic", in which each person could select the "topic" that the knowledge they are submitting is related to. And each "topic" is related to another topic as their child, and so on (all but the last topic, which have no father).
For that all, there are five tables: one for the "units_of_knowledge" (uok) that are being held, one for the "units of knowledge that are from books", one for "the units of knowledge that are from rules", one for the "topics" and one for the "relationship_between_topics". They are as follow:
the table units_of_knowledge contains three field: one internal_id, one "kind" (which describes if the uok is a book or a rule), and one external_id (which describes the id of the uok in its table)
the table books and the table rules contain four fields for the relationship: one internal_id, one "kind" (global value), one "topic" (which defines the topic the subject is related to) and one "text" (just for making easier our example, lets pretend all the rest of the data is in this field)
the table topics contains two fields: internal_id and name_of_topic.
the table relationship_between_topics contains two fields: id_topic, id_parent
the basic structure is:
- the table uok relates to the tables books and rules with a double match field (I know there is an actual name for it, sorry), it being, in uok, "kind" and "external_id" and in the two others "kind" and "internal_id". Each uok's record relates to just one other record, which is or from books or from rules;
- the table relationship_between_topics relates to the table topics twice, in the sense that the id_topic and the id_parent have both a relationship to the field internal_id (of topics)
and here are my questions:
1. how can I make a layout of "relationship_between_topics" where all the relationships are presented, and in a visual way (not with the topic's ids, but with the name_of_topic?
2. what's the best way to implement the topic feature? As it is now, relating to the tables books and rules, or relating it to uok?
Thanks for the attention
1. Can a single topic be related to both a record in the "book" and the "rules" table? If there is never a link to both related tables for the same topic, you can place transparent fields from both related tables on top of each other on the layout.
If there is only one related record from a given table, you don't need to use a portal, you can add fields from the related table directly to the layout. If there are multiple related records, but each have a date in a date field and you only want to show the record with the most recent date, you can sort either the relationship or a one row portal by that date field to show data from only the most recent related record.
2. One option that you may want to consider is this set of tables and relationships:
Topics::__pkTopicID = SubTopicList::_fkSubTopicList::_fkParentTopicID
SubTopics::__pkTopicID = SubTopicList::_fkSubTopicID
Where Topics and SubTopics are two Tutorial: What are Table Occurrences? with the same data source table. SubTopicList is strictly a Join table containing the linking fields to facilitate a "many to many self join" relationship.
I'm not sure if I grasped all that you meant on idea 2, but reading your link on table occurrences I figured out how to solve. I just created two occurrences, and each was linked to one of the fk_fields in relationship_between_topics.
About my question 2, if it's better to put the topic in uok or in books/rules, I still don't know what is the best idea. It's because I know to little about fmp. Surely as I continue to develop my database I'll have better doubts. Thanks for your amazing help.