Sales Order ToDo filters

[DannyC]

Currently out of the box there are the ToDo filters Today, Today + Overdue, Tomorrow, Next 7 Days and so on.

I can't see an All option.

Is that something a plugin could resolve?

[Mike.Sheen]

Is that something a plugin could resolve?

Maybe?

The filters are a fixed enumeration:

Code: Select all

Public Enum Filters
   Overdue = 0
   Today = 1
   TodayPlusOverdue = 2
   Tomorrow = 3
   Next7days = 4
   Next7daysPlusOverdue = 5
   ThisMonth = 6
   AllOpen = 7
   AllCompleted = 8
End Enum

And the SQL query issued is built on those possible values - like so:

Code: Select all

sql = "SELECT RecID, ToDoNo, Subject, Body, TD_Priorities_RecID, DurationInHours, AssignedBy_HR_Staff_RecID, AssignedTo_HR_Staff_RecID, ReminderEnabled, ReminderTrigger, " &
     "ReminderPredefinedSetting, ReminderSpecificDateTime, Source_SY_Forms_ClassName, Source_RecID, Source_DisplayText, TD_ToDoTypes_RecID, UsesStatusTracking, TD_Statuses_RecID, " &
     "CompletePercentage, LastSavedDateTime, LastSaved_HR_Staff_RecID, DueDateTime, FinishedDateTime, HasBeenRead, ReminderSent " &
     "FROM TD_Main " &
     "WHERE Source_RecID = @Source_RecID "

If Manager.Staff.Username <> "Admin" And UserTodoOnly Then
   sql += " AND (AssignedTo_HR_Staff_RecID = @Current_HR_Staff_RecID OR AssignedBy_HR_Staff_RecID = @Current_HR_Staff_RecID) "
End If

'DATEADD(dd, 0, DATEDIFF(dd, 0, a.DueDateTime)) gives us DATE only (time is 00:00:00.000)
Select Case TodoFilter
   Case Filters.Overdue
      sql += " AND DueDateTime < GETDATE() AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.Today
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.TodayPlusOverdue
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) <=DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.Tomorrow
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) = DATEADD(dd, 1, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.Next7days
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) >= DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE())) AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) <= DATEADD(dd, 7, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.Next7daysPlusOverdue
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) <= DATEADD(dd, 7, DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE()))) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.ThisMonth
      sql += " AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) >= DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0) AND DATEADD(dd, 0, DATEDIFF(dd, 0, DueDateTime)) <= DATEADD(ms, -2, DATEADD(mm, 1,  DATEADD(mm, DATEDIFF(mm, 0, GETDATE()), 0))) AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.AllOpen
      sql += " AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID NOT IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

   Case Filters.AllCompleted
      sql += " AND (TD_Statuses_RecID IS NULL OR TD_Statuses_RecID IN (SELECT RecID FROM TD_Statuses WHERE PercentageComplete = 100))"

End Select

sql += " ORDER BY DueDateTime "

As there is no default in the case statement, if that code building the SQL query comes across a value outside the enumeration values when it will behave as though there was no filter.

You will also need to use reflection to access the private ToDoGridController on the form (I think all the forms with a TODO list use the same private field name) which hosts the TodoFilterCombo control to add a new value to that.
The ToDoGridController adds it's items to the ToDoFilterCombo like so:

Code: Select all

With _Host.TodoFilterCombo
   .Items.Add("Overdue")
   .Items.Add("Today")
   .Items.Add("Today + Overdue")
   .Items.Add("Tomorrow")
   .Items.Add("Next 7 days")
   .Items.Add("Next 7 days + Overdue")
   .Items.Add("This month")
   .Items.Add("All open")
   .Items.Add("All complete")
End With

So the underlying value is implied by the order ("Overdue" maps to 0 / Filters.Overdue, "Today" maps to 1 / Filters.Today) - so in theory just adding to that combo will result in a value of 9 to your new value and the SQL query will be issues as though there is nothing to filter.

[SBarnes]

The other way to do would be in a completely new form that inherits from JiwaFinancials.Jiwa.JiwaApplication.NavigationListUI.NavigationList and write the stored procedure for the grid's data as it works like custom tabs i.e. two results sets one for the schema and one for the data.

You can even add buttons to the ribbon to do stuff if needed like any other form.

[Mike.Sheen]

The other way to do would be in a completely new form that inherits from JiwaFinancials.Jiwa.JiwaApplication.NavigationListUI.NavigationList and write the stored procedure for the grid's data as it works like custom tabs i.e. two results sets one for the schema and one for the data.

You can even add buttons to the ribbon to do stuff if needed like any other form.

I thought Danny was asking about the ToDo tab on various forms - Debtor Maintenance, Inventory Maintenance and so on...

[SBarnes]

I think he was asking about the lister, but you could always do a custom tab to show all as well on the screens.

[DannyC]

I thought Danny was asking about the ToDo tab on various forms - Debtor Maintenance, Inventory Maintenance and so on...

Yep exactly.

Not hugely urgent, just thought it was an oversight although it would've been useful for a little project for a client.

Maybe just chuck it in the next version?

[Mike.Sheen]

Added improvement DEV-9007 to add this.