3 Replies Latest reply on Feb 6, 2012 8:53 AM by philmodjunk

    Auto Email if no record added in parent table



      Auto Email if no record added in parent table



      I am running a database where the user will enter his monthly workplan till 25th of every month. Now I want to create a script which will check if no new record is added since last 25th to the parent table (which also contains a portal) then will automatically send out an email. This activity has to be conducted on the 25th of every month.  


      Kindly also explain how to create the script. 

        • 1. Re: Auto Email if no record added in parent table

          It's possible to design your interface so that the user cannot create the child record without also creating the parent record. One method being to make the foreign key field that links it to the parent a required value and then also add a validation calculaiton that checks for the existance of the related parent record or a validation rule that requires that the foreign key field's value be a member of a defined value list--a value list of exiting Parent record primary keys.

          Another method is to require the user to create the child record in a portal to the child table from a layout based on the parent record.

          Wouldn't that be better than checking once a month for missing parent records? (That can be done, it just seems better interface design to catch the error at the time the omission occurs rather than days/weeks after the fact.)

          • 2. Re: Auto Email if no record added in parent table

            thanks Phil basically the user has to enter a parent record along with the child records once in every month, so if the user has not entered the record I want the database to auto generate an email.

            • 3. Re: Auto Email if no record added in parent table

              So the presence of a child record without a parent is not the issue, you just need to confirm whether or not a new parent record was added since the last deadline.

              Presumably you have some kind of data field that identifies the month and year for which such a record applies and these records are then related to a table of Personnel? You could have a date field with the date of the first day of the month, year in question or separate fields for month and year and this still works though a date field can be easier to work with in some cases as you only need one field instead of two.

              It should be fairly simple to find all personnel records that have a related record dated for the month, year in question. Then Show Omitted Only will bring up all of those that do not and you can then have your script send out that list of personnel to the appropriate recipient. Individual reminder emails to the personnel on the list can also be sent.