7 Replies Latest reply on Mar 31, 2016 9:13 AM by ProdMan

    Relationship issues


      I have a "home" database with 100 records and a separate shell Filemaker doc that is for creating purchase orders from the database. There is a relationship between the two via a single number that is unique to each record, and the fields in the purchase order database are all "lookups" linked to the "home" database. I use the unique number as the relationship channel in "edit relationship" dialogue box, specifying that new records can be created in the purchase order database but also specifying that records in the "home"database can neither be created nor deleted when the purchase order records are deleted. Yet when I delete a record from the purchase order database, it deletes the record in the "home" database. What is going on here?


      I would like to be able to remove records from the purchase order database without affecting the "home" database in the least--what is needed to effect this?

        • 1. Re: Relationship issues

          Check all the relationships between the two. Even if the relationship is in a totally separate TOG from the one used for the Layout it will still perform.

          • 2. Re: Relationship issues

            Sorry, not really following what you're saying--"still perform"--yes, the lookups perform, but I don't want the deletion of records to perform. How to prevent?


            I am after a one-way relationship, from the home table to the purchase order table. The relationship link is in the purchase order database, where production code in "home" equals production code in purchase orders, with "allow creation of record in this table via this table" is checked for purchase order table only, and all other boxes for both tables are unchecked. So you would think that when  a production code is entered in purchase order and a record is "looked up" via the look up fields in purchase order, and then the record is deleted in the purchase order, the "home" database would be unaffected. But this is not the case.


            So how does one limit the relationship to one way?

            • 3. Re: Relationship issues

              What ch0c0halic is saying is that there must be another relationship between TOs of these two tables where you have checked “delete records in this table if records are deleted in the other table” for the “Home Table” TO.


              As long as there is even a single relationship between the tables where that setting is activated, the deletion will occur; it doesn't matter from which context (layout) you manually delete a record.

              • 4. Re: Relationship issues

                Hmm. Thanks for explaining. I'm going to attempt to attach a screen shot of the "Relationships" and "edit relaitonship" dialogue boxes--you'll see that there's only the one relationship. Is it possible a separate relationship is actually hidden in one of the fields in "purchase order" that doesn't show up here?


                • 5. Re: Relationship issues

                  Which TO is your layout based on in which you are deleting records from?

                  • 6. Re: Relationship issues

                    PO (the right table) is base on Pubdb_WIP, and I am deleting records from PO.


                    The only thing I can think of is that there's a hidden relationship somehow.


                    History: I created the PO database by duplicating the Pubdb_WIP, removing the existing layouts and creating the new PO layout. (I had to do this because unfortunately FM cannot handle separate page setups particular to individual layouts within one database; the Pubdb_WIP is 11 x 17 landscape and the PO is 8.5 x 11 portrait, meaning I would need to change the page setup every time I switched from one layout to another.) I then removed the Pubdb_WIP as a table in the PO database and set it up as an external data source, with the relationship I posted above. All of the relevant fields in PO are lookups to the Pubdb, including the linked "Production Code". But, as you can see, the "delete records" checkbox is not checked for the Pubdb, so why is it still deleting via this relationship?

                    • 7. Re: Relationship issues

                      I managed to find the answer, and probably wouldn't have done it without the nudges, so thanks to all.


                      The problem was in the layout setup: the "show records from" box. I mistakenly thought you had to show the records from the database being looked-up in order for the lookup to work. Not so. Changing that from the Pubdb to PO fixed the problem.