8 Replies Latest reply on Apr 24, 2012 12:39 PM by MikeBradshaw

    Chicken and Egg? I need to find the name of the current table, but do not have an active field

    MikeBradshaw

      Title

      Chicken and Egg? I need to find the name of the current table, but do not have an active field

      Post

      Hi,

      I have searched for the whole day looking for a way to solve this and each time I find a part solution. It seems I may have a chicken and egg problem, but I am sure someone smarter than me has already figured this one.

      I have setup a 'join' table called DOCUSAGE as a means of determining how a single instance of a Document is used within the database. For example, a Product tech worksheet may have been emailed to a Contact working on a Project. In this instance the single record holds the link to the Document ID, Product ID, Contact ID and Project ID. If the record from the Join table is deleted then all the links have gone, therefore I want to be able to check whether the record in the Join table links to other tables before deleting and providing the user a warning with the option to cancel, delete or edit the record to remove a specific link (hopefully this still makes sense).

      I have it working fine IF I create a separate script for every table, however I was hoping there was a way to create a single script that identified the table whcih could then be usedin the Go to Related Record.....function.

      I have tried using Get(ActiveFieldTableName) however I need to click in a field before this work (once I click in a field it does work, but the user just wants to press the X Delete button, and the button doesn't seem to count even though it is on the portal row. I have looked at making a field Active but to do this you need to have the Table name, therefore I am in the catch 22 situation.

      Any ideas would be greatly appreciated.

      Mike

        • 1. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
          philmodjunk

          If you don't have an active field, then the only "current table" that you have is the current layout's table--actually a table occurrence name.

          Get(LayoutTableName)

          Will return the name of that table if you need it.

          PS. A "table occurrence" is what we call the boxes found in Manage | Database | Relationships. You might have any number of table occurrences there that all refer to the same table as defined in Manage | Database | Tables.

          • 2. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
            MikeBradshaw

            Thanks Phil,

             

            As it is the record in the Portal that requires checking and not the main layout (that is the DOCUSAGE rather than the Account or Contact etc) it will not work. Do you know if there is away to make a field active within the Portal Row without necessarily clicking on it? 

            The red cross button to initiate the delete is part of the Portal Row and only becomes visible when the row has a record to display, however by pressing the red cross button does not make the row active. I even looked for whether there was a way to have 'click through' where a dummy field is located below the red cross button so a press on the button also 'clicked' in the field and therefore made it active.

            This really does seem like a chicken and egg situation.

            Mike

            • 3. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
              philmodjunk

              So you want the Portal's Table Occurrence name, not the layout's?

              I must assume that you have a script that you want to use with multiple portals. Otherwise, you don't need the indirect reference that you are using.

              I have a delete portal row script that highlights the clicked portal row, displays a custom dialog containing data from the portal record for confirmation that then deletes the portal row--all in a script that can be used with any portal in my database or in any other FileMaker database file.

              I do this by passing the needed portal specific info as a script parameter.

              I think that's the option you'll need to use here. You can pass the name of the portal's table occurrence as a parameter to your script and then you can access this name by using get ( ScriptParameter )

              • 4. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
                MikeBradshaw

                Hi Phil,

                 

                appreciate your quick response to the post.

                I also use a standard delete from portal row script:

                Show Custom Dialog ("Inserted text to allow user to confirm deletion")

                If [Get ( LastMessageChoice ) = 1]

                  Delete Portal Row [No dialog]

                  If (Get (Last Error) = 301]

                #Detemine sif another using portal record

                   Show Custom Dialog ["text to warn user"]

                  End If

                  Commit Records/Requests [Skip data entry validation: No dialog]

                End If

                 

                There is also an additional script I was going to use to determine whether the record has links to other modules and therefore execute the deletion script.

                Go to Field []

                Set Variable [$TABLE; Value:Get (ActiveFieldTableName)]

                Go to Related Record [Show only related records; From table; "T27_DOCUSAGE"; Using Layout "layour name to used for DOCUSAGE")]

                # next row check to see if the rcords includes links to other records in other modules 

                If [T27_DOCUSAGE::id_account >0 or T27_DOCUSAGE::id_account > 0 or T27_DOCUSAGE::id_product >0 or T27_DOCUSAGE::id_project > 0 ]

                  Show Custom Dialog ["Warning"; "This document is used in other modules, deleting it will remove it from the other modules too"]

                  Set Variable [$Choice; Value:Get (LastMessageChoice)]

                #Determine whether the user wants to cancel or run the delete script or edit the record manually, removing the relevant links

                obviously the correct End If's etc are in the script.

                 

                I am interested in how you grabbed the Portal name (or ID) to pass to the script via the ScriptParameter. This would be the answer to what I am struggling with.

                 

                Thanks in advance.

                Mike

                 

                • 5. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
                  philmodjunk

                  I don't actually need the portal's table name to delete a portal row so I avoid that issue altogether in my script.

                  My basic delete portal row script is this:

                  #Created by Phil Caulkins
                  #This script functions independently of all layouts/tables. It can be cleanly imported into any filemaker file.
                  #
                  #Delete row of portal where button was clicked after User Confirmation.
                  #
                  #This script is designed to work with all portals where you want to use a button to delete a portal row.
                  #Parameters are passed in a 2 value list.
                  #Parameter 1 should be a boolean value to disable this script if the button is clicked on a blank "Add" portal row.
                  # Not IsEmpty (portalTO::Keyfield) is often the best expression to specify as this parameter.
                  #Parameter 2 should contain text to be included in the custom dialog to aid the user in identifying which row is about to be deleted.
                  # Often, this is the contents of one or more fields shown in the portal.
                  #
                  #Check the custom formatting and tool tips found attahed to the delete buttons in this file for additional ways to make this method user friendly.
                  #
                  Set Variable [ $RowExists; Value:GetValue ( Get ( ScriptParameter ) ; 1 ) ]
                  Set Variable [ $Label; Value:GetValue ( Get ( ScriptParameter ) ; 2 ) ]
                  If [ $RowExists ]
                         #Highlight the portal row to be deleted.
                         Go to Portal Row [ Get ( ActivePortalRowNumber ) ] [ Select; No dialog ]
                         #Confirm that button was not clicked by mistake.
                         Show Custom Dialog [ Title: "Confirm Row #" & Get ( ActivePortalRowNumber ) & " Deletion";
                                 Message: "Delete " & $Label & "?"; Default Button: “Cancel”, Commit: “No”; Button 2: “OK”, Commit: “No” ]
                         If [ Get ( LastMessageChoice ) = 2 /* OK was clicked */ ]
                                 Delete Portal Row [ No dialog ]
                         Else
                                 #Commit releases the focus from the portal row so that it no longer is highlighted.
                                 Commit Records/Requests
                         End If
                  End If

                  Note that the script expects a return separated list of values passed as the script parameter. an example of such a parameter expression entered into the optional script parameter box is:

                  List ( not IsEmpty ( Categories::BugID ) ; Categories::Category )

                  Note that there are no layout, table or even file specific references anywhere in this script. I can import ths script into any file where I need it and simply refer to the comments at the beginning to determine what list of values to pass to it as a script parameter. You can find a working copy of this script in the Known Bug List database.

                  I also came up with a more sophisticated version for deleting portal records from a portal to a Join table that checks to see if this will "orphan" a record in the other related table and if so, asks if it should also be deleted or not. It passes a much longer list of variables that includes the needed table, field and other info to handle that situation.

                  For some relationships, such a check is not needed. You can enable the delete option in the relationship such that deleting the parent record automatically deletes the related child records. (Use this option with care as it can result in a cascading delete that deletes many records from many tables.)

                  • 6. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
                    MikeBradshaw

                    Thanks Phil, this is a great bit of advice!

                    towards the end of your post you mention you have a more sophisticated version that checks to see if the delete will orphan a record. As my DOCUSAGE table is effectively a Join table that is exactly the issue I am having. I need to warn the user that by deleting the record they will orphan (or in my case delete) the record from all other modules.

                    Are you able to share the more sophisticated version too, or at least the bit that provides the intelligence. My attempt was using a simple method of checing to see if any of the ID fields contained a value. As I mentioned this worked fine providing I can make a field in the Portal Row active before running the script, however I cannot always guarantee the user will click on a field before pressing the delete key.

                     

                    cheers

                    Mike

                    • 7. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
                      philmodjunk

                      Go to field can make a field in the portal row active but it's a step that can't be used with indirection so it won't work if you want this script to work with more than one portal. (If you only need it to work with a specific portal, you don't need to detect the name of the portal's table here.)

                      You can pass the table occurrence name as text in your list of script parameters, however, and then it's possible to do this.

                      Here's the script I use for this. This is the first time that I've posted this one and it's not been as thoroughly tested. It's also not quite as "portable" as the original as it requires a special calculation field to determine the "orphan" status of the related record on the far side of the join table. This calculation field is defined in the "far side" table and counts the number of records that exist in the join table. It's passed as a parameter to the script.

                      #Created by Phil Caulkins
                      #This script functions independently of all layouts/tables. It can be cleanly imported into any filemaker file.
                      #
                      #Delete row of portal where button was clicked after User Confirmation.
                      #
                      #This script is designed to work with all portals where you want to use a button to delete a portal row.
                      #Parameters are passed in a 2 value list.
                      #Parameter 1 should be a boolean value to disable this script if the button is clicked on a blank "Add" portal row.
                      # Not IsEmpty (portalTO::Keyfield) is often the best expression to specify as this parameter.
                      #Parameter 2 should contain text to be included in the custom dialog to aid the user in identifying which row is about to be deleted.
                      # Often, this is the contents of one or more fields shown in the portal.
                      #Parameter 3 is the name a calculation field that counts the number of related records in a join table if the portal record being deleted
                      # is a record in such a table. If this count indicates that this is the last join record that refers to this related record, the user will be
                      # asked if they want to delete it or not.
                      #Parameter 4 is the name of a layout from which the orphan record can be deleted.
                      #Parameter 5 is the name of a field containing the primary key for this layout's table.
                      #Parameter 6 is text used to identify the current layout's records when checking to see if the related record on the other side of the join table should be deleted.
                      #Parameter 7 is the name of the record from the table on the near side of the join table.
                      #Parameter 8 is the name of the record from the table on the far side of the join table.
                      #
                      #Check the custom formatting and tool tips found attached to the delete buttons in this file for additional ways to make this method user friendly.
                      #
                      Set Variable [ $RowExists; Value:GetValue ( Get ( ScriptParameter ) ; 1 ) ]
                      Set Variable [ $Label; Value:GetValue ( Get ( ScriptParameter ) ; 2 ) ]
                      Set Variable [ $JoinCount; Value:GetField ( GetValue ( Get ( ScriptParameter ) ; 3 ) ) ]
                      Set Variable [ $Joinlayout; Value:GetValue ( Get ( ScriptParameter ) ; 4 ) ]
                      Set Variable [ $JoinPKName; Value:GetValue ( Get ( ScriptParameter ) ; 5 ) ]
                      Set Variable [ $JoinPKvalue; Value:GetValue ( Get ( ScriptParameter ) ; 6 ) ]
                      Set Variable [ $RecordName1; Value:GetValue ( Get ( ScriptParameter ) ; 7 ) ]
                      Set Variable [ $RecordName2; Value:GetValue ( Get ( ScriptParameter ) ; 8 ) ]
                      If [ $RowExists ]
                             #Highlight the portal row to be deleted.
                             Go to Portal Row [ Get ( ActivePortalRowNumber ) ] [ Select; No dialog ]
                             #Confirm that button was not clicked by mistake.
                             Show Custom Dialog [ Title: "Confirm Row #" & Get ( ActivePortalRowNumber ) & " Deletion"; Message: "Delete " & $Label & "?";
                                     Buttons: “Cancel”, “OK” ]
                             If [ Get ( LastMessageChoice ) = 2 /* OK was clicked */ ]
                                     Delete Portal Row [ No dialog ]
                                     If [ $JoinCount = 1 // this is the last case where a join record links to this record on the far side of the join table relationship ]
                                             Show Custom Dialog [ Title: "Orphaned " & $RecordName2 & " Record"; Message: "This was the last link from a " &
                                                   $RecordName1 & " record to this " & $RecordName2 &" record. Delete this " & $RecordName2 & " record also?";
                                                   Buttons: “Cancel”, “OK” ]
                                             If [ Get ( LastMessageChoice ) = 2 /* OK was clicked */ ]
                                                     Go to Layout [ $JoinLayout ]
                                                     Enter Find Mode [ ]
                                                     Set Field By Name [ $JoinPKName; $JoinPKvalue ]
                                                     Perform Find [ ]
                                                     Delete Record/Request [ No dialog ]
                                                     Go to Layout [ original layout ]
                                             End If
                                     End If
                                     Refresh Window [ Flush cached join results ]
                             Else
                                     #Commit releases the focus from the portal row so that it no longer is highlighted.
                                     Commit Records/Requests
                             End If
                      End If

                      The scripts listed in my posts to this thread, BTW, were copied from database design reports and then formatted with HTML tags to indent them courtesy of the Script Pretty custom function that is also found in the Known Bugs List database.

                      • 8. Re: Chicken and Egg? I need to find the name of the current table, but do not have an active field
                        MikeBradshaw

                        Thanks Phil,

                        I think thos has covered what I need to get this done! I really appreciate your help and especially quick response. Also the advice on how to get a script out of FM topost here :-) - it's the simple things........

                         

                        Mike