I thought I'd add an update in case anyone wanted to try using a "constants table". I decided to use the UD fields in the company file rather than use a separate UD table. I seem to remember several postings that advised starting table building with the company file, why not make use of that. My method seems to be working fine with one exception. You can't filter on another table when you're working with a summarized table in a BAQ (at least not in 8.03.409C). You can filter on a constants field such as fiscal year, but not on a field in another table. I put in a support call but will be terribly shocked if Epicor puts out a SCR to fix it in Vantage 8.03.4xx. I suspect it's a "design limitation"; it probably won't work for the same reason you can't link a table more than once in Vantage BAQs. I would be curious if this would work in Epicor 9. Or are there additional constants available (such as prior year) in Epicor 9? I think I also read in the user group that many of the constants don't work in Vantage; are they fixed in Epicor 9? I'd like to be able to someday automate prior year dashboards that use summary tables.
Sue
Sue
--- In vantage@yahoogroups.com, "snielsen28" <snielsen.hipco@...> wrote:
>
> I have a number of BAQs that need to be changed annually because of the use of the current/prior year for filtering or in calculations. I also have a number of BAQs used in dashboards for exporting into Excel files to be submitted for 401k contributions. These need to use the last pay date in the prior year as a filter. I know there's a constants>fiscal year and a constants/year that can be used for filtering. However, I can't find a way to use this field less 1 (constants>fiscal year - 1) to get the prior year. Using today's date less 365 gives me a "rolling year", I need a fiscal year. I'm experimenting with using a UD table to store "constants". I've added current year, prior year, current PR year, prior PR year (not sure if I need to separate PR years from other years) and last pay date of prior year to a record in this table. I've rebuilt some BAQs using this table as the top parent table with company as the key. It seems to work fine, even with multi-company dashboards. The idea is to simply change current and prior years along with the last pay date of prior year in one spot and "Presto! All year end changes have been made!". I'm still fairly new at this and thought I'd throw this out to the group. Is there an easier way that I'm not seeing? Are there any drawbacks to setting things up this way? Any feedback would be appreciated!
>
> Sue
>