Moving DueDate to previous working day

[former_member610331]

Hi,

Our SAP Business One is setup to move DueDate of Invoices to next working day considering Saturday, Sunday as weekend days. For example, current SAP Business One moves DueDate to Monday as next working day after weekend. What I am working on is to move DueDate to Friday, i.e. previous working day. Somehow, my FMS query detects whether DueDate is a weekend or not, but it is not able to change DueDate using DateAdd. Any suggestions, advice would be greatly appreciated.

Here is my FMS Query:

Declare @DocDooDate date = '2019-06-23' /* Set DueDate manually for test purpose */
Declare @Day nvarchar(100) = Datename(weekday, @DocDooDate) 
Print @Day 
/* Check if DueDate is a Sunday 日曜日 */ 
IF @Day = '日曜日' 
BEGIN 
Declare @NewDocDooDate date = (Dateadd (day, -2, @DocDooDate)); 
Print @DocDooDate 
END 
/* Check if DueDate is a Saturday 土曜日 */ 
ELSE IF @Day = '土曜日' 
BEGIN 
SET @NewDocDooDate = (Dateadd (day, -1, @DocDooDate)); 
Print @DocDooDate 
END 
ELSE 
/* If DueDate is not a weekend, then do not change DueDate at all */ 
BEGIN 
SET @DocDooDate = @DocDooDate 
END

[former_member610331]

There is a new development since the previous post.

FMS query can now detect whether Duedate is a Saturday or Sunday, and move it to previous week`s Friday successfully. I needed to move Duedate to previous week`s Friday if it falls on a Monday. Here`s the tested version of FMS query.

One last hurdle still remains - clicking magnifer glass to move DueDate. I need Duedate to change automatically once Posting Date is added in A/R Invoice. FMS Query is set to auto-populate based on 'Posting Date' but it does not change itself automatically. Any suggestions or whacky ideas would be appreciated.

FMS Query (tested successfully):
SELECT Case When Datepart (WEEKDAY, $[$12.0.0]) = '1' THEN DateAdd (dd, -2, $[$12.0.0]) When Datepart (WEEKDAY, $[$12.0.0]) = '2' THEN DateAdd (dd, -3, $[$12.0.0]) When Datepart (WEEKDAY, $[$12.0.0]) = '7' THEN DateAdd (dd, -1, $[$12.0.0]) Else $[$12.0.0] End

[former_member610331]

Johan,

Thanks for revised query. It works under SSMS (SQL Server Management Studio) but when converted to FMS query it does not work (i.e. after setting @DocDooDate to $[$12.0.0] within revised query)

On a different note.

• I can change DueDate using another FMS query (SELECT DATEADD(dd, -3, $[$12.0.0]) assigned to DueDate within Sales A/R Invoice. However, I need to click magnifier glass in order to change DueDate. It would be nice if DueDate can be changed automatically once Posting Date is added in Sales A/R Invoice.
• Same FMS query cannot detect whether DueDate is a Saturday or Sunday, so if DueDate falls on a weekday (Mon to Fri), and if anyone clicks on magnifier glass, DueDate will be moved back 3 days (e.g. Thursday --> Monday).

[Johan_H]

Hi,

I am not sure how this query can work at all. You declare @NewDocDooDate if the week day is a sunday, but you also try to set it if the week day is a saturday. If the week day is any other day, you are not setting @NewDocDooDate at all.

Which variable do you actually return?

When you say "...it is not able to change DueDate...", did you test if the due date can be changed with a formatted search to begin with?

If it can be done, you could test this version of your query:

Declare @DocDooDate datetime ='2019-06-23' /*Set DueDate manually for test purpose */
print @DocDooDate
Declare @NewDocDooDate datetime = @DocDooDate
Print @NewDocDooDate
Declare @Day nvarchar(100)= Datename(weekday, @DocDooDate)
Print @Day 
/*Checkif DueDate is a Sunday 日曜日*/
IF @Day ='日曜日'
 BEGIN 
  SET @NewDocDooDate = Dateadd(day, -2, @DocDooDate)
 END
/*Checkif DueDate is a Saturday 土曜日*/
ELSE IF @Day ='土曜日'
 BEGIN
  SET @NewDocDooDate = Dateadd(day, -1, @DocDooDate)
 END
/*If DueDate is not a weekend, then do not change DueDate at all*/
ELSE
 BEGIN
  SET @NewDocDooDate = @DocDooDate 
 END
Print @NewDocDooDate
SELECT @NewDocDooDate

Regards,

Johan