By using this website, you agree to our Terms of Use (click here)
I needed to convert a Financial Period in Acumatica to the last day of the month in that Financial Period.
If you look at the End Date field on the Master Financial Calendar (GL201000) screen, you'll see that it refers you to the MasterFinPeriod.EndDateUI field but for some reason Acumatica won't allow me to use that field in a calculation.
So, I came up with a really ugly formula instead. Jotting it down here so I can remember it in the future.
This formula assumes that your Financial Periods are monthly buckets where the first two digits are the month number (01-2019, 02-2019, ... 12-2019) and you are using a Calendar year. This also assumes that you are in the United States and you write dates like a Neanderthal in the format of Month/Day/Year instead of the civilized way of Day/Month/Year.
=DateAdd(DateAdd(CDate(Left([@FinancialPeriod],2)+'/1/'+Right([@FinancialPeriod],2)),'m',1),'d',-1)
I'd love to know if anyone has a better way of doing this.
I'm confused. Isn't this what you want?
FinDate from FinPeriod table is what you want. FinDate i am assuming means Finish Date as there is also a corresponding StartDate, being ofcourse the first date of the period.
I think FinPeriod.FinDate became MasterFinPeriod.EndDateUI when Acumatica started allowing multiple Fiscal Calendars in the same Tenant.
Are you able to use FinPeriod.FinDate in a calculation like comparing to another date to see if it's on or before that date?
I think FinPeriod.FinDate became MasterFinPeriod.EndDateUI when Acumatica started allowing multiple Fiscal Calendars in the same Tenant.
Are you able to use FinPeriod.FinDate in a calculation like comparing to another date to see if it's on or before that date?
Of course - here's a simple query showing all periods where FinDate is greater than today's date (hard-coded):
(partial results shown)
Interesting. Seems like they broke it when they introduced MasterFinPeriod.
I find that really hard to believe. The period table needs to have period start and end dates. FinPeriod also has EndDateUI. That is a different field.
Are you saying that MasterFinPeriod doesn't have FinDate and StartDate?
Here are some screenshots.
First, my Master Financial Calendar (GL201000) screen:
Second, relevant fields from the MasterFinPeriod table displayed in a Generic Inquiry:
Hi Tim,
I'm having trouble with the MasterFinPeriod.FinPeriodID schema as the database update removed the FinPeriod table. Now the FinPeriodID field is a Int32 where my reports are calling for a DateTime type.
I've tried converting to CDate like this: =CDate( DateAdd([MasterFinPeriod.FinPeriodID],'d', -1) ) to no avail.
Quote from my trace:
8/13/2020 9:07:40 PM Error:
Can't calculate expression node: 'ReportNode dataItem: '.
PX.Common.Parser.ExpressionException: Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.
at PX.Common.Parser.ExpressionContext.CheckDateProperty(String method, Object value, Int32 paramIndex)
at PX.Common.Parser.ExpressionContext.DateAdd(FunctionContext context, Object date, Object interval, Object number)
PX.Common.Parser.ExpressionException: An error has occurred while the DateAdd(Identifier(MasterFinPeriod.FinPeriodID), Const(d), UnaryOp: - (Const(1))) function was being executed:
'Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.' ---> PX.Common.Parser.ExpressionException: Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.
at PX.Common.Parser.ExpressionContext.CheckDateProperty(String method, Object value, Int32 paramIndex)
at PX.Common.Parser.ExpressionContext.DateAdd(FunctionContext context, Object date, Object interval, Object number)
--- End of inner exception stack trace ---
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Reports.Data.SectionNode.a(ExpressionNode A_0)
PX.Common.Parser.ExpressionException: Can't calculate expression part: 'CDate(DateAdd(Identifier(MasterFinPeriod.FinPeriodID), Const(d), UnaryOp: - (Const(1))))'. ---> PX.Common.Parser.ExpressionException: An error has occurred while the DateAdd(Identifier(MasterFinPeriod.FinPeriodID), Const(d), UnaryOp: - (Const(1))) function was being executed:
'Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.' ---> PX.Common.Parser.ExpressionException: Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.
at PX.Common.Parser.ExpressionContext.CheckDateProperty(String method, Object value, Int32 paramIndex)
at PX.Common.Parser.ExpressionContext.DateAdd(FunctionContext context, Object date, Object interval, Object number)
--- End of inner exception stack trace ---
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Reports.Data.SectionNode.a(ExpressionNode A_0)
--- End of inner exception stack trace ---
at PX.Reports.Data.SectionNode.a(ExpressionNode A_0)
at PX.Reports.Data.SectionNode.ProcessItem()
at PX.Reports.Data.ItemNode.Process(Object dataItem)
PX.Common.Parser.ExpressionException: Can't calculate expression node: 'groupHeaderSection2 (PX.Reports.Data.GroupSectionNode) dataItem: System.Object[]'. ---> PX.Common.Parser.ExpressionException: Can't calculate expression part: 'CDate(DateAdd(Identifier(MasterFinPeriod.FinPeriodID), Const(d), UnaryOp: - (Const(1))))'. ---> PX.Common.Parser.ExpressionException: An error has occurred while the DateAdd(Identifier(MasterFinPeriod.FinPeriodID), Const(d), UnaryOp: - (Const(1))) function was being executed:
'Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.' ---> PX.Common.Parser.ExpressionException: Type mismatch in the argument 1 of the DateAdd() function: The argument must have the DateTime type.
at PX.Common.Parser.ExpressionContext.CheckDateProperty(String method, Object value, Int32 paramIndex)
at PX.Common.Parser.ExpressionContext.DateAdd(FunctionContext context, Object date, Object interval, Object number)
--- End of inner exception stack trace ---
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Common.Parser.FunctionNode.Eval(Object row)
at PX.Reports.Data.SectionNode.a(ExpressionNode A_0)
--- End of inner exception stack trace ---
at PX.Reports.Data.SectionNode.a(ExpressionNode A_0)
at PX.Reports.Data.SectionNode.ProcessItem()
at PX.Reports.Data.ItemNode.Process(Object dataItem)
--- End of inner exception stack trace ---
at PX.Reports.Data.ItemNode.Process(Object dataItem)
at PX.Reports.Data.ReportNode.a(Object A_0, Int32 A_1)
at PX.Reports.Data.ReportNode.a(IDataNavigator A_0, Int32 A_1, ProcessDataHandler A_2)
at PX.Reports.Data.ReportNode.a(Object A_0, Int32 A_1)
at PX.Reports.Data.ReportNode.a(IDataNavigator A_0, Int32 A_1, ProcessDataHandler A_2)
at PX.Reports.Data.ReportNode.ProcessItem()
at PX.Reports.Data.ItemNode.Process(Object dataItem)
at PX.Reports.Data.ItemNode.Process(Object dataItem)
at PX.Reports.Data.ItemNode.Process()
at PX.Reports.Data.ReportNode.Process()
at PX.Reports.Data.ReportProcessor.ProcessReport(Report definition)
at PX.Data.PXLongOperation.<>c__DisplayClass68_0`1.b__0()
at PX.Data.PXLongOperation.<>c__DisplayClass17_0.b__0()
HELP Please!
You should be using the date fields that are on MasterFinPeriod, either StartDate (1st day of period) or FinDate (last day of period).
Online Members
