Skip to main content

Notifications

Announcements

No record found.

News and Announcements icon
Dynamics 365 Community / Forums / Microsoft Dynamics SL forum / looking for SL sample ...
Microsoft Dynamics SL forum
Answered

looking for SL sample database (or scrubbed real one) for fiscal year info, specifically table GlSetup

(0) ShareShare
ReportReport
Posted on by JonathanCampbell 75

I work on a product (an ETL tool) that reads account history data from SL (and other Dynamics GLs). The sample provided only spans 3 periods of 1 year of data. Unfortunately I don't have the time to learn to use SL at this time to add years/periods; I need to release this ETL tool imminently. To formulate a fiscal period end dates (into SQL Server datetime), I've been concatenating BudgetYear and FiscalPerEnd00 in table GlSetup, etc., but encountered a customer that has NULL in BudgetYear.

Not sure how to proceed. Is this customer's GlSetup corrupt, or is this a normal? If the latter, where would I find a year to use?

Obviously I'm an SL newbie; the code written by my predecessor created a kludge calendar from the actual history data, making it kind of difficult to create budget data beyond the current data years...any help would be appreciated.

  • Verified answer
    JonathanCampbell Profile Picture
    JonathanCampbell 75 on at
    RE: looking for SL sample database (or scrubbed real one) for fiscal year info, specifically table GlSetup

    I found it. The fiscal year table in SL is PJFISCAL. It has fy/period and period start and end dates, very similar to the one in Navision.

  • JonathanCampbell Profile Picture
    JonathanCampbell 75 on at
    RE: looking for SL sample database (or scrubbed real one) for fiscal year info, specifically table GlSetup

    Hi, Butch,

         Actually, what I'm looking for is a range of years and periods applicable to the G/L data. I assumed (perhaps incorrectly) that there would be a row for each fiscal year, since I noted that the sample, which as I mentioned only had 3 months of 1 year, specified the ending dates as Jan30, Feb28, etc. This is obviously not applicable for leap years...Perhaps I am asking too much; the AcctHist entries do have the periodID, so FY dates are somewhat immaterial, I could perform aggregation based on periodID rather than bracketing data by date. It would just be a bit more work for me.

         My model for this mechanism is Dynamics NAV, which has a row for each period, and is completely date-driven.

    Thanks

    Jon

  • Butch Adams Profile Picture
    Butch Adams 4,757 on at
    RE: looking for SL sample database (or scrubbed real one) for fiscal year info, specifically table GlSetup

    Budget year is not a required field, so you cannot depend on it being there. (SL doesn't force you to budget!)

    You could use PerNo to get your current period in yyyypp format or LastClosePerNo to get the last closed period.

    Respond if you can help me narrow down what you need with more info.

    ...b

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Anton Venter – Community Spotlight

Kudos to our October Community Star of the month!

Announcing Our 2024 Season 2 Super Users!

A new season of Super Users has arrived, and we are so grateful for the daily…

Dynamics 365 Community Newsletter - September 2024

Check out the latest community news

more Community News

Leaderboard

#1
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 290,558 Super User 2024 Season 2

#2
Martin Dráb Profile Picture

Martin Dráb 228,645 Most Valuable Professional

#3
nmaenpaa Profile Picture

nmaenpaa 101,148

Leaderboard

Featured topics

Product updates

Dynamics 365 release plans