Solved

Share a formula to get the current FinPeriod to be used in Generic inquiry

  • 30 June 2022
  • 7 replies
  • 917 views

Userlevel 6
Badge +5

Hello,

    Irina previously shared a trick to get the current finperiod as default in GI.  This worked fine in 2020R2, but it does not work in 2021R2. 

 

Normally, we just only want the current period data.

The below trick is working. If someone has better solution, please also share. Thanks.

 

Although it shows 06-2022 in screen, it actually stores as 202206 in DB, so I am using the below formula to calculate current fin period.

=IIf( Month( Today())>9, Concat( CStr( Year( Today())),CStr( Month( Today()))),  Concat( CStr( Year( Today())),'0',CStr( Month( Today()))))
And then put it in GI conditions.

 

icon

Best answer by vkumar 9 July 2022, 06:17

View original

7 replies

Userlevel 1

A possibly simpler formula

= CStr(Year( Today() )) + PadLeft(CStr(Month( Today() )), 2, '0')

Userlevel 7
Badge +11

Hi @ray20 

Slight changes, something I used earlier, this works as well. 

=iif(Month(Now())<10,CStr(Year(Now()))+'0'+CStr(Month(Now())),CStr(Year(Now()))+CStr(Month(Now())))

 

Regards,

 

Userlevel 5
Badge +2

These formulas assume that MasterFinPeriod was setup to match calendar periods. 

If using user defined periods or any other financial calendar that doesn’t match, formulas won’t work.

Why not just Cross join MasterFinPeriod in your GI and find the required period for whatever date is needed? 

Userlevel 6
Badge +5

@RoyceLithgo 
Yes, you are right. Our method has obvious limitations.
Can you give more details on your method?  Can you give us a simple example?

Userlevel 7
Badge

Hi @ray20 - were you able to find a solution for your issue? Thank you!

Userlevel 6
Badge +5

 @vkumar @joshmagalich27 
Hi Folks, just to update.  @RoyceLithgo is right. we can join “FinPeriod” table to get the current or any period we want.
“PX.Objects.GL.FinPeriods.TableDefinition.FinPeriod”

Note: this table has multiple finperiods splitted by organization/company. So in order to avoide duplicated data, we have to carefully make the join condition like below:
 

 

Then we can use the Finperiod table, to get the period we want.
Below is to get the last period leveraging the “Start date” definitions of the period.

 

Userlevel 5
Badge +2

 @vkumar @joshmagalich27 
Hi Folks, just to update.  @RoyceLithgo is right. we can join “FinPeriod” table to get the current or any period we want.
“PX.Objects.GL.FinPeriods.TableDefinition.FinPeriod”

Note: this table has multiple finperiods splitted by organization/company. So in order to avoide duplicated data, we have to carefully make the join condition like below:
 

 

Then we can use the Finperiod table, to get the period we want.
Below is to get the last period leveraging the “Start date” definitions of the period.

 

Thanks for posting this but it’s not quite the way i do it. For finding the correct period, you want StartDate <= [ReportDate] and FinDate >= [ReportDate]. [ReportDate] being a parameter in this GI.

Here’s an example usage:

I then used MasterFinPeriod in the joins for other table relations. Depending on your needs you could also do Inner join to MasterFinPeriod if you want to filter each row of the Left table.

Reply


About Acumatica ERP system
Acumatica Cloud ERP provides the best business management solution for transforming your company to thrive in the new digital economy. Built on a future-proof platform with open architecture for rapid integrations, scalability, and ease of use, Acumatica delivers unparalleled value to small and midmarket organizations. Connected Business. Delivered.
© 2008 — 2024  Acumatica, Inc. All rights reserved