There is a need to get the difference in days between the posting date of a document and today's date. This needs to be displayed in a document.
This can be achieved with a Formatted Search and a User Defined Field on title level. This note describes how to customize SAP Business One to execute this in version 2005A SP01 and 2007A.
How to create a User Defined Field in version 2005A SP01 and 2007A.
Step-by-Step description on how to create a User Defined Field (UDF), in the title level of a Marketing Document and then display it.
- For version 2005A SP01, in the top menu choose:
Tools -> User-Defined Fields -> Manage User Fields
- For version 2007A the path is:
Tools -> Customisation Tools -> User-Defined Tables - Management
a) In the window 'Manage User Fields' expand the title 'Marketing Documents'.
b) Highlight 'Title'.
c) At the bottom of the window click 'Add'.
d) In the window 'Field Data' give the field a Title and a Description.
e) Click 'Add'.
f) Expand menu point 'Title', the newly created field is there.
g) To display the UDF open up the marketing document.
h) Press Ctrl+Shift+U, alternatively use the top menu:
View -> User-Defined Fields
Queries needed for this formatted search.
In this note there are two queries giving slightly different results, either one of them can be used. The first will give the difference in days including today, the second excluding today. Which one to use is user preference.
Example.
a) Today's date is the 22nd.
b) The posting date of the document is the 15th.
Query 1 will display the difference as 8 days.
Query 2 will display the difference as 7 days.
Query 1:
SELECT
Convert(numeric, GetDate()) - convert(numeric, T0.DocDate )
as 'Days After Posting Date'
FROM ORDR T0
Where T0.DocNum= $[$-8.1.Number]
Query 2:
declare @d datetime
set @d = (select t0.docdate from ordr t0 where t0.docnum = $[$-8.1.0])
select cast(datediff(dd, @d, getdate()) as nvarchar(10))
The above queries are specifically written for the document 'Sales Order'. To use the queries for other documents the reference table ORDR needs to be replaced with the corresponding table for the document. At the bottom of this note please find the table names for the other documents.
How to save a query :
The query that's chosen needs to be saved in the system. To do so please follow the step-by-step description below:
- In 2005A SP01 Go to
Reports -> Query Generator -> Click on 'Execute' without making any selection.
- In 2007A Go to the top menu
Tools -> Queries -> Query Generator -> Click on 'Execute' without making any selection.
a) In the 'Queries' window click on the pencil icon in the upper left area to activate the query structure area. The area appears grey when blocked, white when ready and yellow when active.
b) Copy and paste the query from above into the window, replacing the 'SELECT *' that is present by default.
c) Click on the button 'Save', then, in the 'Save Query' window, enter an appropriate name for the query and select the appropriate category. Then click on 'Save'.
d) Click on 'Cancel' in the window 'Queries' and 'Close' in the window 'Query Generator'.
How to define a Formatted Search :
In the system now there is an UDF and a query. The query now needs to be attached as a formatted search to the UDF in the marketing document.
For this please follow the steps below:
a) Open the marketing document and display the UDF, either by pressing Ctrl+Shift+U, or using the top menu: View -> User-Defined Fields
b) Click in the UDF to make it active and press Alt+Shift+F2, alternatively use the top menu:
Tools -> Search Function -> Define.
c) In the window 'Define Formatted Search' select 'Search by saved Query'.
d) A grey field is displayed in the window, double click this field to display the window 'Query Manager'.
e) In the 'Query Manager' select the query that was previously saved and click 'Ok'.
f) Back in the window 'Define Formatted Search' click 'Update'
g) The window is closed and the formatted search has been saved.
h) To use the added function press shift+F2.
Please note that the posting date needs to be older than today's date for the formatted search to give any result.
Documents and corresponding table:
- Sales Documents:
OQUT - Sales Quotation
ODLN - Delivery
ORDN - Sales Return
OINV - AR Invoice, AR Invoice + Payment, AR Reserved Invoice
ORIN - AR Credit Note
- Purchase Documents:
OPOR - Purchase Order
OPDN - Goods Receipt PO
OPCH - AP Invoice, AP Reserve Invoice
ORPC - AP Credit Note