Forum Discussion

MarciaMiller's avatar
MarciaMiller
Qrew Member
3 years ago

Using custom data rules to cleanse data in tables?

Hi all... I was searching this morning on the internet for a "how to" on creating exception reporting in QB.  I came across several posts on using custom rules to validate data during input/imports.  I have a need to validate data that is currently in tables within the QB app that was developed.  This app has been in existence for a few months and it looks like the data that has been added by users is not always correct.  I'm trying to find a way to evaluate all the data in various tables and identify exceptions to business rules.  Ideally I'd like to have a report that does this validation daily so that the data remains clean.  Any thoughts on how this can be accomplished?

------------------------------
Marcia Miller
------------------------------
  • Not use custom data rules to validate existing data because you will block all your users from saving data.  This would probably be pretty annoying to the users to not be able to do their day-to-day jobs. 

    What I like to do is make an all purpose warnings field that will show at the top of the form and I use the formula to only display it if it is not blank.

    Here's an example:

    var text Warnings =

    List("<p>",

    If(IsNull([GIT Load#]), "This Inbound Load is not connected to a Goods in Transit Record. Edit the record to enter the Record ID# of the GIT Loads record. If this is not done, then updates to this Transload record will not be able to trigger the required updates in the GIIT Load record."),

    If([Any Transload Pallets w/o Order#?] or [Any Transload Pallets w/o Warehouse?], "There are TransLoad Pallets Missing Stock Transfer Backorder Allocations. You may not create Labels until this is corrected"));

    If($Warnings <> "",
    "<font color=red><b><font size=+1>" & $Warnings) 

    Do you have a lot of tables you can do a formula like this on each table and then have a report where the warnings is not blank. If you'd like you can make a dashboard filled with the actual reports of records and warnings. You can have up to nine reports on the traditional homepage. So then maybe it's some instruments day to go to the dashboard and make all the reports go blank by fixing all the errors. 

    Now, once you get your day to totally clean if you'd like you can implement a custom data rule and that will absolutely protect against bad data coming in but it will also block imports so you have to consider the user experience.

    ------------------------------
    Mark Shnier (YQC)
    mark.shnier@gmail.com
    ------------------------------
    • MarciaMiller's avatar
      MarciaMiller
      Qrew Member
      Thanks Mark.  This is helpful.  Just to clarify, we have several master data tables within one application.  I need to compare the table where users enter information to the master data tables to ensure that the information is correct.  So I'm comparing fields in the master data tables to fields in the user input table and identifying those that are not the same.

      ------------------------------
      Marcia Miller
      ------------------------------