7 Replies Latest reply on Mar 17, 2009 3:39 PM by philmodjunk

    Programming the who modified, when modified in Filemaker Server

    yenner58

      Title

      Programming the who modified, when modified in Filemaker Server

      Post

      I have a database with about 6000 records.  I would like to use the who/when modified fields.  I do have them in Filemaker but each day when it calculates the 6000 birthdates if show the first person to log on, on all 6000+ records so I have no administrative way to see who did what.

       

      Is there someway to stop this and show only when the file was modified, not considering the automatic birthday calculations.

       

      Thanks, Melinda

       

        • 1. Re: Programming the who modified, when modified in Filemaker Server
          philmodjunk
            

          Please provide a complete description of what you are trying to do. It sounds like you are trying to use the "modification Date" and "Modification Name" field auto entry options.

           

          I can't see what connection that has with "6,000 birthdates"

          • 2. Re: Programming the who modified, when modified in Filemaker Server
            yenner58
              

            I would like to use the When modified and Who Modified fields to see who did what in the files I have a field, When Modified, Type-Text and Options-Indexed, Modification Timestamp (date and time) and then I have a Field-Who Modified, Type-Text, Options-Indexed, Modification Account Name.

             

            then I have

             

            Field Name-AGE, Type-Calculation,  Options-Indexed, =Year(today) - Year(Birthdate) - If (GetAsNumber(Today)<Date(Month(Birthdate); Day(Birthdate); Year(Today)); 1:0)

             

            This gives me their age today, and this since it calculates daily, whoever loads filemaker first then becomes, who and when modifier.   I don't want it to do who/when on the birthday calculations... does this make sense?

             

            Melinda 

            • 3. Re: Programming the who modified, when modified in Filemaker Server
              philmodjunk
                

              You could put your calculation field in a related table (or file, I see by the Today function that this is not FMP 10). Then changes to the other fields would trigger your modification tracking fields, but not this one.

              • 4. Re: Programming the who modified, when modified in Filemaker Server
                yenner58
                   Ho do I put the field in a related table?  and I do have my server on 9 but I order a new computer today and will be on 10 next week.
                • 5. Re: Programming the who modified, when modified in Filemaker Server
                  philmodjunk
                    

                  I'd have to know more about what tables/relationships exist in your database to answer that in detail. 

                   

                  1. Let's assume you have one table named "MainTable" and a field in MainTable that uniquely identifies each record. Typically this is a number field set to auto-enter a serial number. We'll call this field "PrimaryKey."
                  2. Now open the field definition for your date calculation field and copy this expression to the clipboard.
                  3. Create your new table. (I'll call it 'Birthdates')
                  4. Define a new field "ForeignKey" as a number field
                  5. Click the relationships tab and create a relatioship that connects MainTable:: PrimaryKey to BirthDates::ForeignKey
                  6. Enter "DateCalc" as a new calculation field and paste your field definition from step 2.
                  7. Edit this expression to insert "MainTable::" in front of each instance of your Birthdate field.

                   

                   You've now moved your field to a separate table.

                   

                  Note: your calculation field is now an unstored field. Unstored fields will can trigger major delays if you are referring to them in searches or sorts of large numbers of records. If you want this to be a stored calculation, you'll need to move the birthdate field into this new table as well.

                  • 6. Re: Programming the who modified, when modified in Filemaker Server
                    yenner58
                      
                    Resubmitted without your text here.

                     

                    OK I understand until I get to Enter "datecalc" which table does this go in? and do I have to make my previous birthday field datecalc now?

                     

                    Then edit this expression, which expression and how do I edit it?  and I do want the birthdates stored so I move them to Birthdate table correct? 

                     

                     

                    • 7. Re: Programming the who modified, when modified in Filemaker Server
                      philmodjunk
                        

                      Hmm, I just deleted my initial response and am starting over. I just realized that the method I was describing to you was going to leave you with the very large job of copying 1,000's of birthdates from one table to another. The following approach will do that while also creating the new table we were discussing in this thread. One caveat: I don't have a copy of FMP 9, but am assuming that this works for it as well as it works for FMP 10.

                       

                      1. Make sure you have a field that uniquely identifies each record in MainTable. We'll call this field "PrimaryKey."
                      2. Now make a copy of your current file. We'll call it "File2" in this set of instructions.
                      3. Leave File2 closed and select Import Records/File from your current copy of the file.
                      4. Find and Select File2. Click Open.
                      5. Select "New Table" as your target for this import. This step will create a new copy of your table and copy in all of its data--including those birthdates that we are going to need.
                      6. Select Manage Database and click the Tables Tab.
                      7. Change the name of your new table to whatever name works for you.
                      8. Click on the fields tab and delete all fields except Birthdate, datecalc and PrimaryKey.
                      9. Select PrimaryKey, click options and disable any auto-enter options. Let's change it's name to "ForeignKey".
                      10. Click the Relationships tab and create a relationship linking ForeignKey in the new table to PrimaryKey in MainTable.
                      11. You'll probably want to select the "Create" and "Delete" options for your new table's side of the relationship.

                       

                      I wish I could say you are now done, but you still have some work to do.

                       

                      1. You now need to find every reference to your original Birthdate and datecalc fields and change them to refer to the new fields in your new table. You might have such references in Layouts, Scripts and also other field calculations.
                      2. Now save a back up copy of your file and then delete Birthdate and datecalc from MainTable. (If you find you didn't get one of these references changed over, your back up copy can help you fix the <missing field> tags that'll now appear.)

                       

                      Now you're truly done. Note that any other field calculations in MainTable that refer to Birthdate or datecalc will now be unstored and you'll have to decide whether this causes a problem and how you'll fix it. Likewise, if you can beg, borrow or steal a copy of FMP 10Adv, its Database Design Report would be very useful in helping you track down and fix those pesky references to the original fields before you delete them.

                       

                      Whew! that was a lot of work to type out and even more work for you to do, but it should do the trick.