4 Replies Latest reply on Nov 27, 2013 1:16 PM by philmodjunk

    Reassign Record ID number based on Associated Record Name in Another Table

    oman

      Title

      Reassign Record ID number based on Associated Record Name in Another Table

      Post

           I have two tables in my database: Objects and Maintenance Tickets. The Objects table has information about the object, the Maintenance ticket table has information about a maintenance event that is needed for that object. They are linked by the primary key of ObjectID, and each maintenance ticket also has a MaintenanceTicket ID.

           After a maintenance ticket record has been created, I need a simple way to reassign that ticket to a different ObjectID. The difficult part is that I would like a button titled "Reassign Ticket to Different Object" that would then bring up a drop down menu of object names but would replace the ObjectID when selected, so that all of the associated Object info on that maintenance ticket is also updated.

           In summary, How do I create a button that will replace the ObjectID field data in the maintenance ticket view, but allow me to select the replacement data from a list of all object names in my database (i.e. not a list of the Object IDs)?

           Thanks!

           Owen

        • 1. Re: Reassign Record ID number based on Associated Record Name in Another Table
          philmodjunk

               I don't see a need for a button. The ObjectID field can be formatted with a pop up menu or a drop down list such that you can select an object by name, but the value list enters the object's ID number.

               But what you want to do seems odd. If my computer needed maintenance and this the related record is linked to the object record for my computer, why would I want to link the maintenance record to a completely different object?

          • 2. Re: Reassign Record ID number based on Associated Record Name in Another Table
            oman

                 Good question. We have lots of people reporting maintenance issues but at the time of record creation they are assigning them to the wrong object. When the maintenance team reviews all of the issue records created they often find they have been assigned to the wrong object and need to be reassigned.

                 The solution doesn't  need to be a button. If selecting the ObjectID field in the maintenace ticket view could bring up a drop down of object names that would then input the associated ObjectID instead of the name that would be perfect. I'm able to add the drop down menu for the ObjectID field, but when I select an object it is still inputting the name and not the ID number. Is there a simple fix for that?

            • 3. Re: Reassign Record ID number based on Associated Record Name in Another Table
              oman

                   OK I figured it out and it was much easier than I thought.  I selected the ObjectID field and in the Inspector window under Data I selected Values List and created a new list. For the value list I specifed values from a field. I then set it to display values from the Object ID and the Object Name, and finally selected the box to only show the 2nd field values (ie. the object names). Everything appears to be working properly.

                   Thanks Phil you definitely helped me find a much simpler method.

              • 4. Re: Reassign Record ID number based on Associated Record Name in Another Table
                philmodjunk

                     That's exactly what I was referring to. smiley

                     Sometimes, however, your value list ends up too long to make this a particularly user friendly way to select a value. There are a number of methods that can be used to trim larger value lists down to size in order to make them easier to deal with.