Sage 500 Validate GL Account SP?

I've just requested the SDK documentation for V2014 as I seem to not have received it, but...

Is there a straight-forward SP or sequence that I can use to accept a GL Account number and validate it against my live database? 

I will probably want it to be done as a Crystal Parameter request...


  • There are a couple of stored procedures you could look at, but both require a bit of data to run them.  The first is spglAPIValidateAccount.  This stored procedure will determine if a GL account exists and if not will create it if you ask it to.  It has 51 parameters (input/output) associated to it.

    The second routine you could look at is spDMValidateGLAcct which is used by Data Migrator and makes a call to spglAPIValidateAccount.  This stored procedure requires 15 parameters as well as require the temp table #DMValGLAcct exists and be pre-populated with data.  

    Both of these stored procedures are pretty coding intensive especially for use within a Crystal report.  I would suggest reviewing each and determining what parts you actually need and create a "Custom" procedure or function that you can call from Crystal easily to get the results you desire.

  • I'd say write your own.  If you just need to verify it exists that's a couple of lines of code.  Probably could do it in crystal in a formula also (though I'd have to look that up to verify.

  • In Crystal XI you can tie the parameter to a SQL field. Then you don't need to validate it because you know that the user can only select a valid value.

    Just change List of Values: Static to List of Values: Dynamic. Then pick Choose a Data Source: New. In the Value column pick vdvGLAccount.FormattedGLAcctNo if you want the dashes or vdvGLAccount.GLAcctNo if you don't want them.

  • Thanks very much Tim, Lou, and John.  I always hope for a simple solution where we can 'borrow' some already existing efforts...  I found the multitudinous parameters in those 2 sp's to be just a bit beyond KISS.  I guess while we are in Crystal, we'll use your suggestion Tim - thanks for the table references.

    On to the next project, thanks again.