8 Replies Latest reply on Feb 28, 2012 12:51 PM by philmodjunk

    Update Field if Record Being Added or Modified

    ShariFoucher

      Title

      Update Field if Record Being Added or Modified

      Post

      I need to set the license period begin date field (in a portal) to a specific date (4/1/11 right now) when the license number in the same record is updated or a new record is being added.  About mid-March, this date will change to 4/1/12.  It is very important that this date NOT change unless there is an add or update.   

        • 1. Re: Update Field if Record Being Added or Modified
          philmodjunk

          We need to know more about your layout design, the portal and who this date field needs to work. When you say "add' is this adding a new record in the portal or a new record in the table on which the layout is based? We also need to know exactly what you mean by "update".

          • 2. Re: Update Field if Record Being Added or Modified
            ShariFoucher

            The layout is used for data entry/update, and the main table is Dog Owners.  A portal is set up for the dog registration table (one to many relationship).  The License Period Begin Date is always April 1st. so come mid-March, if the License Number field is updated with a new number, the license period begin date needs to be updated to 4/1/12.  Adding a new record refers to the dog registration record, although adding a new owner would automatically mean adding a new dog registration.

            • 3. Re: Update Field if Record Being Added or Modified
              philmodjunk

              Under what kind of "updates" would this date be entered?

               It is very important that this date NOT change unless there is an add or update.  

              You can set up this date in a field with global storage and then define the License Date field to auto-enter the date from this global field each time a new Dog License record is created. When the date changes, you can edit the value in the global field to the next year's date.

              This change will only affect new records. Existing records will keep their current dates.

              In a multi-user file, changes made to a globa field on any but the host machine will not be saved. If you are using a multi-user database, store the value in a field in a utility table and use a start up script that runs when the file is opened to copy the date in to your global. Then you can update the date by editing the non global field and the script will update the global for you the next time the file is opened.

              It's also possible to use a script that checks this date and updates it to next year automatically if you set it up to check this date every time the file is opened.

              • 4. Re: Update Field if Record Being Added or Modified
                ShariFoucher

                Each year, the license number will change, so we are looking for a change to that field to make a valid change to the license period begin field

                • 5. Re: Update Field if Record Being Added or Modified
                  philmodjunk

                  What I have suggested will do that.

                  • 6. Re: Update Field if Record Being Added or Modified
                    ShariFoucher

                    Thank you very much for all your help.  I also appreciate your patience when I can't seem to describe exactly what it is that I want to do.  Many, many thanks.Smile

                    • 7. Re: Update Field if Record Being Added or Modified
                      ShariFoucher

                      I tried the field with global storage and yes, it only works with new records.  Most commonly, I need to change existing records.

                      See attached screen print of my data entry layout, showing "Owners" which is the main table, and "Registration" as the portal.  The dog named Rufus currently has a license number of 337 which is for license period begin date of 4/1/2011.  When the owner renews for 2012, they will be given a new tag - lets assume the new number is 413.  When that "Lic Num" field is updated, I also want the "Lic Period Begin Date" to be change to 4/1/2012 automatically.  The "Transaction Date" is set up for auto-enter upon modification to timestamp.  Don't know if that could help me somewhere along the line. 

                      If the comment that began "In a multi-user file" had anything to do with updating Lic Period Begin Date, I did not get that at all.   If it pertained only to setting the static April 1 date with the year, I will try once I get more experience with Filemaker. 

                      • 8. Re: Update Field if Record Being Added or Modified
                        philmodjunk

                        There's no attached screen shot. Uploaded image files must be of type jpg, gif or pnb. Other file types such as PDF, tiff, bmp.... will not appear when uploaded.

                        Personally, I wouldn't update the existing license record for "Rufus". I'd create a new license record with the needed number and date. That way, I'd have a record of each time the dog was licensed. But I realize that would require a significant design change here so we'll put that idea on hold for the moment.

                        Working with what you have. Go back to the auto enter options tab in field options for this date field and modify the calculation to be:

                        If license number is a field of type number:

                        If ( LicenseNumber ; Table::GlobalDateField ; self )

                        If license number is a field of type text:

                        If ( not IsEmpty ( LicenseNumber ) ; Table::GlobalDateField ; self )

                        Then clear the "do not replace existing value..." check box. This way changes to the license number field will trigger a new copy of the current date entered in the global date field.

                        ...I did not get that at all...

                        It's additional info you need to know about how global fields work if you host your database from a server so that several users can access your database at the same time. If you have a single copy of FileMaker installed on a single computer, then you do not need to concern yourself with this info at this time.