7 Replies Latest reply on Mar 25, 2011 9:20 AM by philmodjunk

    Timestamp for every note entry



      Timestamp for every note entry


      I have a notes section within my layout and need to be able to track who the user was and date and time a note was entered. I also need to be able to input a followup note that to would need to stamp the users name, time and date. Ex:

      John Smith 12/1/2011 10:45pm - The client was having trouble opening doc.

      Rob Smith 11/5/2010 9:45am - Made a follow call.

      BTW I just got this software today so please explain in detail only been using this for two hours now.


        • 1. Re: Timestamp for every note entry

          Best way is to set this up as a table of notes lwhere each line in your example is a record and each part, name, date-time, and note is a field defined within that record. You don't indicate the context to these sample notes--all for one customer I would assume. You can look up Portals in the help system to learn more, but with a related table defined for your notes, you can place a portal to notes on your customer layout to record all notes pertinent to that customer.

          You can use a time stamp field to record the date and time. If you set it to auto-enter the creation timestamp, it will automatically record the current date and time when you create a new record in your portal.

          • 2. Re: Timestamp for every note entry

            I kind of understand and have looked at the portal records in the help but it seems a more detailed. Once I create the portal I don't know what field to select since the XLS file I've imported doesn't have a field for client notes. I tried creating one and once I said ok it didn't do anything. I will be importing thousands of contact info monthly without any notes just clients name, address, email, phone,policy number I then need to be able to make notes if and when the clients calls in and who is taking that note. I did try auto enter creation timestamp and it worked on a field but when I continued to type the note the cursor won't move any further. 

            Any other suggestion I know this is fairly detailed for a beginner, can you spell it out a little more step by step or is there a good youtube video.



            • 3. Re: Timestamp for every note entry

              I think that you are givining up too easily on portals. The key here is that you need a separate, related table for your notes. The absence of a "notes" field in your imported data is not at issue as that data is not being imported into the table where you would define the notes field anyway.

              Do this:

              In your original table, (I'll call it Main), make sure that you have a number field defined as an auto-entered serial number. This field will uniquely identify every record in Main. Depending on the options you use when importing data, you can either have this field assign a serial number to each new record during import, or you can use replace field contents to update this field by adding serial numbers for all new records immediately after import.

              We'll call this field _pk_MainID.

              Now go to Manage | Database | Tables and create a new table named Notes. Define the following fields with the following options:

              _fk_MainID (number field)
              TimeAdded (TimeStamp field, auto enter creation time stamp)
              Notetext (text field)

              Click the relationships tab and drag from Main::_pk_MainID to Notes::_fk_MainID to link the two tables in a relationship.

              Double click the relationship line and click the "Allow creation of records via this relationship" option for the Notes table.

              Click OK until you are back in the database.

              Go to your layout for Main and enter layout mode.

              Use the portal tool to draw a rectangle on your layout.

              Select "Notes" in Show Records from and specify that the portal have a scroll bar. (You can experiment with and research the other options later.)

              Select the TimeAdded and NoteText fields for your portal.

              While the portal's default arrangement is for a mini table of portal records with the fields arranged in columns, you can resize the portal row and place the TimeAdded field above a much taller NoteText field. You can also format the NoteText field with a scroll bar if you wish.

              Now enter browse mode and try entering notes in the Note text field of your portal and see what happens.

              • 4. Re: Timestamp for every note entry

                Wait a minutes I just see what I did wrong with MainID it need to be in both table to relate. But should the timestamp, notetext be in the Notes tables?

                • 5. Re: Timestamp for every note entry

                  Yes, that's where I said to put them:

                  Now go to Manage | Database | Tables and create a new table named Notes. Define the following fields with the following options:

                  _fk_MainID (number field)
                  TimeAdded (TimeStamp field, auto enter creation time stamp)
                  Notetext (text field)

                  • 6. Re: Timestamp for every note entry


                    Thank you I got it to work I just have a couple other questions:

                    1. where are the setting to allow modifications to the notetext field but after inouted and timestamped it can't be modified.

                    2. How do I import a .xlsx file and some of the entries (Name, address, phone, policy,email) are the same that it won't duplicate

                    There will be times when we import a new customer and the policy date will be for one year effective date and expiration date and next year they renew I would like it to update the policy dates if all other info is the same (name, address, email, phone, company)

                    3. Should I place a search field in the layout or use the search field in the top navigation from filemaker? If I should place on in the layout how.

                    Thank you so much for all your help.

                    • 7. Re: Timestamp for every note entry

                      1) That's an extremely tight "lock" on the record. I can forsee instances where a user accidentally exits the note fields too soon and then is locked out of completing the note.

                      Basically, you can set up expressions in Manage | Security... that lock out editing privileges for individual records. You might use:

                      IsEmpty ( Notes::TimeAdded ) or IsEmpty ( Notes::NoteText ) as an expression that locks the record once the two fields have data in both and the record is committed.

                      See "Editing record access privileges" in FileMaker Help and check out this particular sub section: "Entering a formula for limiting access on a record-by-record basis" for a detailed description of how to set this up.

                      You can also set up OnObjectEnter script triggers on both fields to only lock users out on just this layout. You run a script that determines whether the user may edit the field. It they are not to be permitted access, you use Go To Object or Go To Field to move the cursor out of the field to some other location on the layout. (Sometimes you set up an invisible button set to do nothing, give it an object name and then go to it to just make the cursor disappear when they try to click or tab into a note field for which access is denied.)