Alert on UDF B1

[MarkOenema]

Dear all,

I made a UDF in the item master data to register BBD on products. Now I want to set an alert 2 months before the expiration date. SAP Business One 

Who can help me?

Regards Mark Oenema

[Johan_Hakkesteegt]

Hi Mark,

That should not be too difficult.

A few questions:

• Assuming that your UDF contains the expiration date, is the UDF of data type 'Date'? If not, then it should be, if it is, then good.
• Do you want to get an alert every day for items where the date in the UDF is:
  • Exactly and only in 2 months? If it is 1 month and 29 days, you no longer get an alert for this item. So if you miss the alert, it is too bad.
  • In 2 months or earlier? Your list will get longer and longer and longer every day. Have you thought about how to determine that an item no longer needs to be on the list?

Regards,

Johan

[MarkOenema]

Hi John,

[MarkOenema]

Hi John,

UDF is indeed set as date.

About the alert, I prefer to receive an e-mail so I can push those items to our clients.
The alert has to be 'if date will arrive in 2 months' 

Please advise

Thanks in advance regards Mark Oenema

[Johan_Hakkesteegt]

Hi @MarkOenema,

"if date will arrive in 2 months"

I am not entirely sure what you mean with this. Could you please explain in other words, or even in dutch perhaps?

Regards,

Johan

[MarkOenema]

Hi Johan, That I will get a warning 60 before expire date, so I can do specials for my customers.

[Johan_Hakkesteegt]

Hi @MarkOenema,

Please try this query for the alert:

SELECT i.ItemCode, i.ItemName, i.U_BestByDate
FROM OITM i
WHERE DATEDIFF(day, cast(floor(cast(GETDATE() as float)) as datetime), i.U_BestByDate) = 20

You will have to replace U_BestByDate (both in the first and last line) with the actual name of your UDF.

Regards,

Johan

[MarkOenema]

Hi John, I tried the query and changed 20 to 60 days. Now I put an alert, so tomorrow we will see if works. Thanks in advance