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?
Solved
How do you create a filter for a GI/Dashboard which returns results for Yesterday, or Friday if Monday
Best answer by gprice27
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..
Just wondering if
The ones marked with the Red arrow below are easy for monday - thursday, but on friday it is a challenge?
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…
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])
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:
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
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...
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..
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..
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.