11 Replies Latest reply on Jul 8, 2011 12:03 PM by philmodjunk

    Deleting record puts related file records out of sync

    AdrianWilliams

      Title

      Deleting record puts related file records out of sync

      Post

      FM Pro 11..  I Have two related files: Contacts file and Produt1 file. The relationship in Product1 file displays instances of ::Name, ::Address, ::Client No. from the other Contacts file.

      Deleting a record in Contacts file OR Product file messes things up. I can no longer add a new record and have the relationship work to pass the info between files! What's going on? Why can't I delete a record without completely destroying all the work I've done?

      Or can I even prevent anyone from DELETING a record so that any orphaned ones are just left?

      In an attempt to understand, I've deleted ALL records in both files and reset the Serial Number to Newxt: 1 in ID fields in both files. I'm guessing that does nothing or even makes it worse?   Heeeelp!

      Adrian

        • 1. Re: Deleting record puts related file records out of sync
          philmodjunk

          Can you tell us exactly how you've set up your relationship and how you've designed your layouts for adding new records? (This post looks familiar, but I'm not about to trust my memory to get it right here.)

          One useful option is to enable the delete option in the relationship so that deleting a parent record also deletes any related child records. This will prevent any orphan records from being left behind.

          You can also use Manage | Security to set up a password for users that does not allow them to delete records. (We don't allow our data-entry clerks to delete records in our system here as these records are invoices and invoices may be voided, but may not be deleted.) If you do need to allow them to delete, but only under strictly controlled circumstances, you can use Manage | Security to disallow deleting records, but then you can write a script to delete the record set to run with "full access permissions". This script, can then have double checks built in to it to make sure that it is truly safe to delete the record.

          What puzzles me in your original post, is the statement that "Deleting a record in Contacts file OR Product file messes things up. I can no longer add a new record and have the relationship work to pass the info between files!"

          That statement suggests that something is not correctly set up in your relationship(s) here. (And if you are using separate files because you are using FileMaker 6 or older, please let us know so that we can avoid suggesting something that might not work with older versions of the database software.)

          • 2. Re: Deleting record puts related file records out of sync
            AdrianWilliams

            DES                CONTACT

            k_Asset_ID      k_ID_Contact

            Client               50+ more fields in this column all about contact

            Name_First

            Name_Last

            Client_Ref

            DES and CONTACT are separate files. I relate DES to CONTACT using Client --> k_ID_Contact

            (There will be 30 more like DES all unique products with many pictures, letters complicated progress charts. In separate files each product is easier to deal with.  In a single file, I could not see how one Client can have DES, PRF, HTW etc, etc products display in a repeating way. Text is fine in repeating fields in a scrolling window, but how to get various check boxes, buttons for letters etc in a scrolling window? That's why separate files seemed to be the answer).

            When entering first and last name in DES file the Client _Ref appears. I can have other instances too.

            This works - fine. But when I delete a record (as a test), as someone surely will, nothing gets populated in ::instances in DES in any new records.

            So whatever scheme I use single or related files I still need to figure why records get disrupted when 1 is deleted? I know once its deleted its gone - fine. But why does that then prevent ::instances that used to have data from doing so now?

            • 3. Re: Deleting record puts related file records out of sync
              philmodjunk

              WHy is Name_First, Name_Last defeined in DES? If those are the client name fields, they should only be defined in the Contact table.

              Why do you have a Client field and also a Client_Ref field?

              "Text is fine in repeating fields in a scrolling window, but how to get various check boxes, buttons for letters etc in a scrolling window? That's why separate files seemed to be the answer)."

              Pretty much anything you can do with separate files, you can also do in a combined file with many tables. You add as many layouts to a single file as you need. Each such layout can refer to any one table occurrence in Manage | Database | Relationships and through that to any one table with the relationships controlling what data in other tables may also be displayed. This can include using a portal to display multiple DES records that are all linked to the same contact record in a scrolling window on a layout based on Client.

              By putting your tables each in a separate file, you complicate your life (especially when first starting out with FileMaker) as it now takes more work to maintain your database. If you do stick with multiple files, put all your layouts into one file and use external data source references to link to tables in the other files. If you maintain separate layouts in each file, you also have to maintain correctly designed relationship graphs in Manage | database | Relationships. Even without such layouts, you may end up with calculation fields that require relationships defined in the separate files in order to work correctly.

              • 4. Re: Deleting record puts related file records out of sync
                AdrianWilliams

                Following your advice I have now organised each Product into a separate Layout in the same file. So confusion about deleting records in one file not matching related file and consequent sorting nightmares have gone away. Thanks! Each Layout can have the many, varied fields concerned only with that product line. Each record has a Portal with fields required for that product. Each portal has its fields set to max 700 repetitions.

                This now means I need to figure how to view/print a list Product1 line items found within that portal?

                If a recored is Deleted - its gone, and I need to think about how to prevent accidental deletion?

                • 5. Re: Deleting record puts related file records out of sync
                  philmodjunk

                  "Each portal has its fields set to max 700 repetitions"

                  Is that a portal with 700 rows (seems very extreme) or each field in the portal row has 700 repetitions (a very, very bad idea and completely unecessary here--use one related record for each "repetition" and life gets much simpler for you!

                  For safer record deletion, there are several options you can employ:

                  Use Manage Security to prohibit deleting records for your user. Add a button that uses a script to delete the record with "run with full access privileges" specified and which includes your own code to check and make sure that this is not a mistake. You can open a custom dialog to confirm that this button wasn't clicked by accident. You can also check values in fields and permit deleting the record only if those fields store a specific value. (Maybe a new blank record may be deleted by the user, but not records that contain data, are older than 1 day, or...)

                  If you have FileMaker Advanced, you can customize the Records menu to remove the delete option for all or just certain users or you can set up the delete record option (and it's keyboard shortcut) to run your own script to delete the record that first confirms the delete and/or checks other data before permitting the deletion.

                  Using either of the first two methods, change a status field in the record to mark it as "deleted". Use scripts, triggers, portal filters, and/or relationships to hide records marked as "deleted" from view. This gives you the option to find a "deleted" record and "undo" the delete by changing this status field back to its original value.

                  I use a modified version of this last trick in one of my databases to hide deleted records for 60 days. A script that runs when the file is closed that checks for and deletes all "deleted" records that are older than this specified age.

                  • 6. Re: Deleting record puts related file records out of sync
                    AdrianWilliams

                    I figured the best way was to keep each record for one person and include their purchases in a separate portal for each product. At present each Product has all its required fields in its own Layout. Certain fields from that layout are displayed in the portal row for that Product only. Each portal has about 10 fields in a row. Each field has 700 repititions to hold their purchases. If that's a bad idea - can you please explain how I can use "...one related record for each repetition"? I don't understand the mechanism for how this might work. Thanks.

                    I'm using FM Pro 11 so will investigate Manage Security as suggested and experiment.

                    • 7. Re: Deleting record puts related file records out of sync
                      philmodjunk

                      Take a look at the invoices starter solution or this invoices demo file created by Comment. Note how there are no repeating fields used. Every time you want to add another item to the invoice, you add another related record in the portal. Since portal has a scroll bar, there is no limit to the number of rows of data you can add. With the data in separate records, summary reports become much easier to create and work with.

                      http://fmforums.com/forum/showpost.php?post/309136/

                      • 8. Re: Deleting record puts related file records out of sync
                        AdrianWilliams

                        Great - I can see how that can make life easier, but I need about 20+ product Layouts. How do those new Layouts/Tables hook-up to the existing scheme. Should I create a new "Products2" Layout/Table and draw a relationship line into 'LineItems' giving the new Table Serial Numbered ID?

                        • 9. Re: Deleting record puts related file records out of sync
                          philmodjunk

                          It's difficult to say from here as You know what data you have to work with and I don't, but I wouldn't create separate Serial number fields for each product type in the products table--that would lead to major confusion when you need to work with products from more than one such category.

                          There are a number of possible options you can experiment with. Each layout can have a script trigger that performs a find or constrains the found set to only products of a type compatible with your layout. (These are two different options with two different results here.) or record can be listed in a portal with a portal filter limiting the portal's records to a specific product category.

                          You can also set up a common products table with the Product ID, Description, Category and any other fields common to all products, then define related "product Detail" tables for the fields that are specific to Products of a particular category. You can link a detail table by the same ProductID field defined in the products table. Your customized layouts can each contain fields from both the common products table and one of the related product detail tables. (No portal needed, just put the fields from the detail table directly on the layout.)

                          • 10. Re: Deleting record puts related file records out of sync
                            AdrianWilliams

                            That's a major help - thanks. I'll sleep on it. Looks like its better to put details fields directly onto layout would make calculation invoices and printing easier. Portals would be a nightmare to print from.

                            • 11. Re: Deleting record puts related file records out of sync
                              philmodjunk

                              While it is possible to print layouts that include portals. There are limitations to doing so that can be avoiding by printing from a different layout based on the same table as the portal. If you examine the invoices starter solution and Comment's demo file, you'll find that they both use this alternate method for printing the invoice without printing from a layout with a portal.