Alpha Software Mobile Development Tools:   Alpha Anywhere    |   Alpha TransForm subscribe to our YouTube Channel  Follow Us on LinkedIn  Follow Us on Twitter  Follow Us on Facebook

Announcement

Collapse

The Alpha Software Forum Participation Guidelines

The Alpha Software Forum is a free forum created for Alpha Software Developer Community to ask for help, exchange ideas, and share solutions. Alpha Software strives to create an environment where all members of the community can feel safe to participate. In order to ensure the Alpha Software Forum is a place where all feel welcome, forum participants are expected to behave as follows:
  • Be professional in your conduct
  • Be kind to others
  • Be constructive when giving feedback
  • Be open to new ideas and suggestions
  • Stay on topic


Be sure all comments and threads you post are respectful. Posts that contain any of the following content will be considered a violation of your agreement as a member of the Alpha Software Forum Community and will be moderated:
  • Spam.
  • Vulgar language.
  • Quotes from private conversations without permission, including pricing and other sales related discussions.
  • Personal attacks, insults, or subtle put-downs.
  • Harassment, bullying, threatening, mocking, shaming, or deriding anyone.
  • Sexist, racist, homophobic, transphobic, ableist, or otherwise discriminatory jokes and language.
  • Sexually explicit or violent material, links, or language.
  • Pirated, hacked, or copyright-infringing material.
  • Encouraging of others to engage in the above behaviors.


If a thread or post is found to contain any of the content outlined above, a moderator may choose to take one of the following actions:
  • Remove the Post or Thread - the content is removed from the forum.
  • Place the User in Moderation - all posts and new threads must be approved by a moderator before they are posted.
  • Temporarily Ban the User - user is banned from forum for a period of time.
  • Permanently Ban the User - user is permanently banned from the forum.


Moderators may also rename posts and threads if they are too generic or do not property reflect the content.

Moderators may move threads if they have been posted in the incorrect forum.

Threads/Posts questioning specific moderator decisions or actions (such as "why was a user banned?") are not allowed and will be removed.

The owners of Alpha Software Corporation (Forum Owner) reserve the right to remove, edit, move, or close any thread for any reason; or ban any forum member without notice, reason, or explanation.

Community members are encouraged to click the "Report Post" icon in the lower left of a given post if they feel the post is in violation of the rules. This will alert the Moderators to take a look.

Alpha Software Corporation may amend the guidelines from time to time and may also vary the procedures it sets out where appropriate in a particular case. Your agreement to comply with the guidelines will be deemed agreement to any changes to it.



Bonus TIPS for Successful Posting

Try a Search First
It is highly recommended that a Search be done on your topic before posting, as many questions have been answered in prior posts. As with any search engine, the shorter the search term, the more "hits" will be returned, but the more specific the search term is, the greater the relevance of those "hits". Searching for "table" might well return every message on the board while "tablesum" would greatly restrict the number of messages returned.

When you do post
First, make sure you are posting your question in the correct forum. For example, if you post an issue regarding Desktop applications on the Mobile & Browser Applications board , not only will your question not be seen by the appropriate audience, it may also be removed or relocated.

The more detail you provide about your problem or question, the more likely someone is to understand your request and be able to help. A sample database with a minimum of records (and its support files, zipped together) will make it much easier to diagnose issues with your application. Screen shots of error messages are especially helpful.

When explaining how to reproduce your problem, please be as detailed as possible. Describe every step, click-by-click and keypress-by-keypress. Otherwise when others try to duplicate your problem, they may do something slightly different and end up with different results.

A note about attachments
You may only attach one file to each message. Attachment file size is limited to 2MB. If you need to include several files, you may do so by zipping them into a single archive.

If you forgot to attach your files to your post, please do NOT create a new thread. Instead, reply to your original message and attach the file there.

When attaching screen shots, it is best to attach an image file (.BMP, .JPG, .GIF, .PNG, etc.) or a zip file of several images, as opposed to a Word document containing the screen shots. Because Word documents are prone to viruses, many message board users will not open your Word file, therefore limiting their ability to help you.

Similarly, if you are uploading a zipped archive, you should simply create a .ZIP file and not a self-extracting .EXE as many users will not run your EXE file.
See more
See less

f_Audit() - Add auditing to any table

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

    f_Audit() - Add auditing to any table

    f_Audit() will add auditing fields to a table, auditing based field rules, and a simple audit review form and report.

    The auditing field rules where copied from Peter Wayne's "Xbasic for Everyone" with a few minor differences. Other snippets of code where copied from the Alpha5 forum. I would like to thank Cal Locklin, Tom Cone Jr, & Peter Wayne for continual posting of advanced concepts and how to's. Without which this function would not have been finished.

    f_Audit will modify the table structure and field rules of tables it is ran against.

    Added fields
    Created_By_f, character, length of 30
    Created_On_f, Time
    Changed_By_f, character, length of 30
    Changed_On_f, Time
    Unique_ID_f, character, length of 36

    Both Created_By and Changed_By are populated via API_GETUSERNAME()
    Changed_On and Changed_By are populated via NOW()
    Unique_ID is populated by API_UUIDCREATE()

    Modified Record Events
    CanSaveRecord
    OnSaveRecord
    OnEnterRecord
    OnChangeRecord
    CanDeleteRecord
    OnDeleteRecord

    Added tables - will be added in the same folder as the database
    Audit.dbf
    Changes.dbf

    Added forms
    Audit_frm

    Added reports
    Changes_Rpt

    How to use
    In the zipped file is a folder called "Tools_dB". The "Tools_DB" folder needs to be copied to the root of the c: drive.

    Audit.table needs to be copied to C:\Program Files\A5V8\Control_Panel_Addins

    Audit.aex can be copied to C:\Program Files\A5V8\Addins_installed.

    Open a test database, right click on a table, select Addins, Add Auditing. This will add the needed fields and the auditing record events to your table. It will also add the Audit.dbf to the database and the Audit_frm. Modify/Add/delete a record in the newly audited table. Open the audit_frm to see a simple view of what was modified. Click Report Differences for a hardcopy of the changes. "Report Differences" will add the change.dbf table and the changes_rpt report to the database if they are not currently there.
    Andrew

    #2
    Re: f_Audit() - Add auditing to any table

    Andrew,

    Thanks for sharing this. I will make good use of it.
    Tim Kiebert
    Eagle Creek Citrus
    A complex system that does not work is invariably found to have evolved from a simpler system that worked just fine.

    Comment


      #3
      Re: f_Audit() - Add auditing to any table

      Andrew,

      You need to be careful in case the table already has field rule event scripts that have an END statement. Since your code is added at the bottom of the script, it might never get executed.

      You could convert the OnSaveRecrod and OnDeleteRecord scripts to an inline function, and put a call to the function at the top of the script, after any header.

      Similarly, the other events might not execute if there is an existing END statement. Looks like these scripts could go at the top of any existing script if you remove the END statement from your code.

      Alternatively you could leave your code placement, but display a warning dialog to the user if existing code is found - the user should review the scripts to insure that flow through the script is proper.

      Bill.

      Comment


        #4
        Re: f_Audit() - Add auditing to any table

        Originally posted by aschone View Post
        f_Audit() will add auditing fields to a table, auditing based field rules, and a simple audit review form and report.
        Thanks for this, but I suggest you also post this to the code archive. That forum is dedicated to the sharing of tricks and solutions. :)

        Comment


          #5
          Re: f_Audit() - Add auditing to any table

          This is the code archive. :-) Although we did not get all the code. :-(

          Bill.

          Comment


            #6
            Re: f_Audit() - Add auditing to any table

            Originally posted by Bill Parker View Post
            This is the code archive. :-) Although we did not get all the code. :-(

            Bill.
            DOH! That's what I get for using the New Posts quick tab.

            Thanks Bill. Next time I'll look at the forum name.

            Comment


              #7
              Re: f_Audit() - Add auditing to any table

              Hi:

              Just started to play with this code. worked fine in development and in a shadowed database when the development software is on the PC.

              I could not use the software when RUNTME was the only software. It appears that the development software must also be on the PC.

              bob adler

              Comment


                #8
                Re: f_Audit() - Add auditing to any table

                @Andrew:

                Great function!

                Originally posted by aschone View Post
                Both Created_By and Changed_By are populated via API_GETUSERNAME()
                Would you be so kind as to include an option to populate the Created_By and Changed_By from a global variable containing the name of the user who logged into the application specifically, instead of using the Windows user name automatically?

                Thanks!
                Jim

                Comment


                  #9
                  Re: f_Audit() - Add auditing to any table

                  Here is the code. Simply put most of it is not mine hence why I initially didn't publish it. I simply combined pieces of other peoples code to make this work.
                  Andrew

                  Comment


                    #10
                    Re: f_Audit() - Add auditing to any table

                    Greetings all. I'm quite a newbie to Alpha 5 and am attempting to use it to solve a need. I need to create a comprehensive audit trail of changes made by users (I'm looking at having about 80) and this tool seems ideal. However, I've tried every which way I can think of to get this to work on V10, but have had no luck. Help!?!

                    Comment


                      #11
                      Re: f_Audit() - Add auditing to any table

                      This was originally designed and tested in version 8. I have yet to try it in v10 so can not attest to it working or not working in version 10.

                      Are you getting an error message? What operating system are you using? Have you looked at the source code to see if you were able to identify the issue?
                      Andrew

                      Comment


                        #12
                        Re: f_Audit() - Add auditing to any table

                        Originally posted by aschone View Post
                        This was originally designed and tested in version 8. I have yet to try it in v10 so can not attest to it working or not working in version 10.

                        Are you getting an error message? What operating system are you using? Have you looked at the source code to see if you were able to identify the issue?
                        Hi Andrew�thanks for responding! Sorry about this but I�m not a computer guy�just someone stuck with trying to make this work. I�m creating a web accessible database in Alpha 5 v10. My operating system is Windows XP. I�m not getting any errors�I�m simply not getting anything. When I click the Add-in (after I installed it all), nothing happens.

                        I found a post where someone had detailed out exactly what your script installed into each event. I�ve manually pasted them in and am sorting out what does what. I�ve got parts working, but I�ve a long way to go.

                        My goal (on this aspect of the project) is to create a audit trail of changes/deletions/new records�ideally saving key fields in a audit table. I�m trying to figure out how to get your bits to save the field values instead of the �property_to_strings� function, which isn�t large enough nor researchable. Might you tell me how I can modify your line of:

                        �audit.old_rec_f = old_rec� to something like:

                        �audit.old_rec_f1 = var->inst_title�

                        --with �var->inst_title� being a field from my table.

                        Thanks for anything!

                        Comment


                          #13
                          Re: f_Audit() - Add auditing to any table

                          Originally posted by leonard.taylor View Post
                          Hi Andrew�thanks for responding! Sorry about this but I�m not a computer guy�just someone stuck with trying to make this work. I�m creating a web accessible database in Alpha 5 v10. My operating system is Windows XP. I�m not getting any errors�I�m simply not getting anything. When I click the Add-in (after I installed it all), nothing happens.

                          I found a post where someone had detailed out exactly what your script installed into each event. I�ve manually pasted them in and am sorting out what does what. I�ve got parts working, but I�ve a long way to go.

                          My goal (on this aspect of the project) is to create a audit trail of changes/deletions/new records�ideally saving key fields in a audit table. I�m trying to figure out how to get your bits to save the field values instead of the �property_to_strings� function, which isn�t large enough nor researchable. Might you tell me how I can modify your line of:

                          �audit.old_rec_f = old_rec� to something like:

                          �audit.old_rec_f1 = var->inst_title�

                          --with �var->inst_title� being a field from my table.

                          Thanks for anything!
                          I do not do any work on the web side but I do believe that some of the table events do not fire, you will need to research if the events I am using are firing from the web side.

                          I do not understand what you mean by
                          �property_to_strings� function, which isn�t large enough
                          I was saving the output of the property_to_string to a memo field which should be big enough to handle it.

                          I do understand about the unsearchable part. A while back i wrote a companion function that would return just the changed bits between the old rec and the new rec.

                          Ex. f_AuditHistory(table.current(),.t.) I call it from my audit form and save it to a third memo field. Then only display the 3rd memo field. I am not sure if this function was included in my original aex file.


                          �audit.old_rec_f = old_rec� to something like:

                          �audit.old_rec_f1 = var->inst_title�
                          Where is this code at? It has been so long since I messed with most of it that I have forgotten all the little details.

                          you could try using the eval() function wrapped around your 'var->inst_title'

                          On the other hand I wanted the audit functions to be generic enough that it didn't need to be aware of specific fields within the table.
                          Andrew

                          Comment


                            #14
                            Re: f_Audit() - Add auditing to any table

                            Thanks again for helping Andrew�I really appreciate it and your time! I�m weeding my way through which events fire, and how the different scripts interact with each other, towards my goal (trial and error method, 1 line at a time�). I�m keeping myself sane by chanting �learning curve� mantra.

                            Regarding my �researchable� comment, I�d like to have a way to review a history of changes associated with a specific entry group. I�m trying to manage about 1,400 academic course and appointment entries being tweaked by several different people. I need to be able to quickly find who did what and when. As I�m too green to figure out how to create a search tool to examine the audit-trail created by the �property_to_string� function, I figured the easiest would be to make a table that collected all key fields every time someone changed/deleted/added anything. I could easily then make a tool to search this table for the history.

                            The best approach I�ve found to date is to use your scripts to record to an audit table, and I just figured out how. I simply needed to give my variable a location using �tc� (table.current). I�ve changed your line:

                            �audit.old_rec_f = old_rec� which is in the Table>Field Rules>Events Record Events>� to:
                            �audit.old_rec_f1 = tc.inst_title� which saves the contents of the field to the audit table.

                            With this, I�ve got a good direction to follow on this aspect of the project. Thanks again for the input�maybe I should take a class or something?

                            Comment


                              #15
                              Re: f_Audit() - Add auditing to any table

                              Take a look at "Xbasic for Everyone" by Dr. Peter Wayne. Chapter 42. An Audit Trail. This is where I got a majority of the code. From there I tweaked bits and pieces.

                              The audit functions should have already been writing the changes to a audit table. From there you can use the f_AuditHistory() to find just the bits that changed. Save the changed bits to a new field, then you can write a routine that will search the changed bits.
                              Andrew

                              Comment

                              Working...
                              X