2 Replies Latest reply on Sep 18, 2012 9:08 AM by mrwatson-gbs

    Need to find Relationships that Delete records in related tables

    dfehrenbach

      Does anyone know of a quick way to identify all the relationships that will delete records in one table when a record in the related table is deleted?

      I have a solution with a ton of TOs, even with DDR showing only Relationship Details there is still a vast number of pages to plow through and I'm afraid of missing some. Can anyone please share their technique?

       

       

        • 1. Re: Need to find Relationships that Delete records in related tables
          Mike_Mitchell

          I use a product called BaseElements, from goya (http://www.goya.com.au/baseelements). Once you process your DDR through the input engine, you can search for relationships that do this. I believe Inspector Pro does this as well, though I've never used it.

           

          HTH

           

          Mike

          • 2. Re: Need to find Relationships that Delete records in related tables
            mrwatson-gbs

            One method I use is: XSLT to find and extract the information out of the DDR that I need. This is VERY much quicker than an entire DDR analysis using any of the standard analysis tools (Cross-Check/Base Elements).

             

            You can use any XSLT engine you wish. The free BE-Plugin from base elements (mentioned above) also has a few perform xslt commands, if you wish to use that.

             

            I use the unix command "xsltproc" on the mac:

             

            xsltproc FMPReport_ListRelationsWithDelete.xsl MyDDRFile.xml
            

             

            (Not true! In fact I use TextMate a great editor for the mac, where I've built the command into the menus - but the basis is the xsltproc command.)

             

             

            And now here's the XSLT:

             

             

            <?xml version="1.0" encoding="UTF-8"?>
            <xsl:stylesheet xmlns:xsl="http://www.w3.org/1999/XSL/Transform" version="1.0">
                      <!--===== AUTHOR =====
            
            
                      (c) Copyright 2012 MrWatson, russell@MrWatson.de, this XML is free to use - as is - as you please. 
            
            
                      ===== PURPOSE =====
            
            
                      XSL-File: FMPReport_ListRelationsWithDelete.xsl
            
            
                      XML-Source: FileDDR.xml
                      XML-Grammar: FMPReport
                      XML-Content: FileMaker DDR of a database file
            
                      Lists all relations that have a "Delete records in related tables" option.
            
            
                      ===== HISTORY =====
                      © russell@MrWatson.de 2012
                      2012-09-18 RW: Version 1.0
                      -->
                      <!-- ===== HEAD ===== -->
                      <xsl:output method="text" version="1.0" encoding="UTF-8" indent="no"/>
                      <!--===== VARIABLES =====-->
                      <xsl:variable name="delimiter" select="'&#9;'"/>
                      <xsl:variable name="newrecord" select="'&#10;'"/>
                      <!--===== TEMPLATES =====-->
                      <!-- Main -->
                      <xsl:template match="/">
                                <xsl:value-of select="concat('File: ', FMPReport/File/@name,$newrecord,$newrecord)"/>
                                <xsl:apply-templates select="*"/>
                      </xsl:template>
                      <!-- Match relations with deletes in them -->
                      <xsl:template match="/FMPReport/File/RelationshipGraph/RelationshipList/Relationship[LeftTable/@cascadeDelete='True' or RightTable/@cascadeDelete='True']">
                                <xsl:value-of select="concat(LeftTable/@name,$delimiter)"/>
                                <xsl:if test="LeftTable/@cascadeDelete='True'">
                                          <xsl:value-of select="'D!'"/>
                                </xsl:if>
                                <xsl:value-of select="' ---- '"/>
                                <xsl:if test="RightTable/@cascadeDelete='True'">
                                          <xsl:value-of select="'D!'"/>
                                </xsl:if>
                                <xsl:value-of select="concat($delimiter,RightTable/@name)"/>
                                <xsl:value-of select="$newrecord"/>
                      </xsl:template>
                      <!-- ignore all other text -->
                      <xsl:template match="text()"/>
            </xsl:stylesheet>
            
            

             

            You need to apply it to each DDR file. And if you want output for windows with CRLF, you may wish to insert &#13; before the &#10; of the newrecord variable.

             

            It produces a tabbed table, something like the following (* open with FileMaker or Excel for easier reading), where D! indicates a delete option on that side of the relation:

             

             

            File: MrW__DB.fp7
            
            
            DebRng.Pos_CRTDEL               D! ----                _DebRng__
            Adr.ASP_CRT                           D! ----                _Adr__
            KtoBlg.KtoBlgPos_CRTDEL       D! ----                _KtoBlg__
            CV.CVPos_CRTDEL                  D! ----                _CV__
            WL.WLW_CRTDEL                    D! ----                _WL__
            KtoPos.FiBuPos_CRTDEL         D! ----                _KtoPos__
            Text.Param_CRTDEL               D! ----                _Text__
            Termin.Akte                           D! ----                _Termin__
            _EinAus__                                   ---- D!            EinAus.EinAusPos
            
            

             

            * I've had to tamper with the results here to make it look like it does in a text editor / spreadsheet

             

            Let me know if it works for you!

             

            Greetings from Hamburg in Germany

             

            MrWatson