2 Replies Latest reply on Jun 27, 2014 2:07 PM by philmodjunk

    Change Dialog Box based on Field contents of Different Record



      Change Dialog Box based on Field contents of Different Record



           We use our database to track maintenance issues for objects. Objects is one table. Maintenance Issues is another table, and they are linked by the Object ID. When a new maintenance issue is reported (by creating a new record in the maintenance table) and assigned to an object, I'd like to the database to check if there are already issues (i.e. other records) reported for that object which have not been marked as "resolved". The dialog box would say "There is already an unresolved issue reported for this object. Are you sure you want to submit a new maintenance issue? If they say No I'd like the just created record to be deleted. If they say Yes they would then be free to finish inputting data.

           Any help someone can give me would be much appreciated.


        • 1. Re: Change Dialog Box based on Field contents of Different Record

               If you're using FM 12 or greater you could use ExecuteSQL to search for maintenance records with the same Object ID. Then an if statement to set a variable to be displayed in the dialog.


               Alternately if you don't have FM12 >  you could use a relationship where Maintenance::Object_ID = MaintenanceSelfJoin::Object_ID. (MaintenanceSelfJoin being a new TOC you created) you then use   "ValueCount ( List(MaintenanceSelfJoin::Object_ID ) > 1 " to determine if there are other maintenance records for that object.


               I prefer ExecuteSQL over adding a new relationship. Each relationship you add slows your solution down just a tiny bit. If you're using client server across a VPN then every network packet counts.

          • 2. Re: Change Dialog Box based on Field contents of Different Record



                 Objects::__pkObjectID = MaintIssues::_fkObjectID

                 If you add this relationship:

                 MaintIssues::_fkObjectID = MaintIssues|SameObject::_fkObjectID AND
                 MaintIssues::constResolved ≠ MaintIIssues|SameObject::Status

                 MaintIssues|SameObject would be a new table occurrence of MaintIssues. constResolved would be a field of type calculation set up to evaluate to the same value that you enter or select in Status to mark a record as "resolved".

                 Then you can use Count ( MaintIssues|SameObject::_fkObjectID ) to check for the existence of unresolved maintenance issues for the same object.

                 I'd change the sequence of operations, however, if I were setting this up. I'd require the user to click a button or use a custom menu to create a new maintIssue record so that a script can check for the existence of unresolved issue reports before creating a new record. That way, if the user elects not to add a new report when they get this message, no blank MaintIssues record exists to be deleted. This script could even pull up a summary report on the layout listing the open issue reports so that the user can examine at least a brief description of all unresolved issues in order to better decide whether they still want to add their own new record.

                 A simple form of such a script would start out like this:

                 IF [ Not IsEmpty ( MaintIssues|SameObject::_fkObjectID ) // unresolved issues exist ]
                    #Go to Related Records could be used here to pull up all unresolved issues for the current object in a list or table view if desired
                    Show Custom Dialog [ "Unresolved issues already exist for this object. Create a new Issue record?" ]
                    If [Get ( LastMessageChoice ) = 2 // Cancel was clicked ]
                       Exit Script []
                    End IF
                 End IF
                 New Record/Request