Skip to main content
Question

Date formats for periodID fields


Forum|alt.badge.img

I’m working on a report based entirely off of PMTRAN (with PMCOSTCODE linked in to give me access to CostCodeCD).  I’m starting with parameters for project, startperiod & endperiod.  I’m then trying to filter so that I only select records from PMTRANS where PMTRAN.ProjectID = @project and where PMTRAN.FINPERIODID is between @startperiod and @endperiod.

I can’t seem to get my date parameters into a format that is consistent with PMTRAN.FINERIODID.  I’ve tried

  1. creating the parameters as ‘string’, ‘date’, and ‘integers’.
  2. creating a variable based on the parameters to put them in yyyymm format.
  3. creating variables to put all of the dates into yyyymm format.

nothing seems to work.

This is how I’m defining the parameters

view name
=Report.GetFieldSchema('OrganizationBranchReportParameters.FinPeriodID')

default value
=Report.GetDefExt('OrganizationBranchReportParameters.FinPeriodID')

But I’ve also tried

view name
=Report.GetFieldSchema('PMTran.FinPeriodID')

default value
=Report.GetDefExt('OrganizationBranchReportParameters.FinPeriodID')

 

I’ve looked at a bunch of other reports where period ranges are used and can’t seem to find anything that works with PMTRAN.FINPERIODID

Any suggestions?

4 replies

BenjaminCrisman
Acumatica Employee
Forum|alt.badge.img+4
  • Acumatica Support Team
  • 625 replies
  • August 1, 2024

@RHarrison You don’t actually have to join a table in to get a date parameter. You can add a table to the report which has the parameter type needed, then use the GetFieldSchema() function on this, but this added table does not need to be added to Relations tab.


Forum|alt.badge.img
  • Author
  • Freshman I
  • 35 replies
  • August 1, 2024
BenjaminCrisman wrote:

@RHarrison You don’t actually have to join a table in to get a date parameter. You can add a table to the report which has the parameter type needed, then use the GetFieldSchema() function on this, but this added table does not need to be added to Relations tab.

I’m not adding the table to get period info, just to get the costcodeCD for sorting.

My problem is that the parameter I create seems to be in a different format that PMTRAN.FINPERIODID.

I can’t do basic comparisons like >= or <=.

The only way I can get them to work is if I build variables for both FINPERIODID & my parameter by doing this

$FinPeriodIDYYMM = right([PMTran.FinPeriodID],4)+left([PMTran.FinPeriodID],2)

and

$StartPeriod YYMM =right(@StartPeriod,4)+left(@StartPeriod,2)

Then I can compare the two variables but this seems like a lot of unnecessary work.


Chris Hackett
Community Manager
Forum|alt.badge.img
  • Acumatica Community Manager
  • 2621 replies
  • September 9, 2024

Hi @RHarrison were you able to find a solution? Thank you!


Forum|alt.badge.img
  • Author
  • Freshman I
  • 35 replies
  • November 28, 2024
Chris Hackett wrote:

Hi @RHarrison were you able to find a solution? Thank you!

Chris, I’ve still not found a solution to this. And I’m now having the exact same issue while working with GLTran.FinPeriodID.

I have a parameters defined as @StartPeriod & @EndPeriod 

viewname:  =Report.GetFieldSchema('OrganizationBranchReportParameters.FinPeriodIDByBAccount,OrgBAccountID,UseMasterCalendar,UseMasterCalendar')

defaultvalue:  =Report.GetDefExt('OrganizationBranchReportParameters.FinPeriodID')

 

I am then selecting records in my filter like so GLTran.FinPeriodID <= @EndPeriod

That works.

But If I try to build up an opening balance by defining a variable as 

OpenBal = $OpenBal + iif([GLTran.FinPeriodID]<@StartPeriod, [GLTran.DebitAmt]-[GLTran.CreditAmt], 0)

And this blows up.  

If my @StartPeriod is 02-2023 it seems to be stored at 022023 and then dates at the end of 2022 (ie, December 2022) end up being >.  It seems like I’m ending up with 122022 > 022023.  

I can’t figure out why the filter select is working but the variable math isn’t.  

I clearly don’t understand how these date fields are being stored and used.  

I really don’t want to have to build intermediate variables to convert all of my dates into yyyymm format so that my math works.

 


Cookie policy

We use cookies to enhance and personalize your experience. If you accept you agree to our full cookie policy. Learn more about our cookies.

 
Cookie settings