5 Replies Latest reply on Aug 9, 2012 2:56 PM by philmodjunk

    Lookup data from related record in same table and display messages if necessary

    user17887

      Title

      Lookup data from related record in same table and display messages if necessary

      Post

      I'm very new to FM and Using FMAV12 and any help would be greatly appreciated.

      I have a Group Code and Name that I need to add in to my table.  I want to be able to begin typing a group code and either modify an existing record or add one if none exists.  If one exists I need it to auto populate the group name field or just accept a new one if no code exists.  I would also like to display a message indicating the last time the name field was modified if a name does exists.   Our Group Codes will be reused depending on how old they are.  They need to be unique but the names can change.

      I currently have two fields, Group_Code and Group_Name related to each other in a separate table occurance.  When I type in a new group code I can tab to the name field without a problem. But if I type an existing code, it pops up with a dialog telling me that the group code must be unique but allowing me to duplicate it if I want. Need to change this dialog to indicate when the last time the name field was modifed and whether or not I want to update the group name.  I don't want it to create a duplicate record.

      Thanks again for any help.

       

       

        • 1. Re: Lookup data from related record in same table and display messages if necessary
          philmodjunk

          I believe that you want to find an existing record if one exists for a given group code and to create a new record only if such a record does not already exist.

          Define a new text field and specify global storage for it. To give a field global storage, find it in Manage | Database | fields and double click it to open field Options. Click the storage tab and you'll find a check box for specifying global storage for that field.

          I'll call this field, gGroupCode and call your Table GroupCodes. Modify the following example to use your names in place of mine.

          Put gGroupCode on your layout.

          Create this script:

          Enter Find Mode [] //clear the pause check box
          Set Field [ GroupCodes::GroupCodeField ; GroupCodes::gGroupCode ] //this step will fail if gGroupCode does not have global storage specified for it.
          Set Error capture [on]
          Perform Find []
          If [ Get ( Foundcount ) = 0 // no records were found ]
             New Record/Request
          end If

          Now you can either place a button next to the gGroupCode field to perform this script or you can select the field while in layout mode, right click it and select "Script triggers". Then select the OnObjectSave trigger and select the above script to run each time you enter a new value in the field and then exit it.

          You can add a date field to this table and specfiy in Field Options that it auto enter the modifcation date. This field will then tell you the last date that the record was modified.

          • 2. Re: Lookup data from related record in same table and display messages if necessary
            user17887

            Thanks for the reply. 

            I must be missing something in the set field step.  When I enter the calculation of the two fields it says that  "an operator is expected here".  What am I missing?

            • 3. Re: Lookup data from related record in same table and display messages if necessary
              user17887

              Never mind.  I got it.  One goes in the target field and the other in the calculation. 

              Is there a way to use the error message to ask wether they want to update the record or try a different group code? I would also like to put the last mofied time field in the message.

              • 4. Re: Lookup data from related record in same table and display messages if necessary
                user17887

                It's not working.  I created two fields.  gGroupCode and GroupCodeField.  I added both to the layout. I placed a button that ran the script on the layout.  I click new record, type a two letter code in the GroupCodeField, run the script and nothing happens.  The fields get updated whether there ar duplicates or not.

                • 5. Re: Lookup data from related record in same table and display messages if necessary
                  philmodjunk

                  Why did you add two fields? Only one, the global should be added. The other field is my name for the field you already have data for the group code.

                  There is no error message with the script, but you could add a custom dialog that asks them if they want to edit the record thus found or enter a different code into the global field.

                  Enter Find Mode [] //clear the pause check box
                  Set Field [ GroupCodes::GroupCodeField ; GroupCodes::gGroupCode ] //this step will fail if gGroupCode does not have global storage specified for it.
                  Set Error capture [on]
                  Perform Find []
                  If [ Get ( Foundcount ) = 0 // no records were found ]
                      New Record/Request
                  Else
                      Show Custom Dialog ["Last Modified: " & GroupCodes::ModifiedTime & " Edit This record or enter a new code." ]
                  end If

                  but keep in mind that you don't need the dialog. You can put the modifedTime field on the layout and the user will see it when the record appears. (ModifiedTime is the name I've given for a field of type Time. It would be set to auto-enter the current time each time the record is modified.

                  If the user enters a code and a record pops up that isn't the one they expected, they can just enter a new code into the global field and run the script again.