2

We run Dynamics GP, and anybody that's dealt with GP will be familiar with all of its "magic number" columns representing the various enum values inside the application. For example, a reporting query might look something like this:

...
WHERE sod.SOPTYPE = 2
    AND iv.VCTNMTHD = 3
    AND pod.POLNESTA IN (2, 3)

...which is about as far from self-documenting as possible.

At first I had the bright idea of creating a table called 'Enums', filling it with values, and making a scalar wrapper function so you could query like this:

...
WHERE sod.SOPTYPE = Enum('Sales Doc Type', 'Order')
    AND iv.VCTNMTHD = Enum('Valuation Method', 'Average Perpetual')
    AND pod.POLNESTA IN (Enum('PO Line State', 'Released'), Enum('PO Line State', 'Change Order'))

Of course, that sucked, because the optimizer couldn't see the underlying 'constant' value, and thus made some rather poor choices since it had to make cardinality guesses.

So it needs to be an actual constant value in the code, but I'm not sure if there are any nice features built into SSMS that would make this sort of thing easy. Could I (mis)use code snippets or templates in some way? If we could do some kind of lookup/insertion to make our code look something like this, that would be awesome:

...
WHERE sod.SOPTYPE = /*Order*/2
    AND iv.VCTNMTHD = /*Average Perpetual*/3
    AND pod.POLNESTA IN (/*Released*/2, /*Change Order*/3)

I'm also not opposed to checking out SSMS add-ins.

  • Instead of the scalar function, have you tried simply joining to the table and adding a where clause for the string correlation? – scsimon Jul 18 '18 at 15:04
  • 1
    @scsimon That does work better (reads and CPU are cut roughly in half), but it's still about double what I get with simple numeric constants directly in the script. (Not to mention all the extra code bloat with the additional joins.) – db2 Jul 18 '18 at 15:20
  • 1
    Great Plains is an interesting application. Victoria Yudin's blog helps. There used to be a site called "Dynamics GP Table Reference" but it appears to be dead now. Maybe there is a replacement. Eventually you'll memorize them... :) Good luck on your quest! – Jacob H Jul 18 '18 at 17:08
  • 1
    @JacobH "Interesting" is definitely one way to describe it. We've got most of the tables we frequently touch memorized, but there are lots of little cryptic enum fields like this that are difficult to keep straight. (What was 11 in IV30300.DOCTYP again?...) Putting /*Inline Comments*/ into the code would help maintainability, and if I can also get automatic replacement when typing something like ValuationMethod:AveragePerpetual, then that would be totally sweet. – db2 Jul 18 '18 at 17:23
  • 1
    I found this cheat sheet from the GP UG if it helps you. Maybe you could make this work with extended properties? Probably would be a significant effort that could be undone with a GP upgrade though... and I don't think it would be much faster than using @scsimon suggestion. – Jacob H Jul 18 '18 at 17:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.