Question

How do you create a filter for a GI/Dashboard which returns results for Yesterday, or Friday if Monday

  • 1 June 2021
  • 9 replies
  • 106 views

I am looking to add quotes, orders, and sales from the previous workday on to a dashboard. We work Monday to Friday. I have used the “date” equals “@today-1” which works for Tuesday-Friday. However, when using it on Monday I do not get any results because it is looking at Sunday instead of Friday. Is there a way to add a condition to the filter so that it only looks at weekdays or an If statement so the previous Friday result if it is Monday?


9 replies

Userlevel 2
Badge

Did you have any joy figuring this one out?

Userlevel 2
Badge

Just wondering if @Naveen B might be able to shed some light on this one?

The ones marked with the Red arrow below are easy for monday - thursday, but on friday it is a challenge?

 

Userlevel 2

Hey Marty - Maybe this will work - use the @WeekStart(Sunday for EN-US) and @WeekEnd(Saturday) to get what you need - please check my math…

 

 

Userlevel 2
Badge

Hey Marty - Maybe this will work - use the @WeekStart(Sunday for EN-US) and @WeekEnd(Saturday) to get what you need - please check my math…

 

 

That would be excellent and i have tried similar, but it seems that only @today allows math in MYOB Advanced… maybe it does allow math in Acumatica

 

I’m going to try something like this:

 

What we need to do is add an additional column to the GL with the calculated Pick date.

Use a formula similar to the following:

 

The Day of the Week function returns 1=Sunday, 7=Saturday.

 

=IIf( DayofWeek([SOOrder.RequestDate])=1 OR DayofWeek([SOOrder.RequestDate])=7, Switch( DayofWeek([SOOrder.RequestDate])=1, DateAdd([SOOrder.RequestDate], 'd', +1), DayofWeek([SOOrder.RequestDate])=7, DateAdd( [SOOrder.RequestDate], 'd', +2)), [SOOrder.RequestDate])

Userlevel 2
Badge

Hey Marty - Maybe this will work - use the @WeekStart(Sunday for EN-US) and @WeekEnd(Saturday) to get what you need - please check my math…

 

 

That would be excellent and i have tried similar, but it seems that only @today allows math in MYOB Advanced… maybe it does allow math in Acumatica

 

I’m going to try something like this:

 

What we need to do is add an additional column to the GL with the calculated Pick date.

Use a formula similar to the following:

 

The Day of the Week function returns 1=Sunday, 7=Saturday.

 

=IIf( DayofWeek([SOOrder.RequestDate])=1 OR DayofWeek([SOOrder.RequestDate])=7, Switch( DayofWeek([SOOrder.RequestDate])=1, DateAdd([SOOrder.RequestDate], 'd', +1), DayofWeek([SOOrder.RequestDate])=7, DateAdd( [SOOrder.RequestDate], 'd', +2)), [SOOrder.RequestDate])

 

 

ok - turns out this does not work for me as i am wanting to use a date that is stored in the UDF of the Sales order…. values which cannot be accessed in the GI for comparative math :( …. am stuck. 

 

 

 

this now links back to another thread i have open on here: 

 

Userlevel 2

You can actually access the Attribute value (udf fields are just Attributes mapped to an entity) for the record by joining to the CSAnswers DAC using the NoteID and the AttributeID. you should then be able to use this value in any GI SQL calculation/condition.

Document.NoteID = CSAnswers.RefNoteID  and CSAnswers.AttributeID = ‘NameofAttribute’ (you should know this or get this from your udf setup...’

Acumatica adds the attribute values dynamically as virtual fields to the DAC, which is great if you just want to display them  - but because they are virtual they cant be translated into the underlaying SQL - which is why they don’t work in the Conditions Tab..

But if you do a ‘real’ join - you can use the field value in the conditions..

 

Hope the gets you unstuck…

 

best G

Userlevel 2
Badge

If I could have done this a week ago I would have... Sadly UDF answers are not stored in the CS answers table for all MYOB Advanced users and there seems no support documentation on it... 

Userlevel 2

Yes - you are correct, I should have read the question better.

User Defined fields are stored in the *KvExt tables in the Database.  If you have ever looked at the Acumatica database tables in SQL you can see each table has a matching KvExt

This article explains how to get at that data - but you need to write a customization to create a DAC that maps to the specific KvExt table you are after…  Once you have that DAC, you can use it in your GI

 

here is a stack overflow article which should get you started..

 

https://stackoverflow.com/questions/57597280/is-there-a-way-to-get-user-defined-fields-into-selectors/57601545#57601545

Userlevel 2

FYI - here is how you create the DAC using the Customization Project Editor

 

Create a customization Project

In the Code page add a new Data Access Class - example below for SOOrderKvExt

 

This is the DAC code that gets created -

then just publish the project and Bingo…

 

Here is the join in the GI using the new DAC

Just add your FieldName ’Attribute’+AttributeID to filter the join to the field you need..

 

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 — 2020  Acumatica, Inc. All rights reserved