5 Replies Latest reply on May 12, 2010 4:05 PM by LaRetta_1

    Avoid duplicated records on related value



      Avoid duplicated records on related value


      Hi to all!

      I stuck on loophole and I dont know how to solve it. I have One File (let's call it File "A") and file "B" (which is actually a Contact mannager)

      When I do something in FIle "A", a create a filed inside which refers data form FIle "B": I mannage to do that by create a custom Relationship, but everytime, when I enter 

      Data to file "A", it duplicates in File "B". When I try to edit that relationship, and uncheck "Allow creation of records in this table.... (Table "B"; a Contact Manager), I am not able to use records on File "A". I try everything, with signs of "equal" or "x", but dont have any solution.


      Please, help


      Of Course, It is Filemaker 10.0.3 and macos x 10.6.3

        • 1. Re: Avoid duplicated records on related value

          "Data to file "A", it duplicates in File "B". When I try to edit that relationship, and uncheck "Allow creation of records in this table.... (Table "B"; a Contact Manager), I am not able to use records on File "A"."


          It sounds like, when Allow Creation' is on, that it is inserting the 'key' into file B which is correct.  But we need more information. 


          1) What is this 'custom relationship' you've created?  What are the key fields which join them (and what are their data types)?

          2) File B is Contact Management but what does file A represent?  

          3) What does "a create a filed inside which refers data form FIle "B"" mean?  YOu place a field from FileB directly on your layout in FileA or are you using a Lookup or auto-enter? 

          4) What do you mean by "I am not able to use records on File "A"."?  How are you trying to use them?

          5) How are you viewing and entering data - on layout from File A with portal to File B or ?


          Remember that we are blind and your words are all we have to put together how your solution looks and functions.  It would be easiest for everyone if you upload a sample file somewhere.  Otherwise, we need answers to help us put together the vision of your request.  :smileyhappy:

          • 2. Re: Avoid duplicated records on related value

            Thanks for fast replay, I Apprecitiate that! :)




            1. File" A" is list of all my hardware at home. In that file, I create a filed (text field) where I want to be display Contact from suplier From file "Contact manager"

                simply, when I click on that field I want info from my contact manager and field called "Company" inside field in FIle "A"-Hardware list.


            2. Like I said, File "A" is Filemaker file called Hardware list and inside I have filed which I related to field to another file (contact manager- field: Company)

                everything works, but everytime I click on field inside Hardware list and put desired value from Contact manager, in contact manager duplicates record!!!!!!


            3. No, I create a custom relationship which I linked both fileds; a field from "Hardware list" (company name)  and field form Contact manager (company)

                and check "allow creation of records in this table...." on Contact manager, othervise, I cannot access to Drop down list on "hardware list " file.


            4. I gonna upload a file on my dropbox and print screen on put somewhere on internet...



            Thanks a bunch for your imidieatly intervention! :)


            • 3. Re: Avoid duplicated records on related value

              Yes, please upload the file.  it would be best for us to see your entire solution for a good review of your situation.


              I will be happy to take a look but I'm going to be quite busy for next several hours.  Hopefully someone else can also step in and walk you through it if I can't get to it. 


              BTW, print of screen won't really help that much - file is needed.  Otherwise, you will need to print your graph (which shows the relationship), print your layout (in layout mode) and print (or explain in detail) how you have created your value list.


              UPDATE: Added paragraph.

              • 4. Re: Avoid duplicated records on related value

                Ok....I Uploaded that FM solution here:



                Inside is picture of problematic field, so.....


                if any of you may help....I would apretiate ....

                • 5. Re: Avoid duplicated records on related value

                  Forgive the length and please don't faint.  :smileysurprised:


                  As with everything, take it easy and step through my instructions and you should be fine.  BACK UP ALL YOUR FILES FIRST IN CASE SOMETHING GOES WRONG.  Once you get through it and re-read (and possibly re-read and then create a few tests to understand the theory of how relationships work), you will be fine.  I have faith in you ... I can spot tenacity when I see it.   So here goes:


                  There are several issues here.  I will attempt to walk you through them one at a time.


                  Issue #1 … You need a Company table - Your relationship is incorrect.  Your Contact Management table has a primary key based upon the person and there can be multiple people for one Company.  You need a table called Companies.  In that table, have a primary key called kp_ID_Company (auto-enter serial starting at 1 – see Options Auto-Enter tab).  Make it a number field.  For now, also just have a Company name field or go ahead and add fields for the Company there, such as company main phone number.  I am using naming conventions from the FileMaker created example you are attempting to use (it is not my preference in naming).  It would also have been nice if they would have structured the file properly with a Company table to begin with.


                  I tried to figure out what language this was but even Google couldn’t find Opretater so I have to guess here.  I will assume that Opretater is the Company name field in Hardware since you had it joined to Contact Management::Company field.  Change Opretater field name to kf_ID_Company and make the field a number.  Join them in the graph as:


                  Hardware::kf_Company = Companies::kf_ID_Company


                  Now you have a proper relationship.


                  Issue #2 … Now go to your value list definitions and your Companies value list will be based upon field data (all data) and you will select your new Companies table.  Select kp_ID_Company in the left side and the ‘also display’ Company name in the right side.


                  Issue #3 … Go back to your Hardware layout.  The  field you circled in red as being the problem is a field from the Contact Management file.  You have accidently placed two Company fields onto this layout but both of them are based upon the Contact Management file.  You need to place your Hardware::kf_ID_Company field on your layout.  Attach a POP-UP menu to it.  When your User selects a Company for this Hardware record, it will insert the kp_ID_Company value into your kf_ID_Company field.  Pop-up menus also will then display the Company Name when you leave the field.


                  We’re almost done, I think …


                  Issue #4 … Delete both of those Contact Management::Company fields from your layout.  If you want to display any company information on your Hardware layout to assist your User, now just grab the fields from Company and place them directly on your Hardware layout.  When your User selects from the pop-up and it inserts the Companies primary key into your foreign key field (read the instructions in the yellow notes field in your graph), a relationship will be created between Hardware and Companies and any Company fields on that layout will then display the proper Company information, such as phone.


                  Issue #5 …  Your Contact Management file needs to be linked to your new Company table so that each company’s employees are related to their company.  So your Contact Management file also needs a kf_ID_Company field.  Attach the same pop-up and value list for Companies to it.  Then when you add a company’s employee, select the Company they work for.  Now the company information can also be directly displayed on their employee’s layout.  Delete all redundant fields, such as Company Name … there should only be ONE and that would be in your Company table.


                  I realize I’ve provided a LOT of information but you seem hungry to understand and willing to do the work; so I am therefore happy to assist.  I suggest you delete all the data, reset your Primary Keys in each table (ALL tables should have a primary key) and then add your data.


                  Ps … if you have a lot of existing data, we can create temp relationships and auto-assign the foreign keys but it can be tricky.  Let me know if you need to handle that.  Hopefully you are early enough in the process to restructure properly without a lot of effort.  If you get really stuck, after working clean through it, please ask further. 


                  One more tidbit ... when you want to see all the Hardware by a Company, go to your Company table and place a portal (from the Hardware table) on your layout.  Because you already have a proper relationship between the two, all Hardware for this Company will appear.  Have fun (and I sincerely mean that.) :smileyhappy: