2 Replies Latest reply on Mar 1, 2009 10:03 AM by preston56

    Self Join not working.



      Self Join not working.


      I know I can be slow sometimes, but this one has me frustrated. In a previous post I was trying to resolve my problem to no end. Here it is.


      Field equals "CSE Name" (text) These are unique due to an employee number being part of the text.

      Field equals "CSE Phone" (text) These are also unique


      I wanted the "CSE Phone" field to auto enter the number once I filled in the "CSE Name" field. I basically create new records now by just duplicating related records. The only time this is nedded is when a CSE changes in a particular record. Theire are over 260 CSEs and hunting down the correct phone number is daunting.


      I am running a flat DB containing over 65 fields with over 4,000 records. There is only one table. I have tried to perform the self join relationship and it doesn't work. I am not familiar with the technical jargon or formulas. I have been successful with maintaing things up to now using the tools (i.e. create tables, create value list). These require no "formula" explanation. I followed the example in the FM9 Pro help file and it does not work.


      Please keep it simple, I do not work regularly with DB phrases or terminolgy. Any assistance will be greatly appreciated.

        • 1. Re: Self Join not working.

          howdy Preston56,

          Thanks for the post.


          I think maybe it's my turn to be slow, but let me ask a couple of questions first before I write something foolish (some would say it's too late for that).


          1. If CSE Name is unique, and you're creating a new record with a unique CSE Name, where are you trying to get the CSE phone from?  What would this new, unique field be linked to?...and how?  (but perhaps that's your question...)


          2. If you are duplicating a record to make the new record, isn't the phone# already there?  If you're duplicating a random record just for the other 63 fields and then replacing the CSE Name and CSE number, where would you need to look to find the number that you'd like inserted into this record's CSE number field?  Another, unrelated, CSE number field?


          Again, please forgive me if I'm being blind...I'm just trying to understand what you're after.

          • 2. Re: Self Join not working.



            I have finally learned how to link two fields together so when one is filled in the other populates with the associated data. Thanks to Rosemary of FileMaker.


            Here goes the problem and the solution.


            Problem: I have a flat database containing over 4,00 records with over 65 fields. Two of the fields are Engineer names and Engineer phone numbers. I wanted the phone field to auto populate after I entered the engineers name.  Self-join does not work. It took a Relationship, but I was missing a very important step.


            Solution: I created one new table - an Excel file containing two columns: Engineers and Phone numbers and imported it as a new table "AXA Engineers".

            I went to manage database and created a relationship between "Engineer" in the existing table (Main Table) and "Engineer" in the new table (AXA Engineers). This was where I got in trouble. I needed to perform one more step.


            I went to the main details page (Browse Mode) as I usually do to view my records and selected Layout mode. I double-clicked on the Engineer phone field and selected, from the pull-down, the new table and then highlighted Phone number and saved. Eureka! It works.


            To answer Ninja's qusetion "If you are duplicating a record to make the new record, isn't the phone# already there? ". These are records containing equipment data and who is responsible for the service. Depending on the type of equipment there may be a different engineer. I only use duplicate because over 2/3rds of the data will remain the same. This way I only have to make a few changes to create the new record (i.e. equipment type and who is responsible).


            Thanks to all that assisted in my journey   Big KUDOS to Rosemary