1 2 Previous Next 21 Replies Latest reply on Jan 31, 2014 11:57 AM by philmodjunk

    Tracking Certifications

    EP

      Title

      Tracking Certifications

      Post


           Human Resources file.  Each employee has different licenses/certifications we need to keep track of.  I have set up a FileBank table and layout that allows a user to upload files.  HR::pkID-<FileBank::fkID

           I can't figure out a way to track certifications that are expiring. I can add FileBank::Expire field to track when a certification is going to expire and display in a portal on a dashboard layout, but I feel the Expire field should be part of the HR table.  And when a new certificate is obtained and uploaded, the expired date needs to change.

           Any ideas?

        • 1. Re: Tracking Certifications
          philmodjunk

               I don't see any reason why the expire data field should be defined in HR. You will have multiple certifications and each will have a different expire date.

               Can you explain why you think you need the field to be in HR?

               It would seem that editing the expire date to show the new date when a renewal occurs would be a simple data entry task.

               A portal to FileBank can be placed on your HR layout and filtered to list either all expired certifications or all such that are within X days of expiring.

          • 2. Re: Tracking Certifications
            EP

                 I agree with you.  I put the expiration date in the Filebank table.  I have a portal that displays "expiring documents" based on a timeframe.  What I need to figure out is how to design updating of expiring document records .  I don't want a user to be able to update the record and overwrite the old container file and expiration date.  I'm thinking of a script that flags the expired record as "renewed" when a user uploads a new document of the same type with a new date and a portal filter will filter out renewed RR 's.

            • 3. Re: Tracking Certifications
              philmodjunk

                   An interesting question is whether you need to retain past, expired certification records after the update or not? Do you. That's an issue that can be handled without too many problems if you need that.

                   Hmmm, I think that i'd setup an Upload layout where users can insert their certification file into a container field. A conditional value list can be put on that layout that lists only the certifications for the current employee. By selecting from this value list, the employee links their new certification with an older expired or about to be expired FileBank record and this can then enable HR personnel to find expired records with a linked renewal record in order to handle this situation.

              • 4. Re: Tracking Certifications
                EP

                     Yes, I need to retain past, expired records.

                     

                          Hmmm, I think that i'd setup an Upload layout where users can insert their certification file into a container field. A conditional value list can be put on that layout that lists only the certifications for the current employee. By selecting from this value list, the employee links their new certification with an older expired or about to be expired FileBank record and this can then enable HR personnel to find expired records with a linked renewal record in order to handle this situation.

                     So does this mean, I would need another table? Filebank::_pkID-<Filebank2::_fkID?

                      

                     thanks

                • 5. Re: Tracking Certifications
                  philmodjunk

                       No, but you'd need another table occurrence in order to set up a self join relationship between Filebank and a new occurrence of the Filebank table--you'll actually need 2 more, one for the conditional value list and one for "linking" an expired certification to the newly uploaded certification.

                  • 6. Re: Tracking Certifications
                    EP

                         I'm having a hard time understanding what you mean by   "linking an expired certification to the newly uploaded certification." I understand the concept but don't know how to program this.  The new and expired certification are essentially 2 separate records in the same table. I display all document records in a portal, are you saying that I should have a separate portal based on the table occurrence linking the records?

                    • 7. Re: Tracking Certifications
                      philmodjunk

                           Your current data model for this is:

                           HR::pkID = FileBank::fkID

                           but by using the duplicate button in Manage | Database | Relationships (two green plus signs) you can create additional occurrencs of FileBank and you can link them by different match fields to get this data model:

                           HR::pkID = FileBank|NearlyExpired::fkID AND
                           HR::cRenewalFlag ≠ FIleBank|NearlyExpired::RenewedFlag
                           HR::cTodayPlus30 > FIleBank|NearlyExpired::RenewalDate

                           cTodayPlus30 can be an unstored calcculation field with date as a result type and this expression:

                           Get ( CurrentDate ) + 30. It will limit the relationship to just those records for the current employee where the expiration date is 30 days or less in the future from tdoay. cRenewalFlag is a number calculation field with 1 as the sole term. By setting Renewal Flag in FileBank to 1, an HR person can show the certification as renewed.

                           To link one FileBank Record to the FileBank record for which it is the renewal, set up this relationship:

                           FileBank::fkFileBankID = FileBank|Renewal::pkFileBankID

                           fkFileBankID can be set up with a conditional value list based on the first relationship that I've described and used to select the certificate for which the newly added certificate is its renewal.

                      • 8. Re: Tracking Certifications
                        EP

                             Thank you Phil.  I believe I have it working.  Records without a renewal date were appearing in the "Expiring Documents" portal, so I just applied a filter for the empty field.  I set up my last relationship and created a new layout (duplicated the FileBankUpload layout, but based it on FileBankRenewal table occurrence.  I added the _fkFilebankID field and am trying to set up the cond value list.  Its been a while, so im refreshing my self on how to do this at the moment. Does this method make sense ( adding a similar layout described above)?

                        • 9. Re: Tracking Certifications
                          philmodjunk

                               Seems like we are on the same page.

                          • 10. Re: Tracking Certifications
                            EP

                                 On my new "Renewal" layout, I want to use a Set Field function to set the RenewalFlag in the expired record to 1.  This would occur only when HR personnel fill out the required fields and click the OK button.  Instead of using a conditional value list like you mentioned, I am adding a "Renew" button in my portal that displays the documents.  When HR personnel click this button, it will open a popup displaying the "renewal" layout, duplicate the portal record and erase only the necessary fields (that need to be updated).  I can capture _pkFilebankID in a variable, but how would I store this value to use with the "OK" button on the Renewal layout the user will be working in? 

                                 I got your method working with the cond value list, but I prefer to do it this way because it will reduce human error and an expired record can only be flagged as renewed if certain criteria are met (required fields).

                                  

                                 Thanks

                            • 11. Re: Tracking Certifications
                              philmodjunk

                                   That's not the purpose of the conditional value list. The purpose of the conditional value list is to list all expired and about to expired certifications so that a user can link a new FileBank record to a previous one. "Here's my new CPR certificate, so I select "CPR" from the conditional value list to select the expired or about to expire CPR certificate" so that HR can easily find the one record for which the new record renews.

                                   Set field can certainly set that field to 1 or you can set it up as a check box that enters 1 into the field.

                              • 12. Re: Tracking Certifications
                                EP

                                     Thinking ahead, there may be a time when documents need to be renewed, even if they don't have an expiration date (example- Employee agreements, etc).  I'm adding a "Renew" button to my portal.  The portal displays an employees uploaded documents on their Personnel page.  An HR worker can simply locate the document in a portal row and click "Renew", which will run the attached script.  This script will navigate to the record containing the old document and duplicate it (and clearing certain feilds that need updated data), while also satisfying the requirement of linking the 2 docs together via _fkFilebankID. 

                                     I just need to get the RenewalFlag field automated.  when the user clicks the OK button on this layout ,I want to use Set Field to set the RenewalFlag in the old record to 1.  How can I navigate back to the old record? Can I pass the _pkFilebankID variable from the "Renew" script to this script in a clean way?

                                      

                                     Thanks

                                • 13. Re: Tracking Certifications
                                  philmodjunk

                                       Simply set the field to 1 before you duplicate it. Then duplicate it and set the field to "" to clear it in the duplicate.

                                  • 14. Re: Tracking Certifications
                                    EP

                                         I thought about this, but I want it to trigger once the new record is created and my script runs (confirming certain fields weren't left blank).  This will ensure the document was updated at the same time updating the RenewFlag on the old record.

                                    1 2 Previous Next