Hi All,
I am looking for some technical help on a grid view issue. I have some views being called by command line for various data. For the AR and AP data, I need to add fiscal period and year to each transaction record. Since the fields are not available in the field list, I would like some ideas on how to get the fields without a huge formula, and be able to link it to the fiscal calender, while still being able to call the view and export to Excel from the command line. Thanks for any ideas.

Hi Laura,

It depends on whether the fiscal calendar and actual calendar match. If so then I would just use the Date/Time functions when creating a calculated column to pull them from the Transaction date field and create a new column in the view. So to get the fiscal period I would use: MONTH({Trx Date} ). For the fiscal year: YEAR({Trx Date} )+1900. You might have to adjust the formatting for the column to get rid of decimal places.

If they don't match then you will need to adjust the formula, let's pretend they have an October year end. To get the fiscal period I would use: If (MONTH({Trx Date} )<10 , MONTH({Trx Date})+2, MONTH({Trx Date})-10). For fiscal year: If (MONTH({Trx Date} )<10 , YEAR({Trx Date})+1900, YEAR({Trx Date})+1901).

Send me an email if you have any more questions,
Lauren

Hi there,

What exactly are you trying to get to. The fiscal year and period are store in the GL transaction file, along with most of the other data from at AR/AP transaction. That would be a simple way to get to that.

If your fiscal calendar follows the natural calendar, then Lauren's approach works well.

If you fiscal calendar is 13 period or something odder, then I can send you a workspace that uses GETLINKEDVALUE() to get the year and period.

The next Upgrade to GridView includes functions to calculate this from a given date - but you'll have to wait for that a bit.

It does not follow the natural calendar (that would be too easy). I am taking detailed information from the AR records (Invoices, aging and one for cash) and another view pulls the AP records (Invoices, aging and disbursements). I appreciate any tips.

Check the fields in the General Ledger Posted Transaction file and see if they meet your requirements.

Hello:
Laura's formulas will work with a fiscal year that doesn't follow the natural calendar. GV also has a function named FiscalStart() to help determine the 1st day of the fiscal year (though you will still need a formula).

Thanks Bruce, The calendars are all over the place - through the years they had two short fiscal years and they never follow any pattern. I could make a formula, but I wanted that to be a last resort. I will take Andrew's advice and pull from the GL. It's not ideal, but I think I can make it work. Thanks all!

Here's a workspace that applies the Fiscal Year and Period to the AR/AP Transactions. It will be accurate as long as no one changed the Fiscal Period when the batch was retrieved to Ledger, and they always assigned the period based on the date of the transaction.