4 Replies Latest reply on Apr 5, 2012 5:50 AM by NathanFuente

    assign fields within same table



      assign fields within same table


      Hello, I would like to build a database that consists of only 1 table but where you can assign to a field other fields of the same table. In the form view the assigned field to a field should be shown in portal row.


      Question: What kind of approach should I use?


      It seems possible to me. I myself am not a power user of FM and I’ve been desperate to find a way to get this to work.


      Please, help me if you have any suggestions for my problem



      Thanks in Advance,




        • 1. Re: assign fields within same table

          Can you explain your intended desing in more detail? What you show on the screen looks like something best implemented with two tables instead of one, but I cannot read the language so I may be missing important details here.

          • 2. Re: assign fields within same table

            Thanks. basically i want to create a dictionary. My uploaded picture shows the design of my dictionary - there it's showing the record moral with its explanation in german. Now say i would like to assign another record from the same table, for instance the record ethic to moral.


            To make this happen i have been trying to assign kp_ of my moral table to another table called assignment where the kp_ of moral table are taken as kf_ .

            But it didn't work. this way i could only assign the records moral to moral, ethic to ethic but never moral to ethic or ethic to moral.


            Maybe there's a better alternative to do it. I appreciate your posts and hope i could give you a clear idea about my project

            • 3. Re: assign fields within same table

              Ok, so you need to "cross reference" different records in the same table. That requires a "self join" and since a given record could be linked to any number of other records, it's also a "many to many" self join and that requires adding a second table.

              Define a serial number field in your table. It is tempting to match records by word name, but this can create problems should you accidentally mispell a word and then try to correct the spelling after linking it to other words. Using an auto-entered serial number will allow you to correct any such data entry errors without this problem. It also allows you to have two records with the same Word name--that may not be needed, but in some languages such as English, the same word can have more than one meaning--so you might need such a capability.

              Define this relationship:


              Dictionary::__kp_WordID = CrossReferences::_kf_DictionaryID
              ReferencedWords::__kp_WordID = CrossReferences::_kf_ReferencesID

              Enable "Allow creation of records via this relationship" for CrossReferences in the Dictonary to CrossReferences relationship.

              Dictionary and ReferencedWords are two occurrences of your existing table. In Manage | Database | relationships, make a new table occurrence of Dictionary by clicking it and then clicking the duplicate button (2 green plus signs). You can double click the new occurrence box to get a dialog to appear where you can rename the new occurrence box to be ReferencedWords.

              We have not duplicated a table. Instead, this is a new reference to the same table already present in your database.

              With this design, you can add a portal to CrossReferences on your layout and include fields in this portal from ReferencedWords. In this portal, you can place _kf_ReferencesID and format it with a list of values from Dictionary::__kp_WordID (use the WordName field as the "also use values from" field) Then, selecting a word in this value list will link it to the current record on your Dictionary Layout. If you want this action to make a two way link, you may want to use a script trigger that creates a second record in CrossReferences with the values in _kf_DictionaryId and _kf_ReferencesID reversed.

              • 4. Re: assign fields within same table

                Thanks a lot!!! This looks so interesting and I will try it out immediately! Thanks again for helping me with my problem, I really appreciate it