Skip to main content
Solved

How do i access the value of a date stored in a user-defined field in a generic inquiry calculation


Forum|alt.badge.img

I am building a GI that needs to be able to pull a user defined field date and perform calculations on it such as dateadd or datediff. When I try to do this I get a virtual field error message coming up on my gi. If this date field was stored in an attribute I know how to get the date value from the CS answers table but it seems that user defined Fields do not store the values in the CS answers table - where are these stored in how can I perform calculations against them?

Best answer by DanielStarke

I have cracked the code! we will use it in a filter and we use the where scheduled date is less than or equal to  @today for the today's schedule and the same formula + 1 for tomorrow's schedule etcetera

View original
Did this topic help you find an answer to your question?

11 replies

Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • October 30, 2021

Hi @DanielStarke  If possible, can you please share the GI here.


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • October 30, 2021

Hello @Naveen B it is the same GI and scheduled date field we were working on yesterday. Link below. 

 

I want to be able to have a column that says the number of days ago that it was scheduled, as in something like Today() - scheduledate . But this will not work as the scheduled date is not accessible to do calculations on

 

I'm also having to do a slight add on to adjust the dates for our NZ timezone

 

https://community.acumatica.com/topic/show?tid=7116&postid=22937


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • October 31, 2021

=[SOOrder.AttributePCSCHEDDAT] - today() is the formula i have in my GI at present where the scheddat is an attribute connected to the Sales order UDF 

this is the message that pops up

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • October 31, 2021

Hi @DanielStarke Hope you are trying to get the difference between the Today and the Scheduled Date.

Please find the formula below for your reference.

=DateDiff( 'd', Today(), [SOOrder.AttributeNDATE])

 

 


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • October 31, 2021

Hello @Naveen B , it almost looks right - I am getting another sort of error now: 

 

 


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • October 31, 2021

I noticed you had an N in your attribute name, so i added it, but also i tried without and i get the same error


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • November 1, 2021

Hello @Naveen B  I have been told that the data fields are stored differently in MYOB Advanced (Which I use) 

 

@dipikaa62  - not sure if this is your acumatica handle, but… :) 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • November 1, 2021

Hi @DanielStarke  I checked in Acumatica and it is working as expected, will check in MYOB instance and let you know here.

 


Naveen Boga
Captain II
Forum|alt.badge.img+19
  • Captain II
  • 3381 replies
  • November 1, 2021

Hi @DanielStarke  I just verified in MYOB instance and validation is passed.

But when I check with GI result, actually it is NOT calculating the Date Difference, where as it is calculating in the Acumatica instance.

This might be the limitation of MYOB, but I’m NOT sure.

 

 


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • Answer
  • November 1, 2021

I have cracked the code! we will use it in a filter and we use the where scheduled date is less than or equal to  @today for the today's schedule and the same formula + 1 for tomorrow's schedule etcetera


Forum|alt.badge.img
  • Author
  • Varsity I
  • 61 replies
  • November 5, 2021

argh… now i have another issue…. what about friday… when you want to see monday as “tomorrow”

 

this now links to another thread on here: 

 


Reply


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