2 Replies Latest reply on Oct 5, 2012 4:10 PM by MichaelHokanson

    All fields updating - new to FM

    MichaelHokanson

      Title

      All fields updating - new to FM

      Post

           I'm trying to learn to use FM and my first exercise is a support ticketing system. I have gotten most of the DB to work properly, but I have a field called 'Last_Update' that I'd like to have update with the contents of the most recently filled note field on my notes table. I'm attaching a diagram of my DB. If someone puts a note in field Note_03 on the Notes table, and there are no notes in Note_04-Note-10 then Note_03 should copy to the Last_Update field on the General Table where the Ticket_number matches. The problem I'm running into is that if I run the script from a record with Note_03 being the most recent update, all records are having their respective Note_03 field assigned to Last_Update, even if they are empty or not the most recent note for that ticket. I briefly tried searching the forums for similar issues, and tried Googling for quite a while, but didn't find anything to help solve this problem (I'm probably using incorrect search terms though). Any ideas?

            

      TicketingSystemDB.png

        • 1. Re: All fields updating - new to FM
          philmodjunk
               

                    If someone puts a note in field Note_03 on the Notes table, and there are no notes in Note_04-Note-10 then Note_03

               That reveals a basic structural weakness to your design.

               In a Notes table, each note should be in a different record, not in different fields of the same record.

               You'd want a minimum of 3 fields in your notes table:

               _fkGeneralTableID  (link to serial number of specific record in General table
               WhenRecorded--a field of type date or TimeStamp that records when the Notes Record was created
               Note---text field to record actual Note

               Then either a sorted portal or sorted relationship that sorts the related records in descending order by WhenRecorded will list the most recent update first--and there is no need for an added field to show that nor for a script to find the most recent note.

          • 2. Re: All fields updating - new to FM
            MichaelHokanson

                 Thanks for the response. I'm trying that out and am currently working on the sorted portal section for a list view showing the most recent note for all tickets.