Solved

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

• 7 replies
• 917 views

Userlevel 6
+5
• Captain II
• 397 replies

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
+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
+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
+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

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

Userlevel 6
+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
+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.