Lonely Shell Variables – Part 2

If you go back now to the business examples about territories and week’s average, think about how @vars can be used. You can have a program that gets the key values, and then use the @`@var` syntax to use the values in your queries.

Don’t stop there. Instead of generating values, think about generating parts of your query. It’s just a string of text, right? What if you have a query where the dict definitions you use change from one fiscal period to the next? Something like this (completely contrived):
SORT G/L-SUMMARY REVENUE-03 EXPENSES-03 PROFIT-03
If you want to generate a new statement every period with dict items like that, you need to run a program. As a final example, here’s another way to do that.

Create a program called Current.Fiscal that returns the period as 01 to 12 (yes yes or 13 for some companies…).

Create an @var to retrieve that value:
set FISCAL=run bp Current.Fiscal
(I’m intentionally mixing cases here – D3 is case-INsensitive by default so this isn’t normally a problem. But D3 can be made case-sensitive. If your system is configured to be more case-sensitive you’ll need to apply those rules to your shell variables.)

Now in an access query, use that special syntax:
SORT G/L-SUMMARY REVENUE-@`@FISCAL` EXPENSES-@`@FISCAL` PROFIT-@`@FISCAL`

*Gasp* That’s ugly! Let’s give it a final tweak. Put the ugly text into an @var by itself:
set PERIOD_INFO=REVENUE-@`@FISCAL` EXPENSES-@`@FISCAL` PROFIT-@`@FISCAL`

And now, finally…
SORT G/L-SUMMARY @PERIOD_INFO

In that case, the Set statements for both FISCAL and PERIOD_INFO will need to be re-executed in order to make a new period available to queries, but since these fiscal periods only change once a month, in this case it’s OK.

There’s actually a cleaner way to do that. (Did I say “finally”?) The brief Current.Fiscal program doesn’t need to just generate 01 through 12, it can generate the entire PERIOD_INFO string, then write it to file var: item PERIOD_INFO. If you do that you won’t need @FISCAL at all and @PERIOD_INFO will look very clean.

Once you understand how these @vars are built and how they work together you can make better decisions about where to generate data and where to put various “components” that you may want to use.

Summary

In this blog/article I’ve provided more examples of simple shell variable usage, and quickly went from there to more complex examples. If you play with them a bit, you’ll probably find use for both simple and complex cases. Of course you’ve survived your entire Pick/MV career without this feature, but like any feature, once you know it’s there and can help with various tasks, you’ll probably want to use it occasionally.

If you do use these @vars, these “lonely shell variables”, please post a note here and let us know how. If you know more tricks with these guys, please share. If you do decide to dabble with this stuff – Have fun!