Field
|
Description
|
Name
|
Here you define the name of the Source (NB: Name must be unique)
|
SQL
|
Here you define the SQL that define the source data. Before a source has valid SQL it need to have certain column names included and include certain @Keywords
Practically this mean the you use the SQL Column Alias feature to give each column the correct name...
Example: SELECT T0."DocEntry" AS "PrimaryKey"
Mandatory SQL Columns (aka must be in you SQL)
Column
|
Description
|
StartDate
|
Here you define the "StartDate" of the SAP object (aka a Date Field). This does not mean the Create Date or the Posting Date of an object, but instead what date you wish a calendar entry to "start" on (Example: in Sales Orders you might be interested in showing the orders on their Due Date so you use the DocDueDate field as the "StartDate")
|
Subject
|
Here you define the Subject of the Calendar Entry (aka it's title). You will often use SQL concatenation to build up the title.
|
PrimaryKey
|
Here you defined the Primary Key of the SQL Source (Example on SAP Documents the field is DocEntry field)
(PrimaryKey is not visually shown anywhere on the calendar entry)
Tip: Strictly speaking Primary Key is not mandatory (you can still show calendar entries without it) but it is mandatory for double-click navigation
|
ObjectType
|
Here you define the SAP ObjectType of the SQL Source (Example on an A/R Invoice it is "13")
(ObjectType is not visually shown anywhere on the calendar entry)
Common ObjectTypes:
ObjectType
|
Description
|
23
|
Sales > Quotation
|
17
|
Sales > Order
|
16
|
Sales > Return
|
234000031
|
Sales > Return Request
|
15
|
Sales > Delivery
|
13
|
Sales > Invoice
|
14
|
Sales > Credit Memo
|
203
|
Sales > Down Payment
|
540000006
|
Purchase > Quotation
|
22
|
Purchase > Order
|
21
|
Purchase > Return
|
234000032
|
Purchase > Return Request
|
20
|
Purchase > Delivery
|
18
|
Purchase > Invoice
|
19
|
Purchase > Credit Memo
|
204
|
Purchase > Down Payment
|
33
|
Activity
|
97
|
Opportunity
|
171
|
Employee
|
156
|
Pick List
|
10000044
|
Batch Numbers
|
10000045
|
Serial Numbers
|
191
|
Service Calls
|
202
|
Production Order
|
234000021
|
Project Management
|
190
|
Service Contracts
|
PMX:PICKLISTPROPOSAL
|
Produmex > Sales > Picklist Proposal
|
PMX:PICKLIST
|
Produmex > Sales > Picklist
|
PMX:OSE
|
Produmex > Organizational Structure > [Any SubObject]
|
PMX:MOVEORDER
|
Produmex > Move Order
|
PMX:CONTAINER
|
Produmex > Purchasing > Container Management
|
PMX:INVENTORYLOCKINGINADVANCE
|
Produmex > Sales > Inventory Locking in Advance
|
BEAS:WORKORDER
|
Beas: Work Order
(Require: Beas version 9.2 PL04 or Higher)
|
BEAS:WORKORDERPOS
|
Beas: Work Order Position
(Require: Beas version 9.2 PL04 or Higher)
NB: to add an golden arrow going to a Beas work order position you need to create a combination key from the DocEntry and LineNumber to use the golden arrow on like shown below.
SELECT TOP 1
CAST(DocEntry AS NVARCHAR(254)) + '|' + CAST(LineNumber AS NVARCHAR(254)) AS 'PrimaryKey'
FROM [BEASV_WORKORDER_POS] T0
|
BEAS:WORKORDERBOM
|
Beas: Work Order BOM
(Require: Beas version 9.2 PL04 or Higher)
|
BEAS:ITEM
|
Beas: Item Master Data
(Require: Beas version 9.2 PL04 or Higher)
|
Tip: Strictly speaking ObjectType is not mandatory (you can still show calendar entries without it) but it is mandatory for double-click navigation
Alternative to specifying a SAP ObjectType you can instead reference a Universal Function that should take care of the Navigation (when you double-click a calendar entry)
Example:
We have a source that on Double-click should open B1UP Recurring Invoices. Instead of a normal ObjectType or leaving it out you instead write UF:<uf-code>; in this sample UF:UF-444
By doing this you tell the system that UF-444 need to execute whenever an calendar entry from this source is executed.
In UF-444 you can now use keywords $[CALENDARVALUE.STRING] or $[CALENDARVALUE.NUMBER] to do custom navigation via UF Macro (or any other UF Type for that matter; Example a summary-report, launch external program or similar)
Tip: This is also the way you can Navigate to Beas Manufactoring screens (all you need to know is the correct Beas-script to execute)
|
Mandatory @Keywords (aka must be in you SQL) [Warning: Keywords are case sensitive]
Keyword
|
Description
|
@StartDate
|
This keyword represent the start date of the current Calendar view. You should use the @Keyword in you SQL WHERE condition to limit the data that make sense to should in the visual date range
|
@EndDate
|
This keyword represent the end date of the current Calendar view. You should use the @Keyword in you SQL WHERE condition to limit the data that make sense to should in the visual date range
|
Optional SQL Columns (aka can be used but not mandatory)
Column
|
Description
|
SecondaryKey
|
In SAP Business One certain objects you can show can have a secondary key (Example Employee Absense or Multi-schedule Service Calls). If you have such a secondary key you can name is "SecondaryKey" for the system to pick it up.
(SecondaryKey is not visually shown anywhere on the calendar entry)
NB: This field is mandatory if you have a source that use Drag/Drop/Resize action of Type Employee: Absence or Education
NB: This field is optional if you have a source that use Drag/Drop/Resize action of Type Service Call: Change Date and Time on Multi Schedule Mode (aka get the schedule data from table SCL6)
|
StartTime
|
Most SAP Object have a Date concept, but only a few (most notable "Activity" and "Service Call") have a start-time (Example: A Sales Order only have a date it is due, not a specific time of the day)
If your object has a Start-time you can optionally include it so the Calendar can show it on a specific time (else it will just be shown as an "All Day" entry)
NB: Time fields in SAP are integer value field in the database (aka 800 in DB = 08:00 (8am) and 2200 = 22:00 (10pm)). The calendar widget assume your Starttime is the same you you need to provide it as an integer
Tip: It is possible for advanced scenarios to have UDF StartTimes. See this video how: https://youtu.be/va5RXcoEHik
|
EndDate
|
Given that you have a StartDate that are mandatory it might sound a bit strange that EndDate is optional, but this is done due to the fact that many SAP Object only have a single date, so the system assume that if you do not give an EndDate, the it is the same as you StartDate.
Should you however have an object that have a multi-day concept (example a start and end-date of a Production) you can define the EndDate here.
Tip: It is possible for advanced scenarios to have UDF EndDates. See this video how: https://youtu.be/va5RXcoEHik
|
EndTime
|
Most SAP Object have a Date concept, but only a few (most notable "Activity" and "Service Call") have a end-time (Example: A Sales Order only have a date it is due, not a specific time of the day)
If your object has an End-time you can optionally include it so the Calendar can show it on a specific time (else it will just be shown as an "All Day" entry)
NB: Time fields in SAP are integer value field in the database (aka 800 in DB = 08:00 (8am) and 2200 = 22:00 (10pm)). The calendar widget assume your Endtime is the same you you need to provide it as an integer
Tip: It is possible for advanced scenarios to have UDF EndTimes. See this video how: https://youtu.be/va5RXcoEHik
|
Location
|
If you have a column with the name "Location" this will be used in parentheses after the title. Example: <title> (<location>)
|
Details
|
If you have a cloumn with the name "Details" this will be used as body-text of the Calendar Entry
|
Resource
|
If you have a column with the name "Resource", you add a Resource SQL and the source is being used in a calendar with a single source. This will be the Resource Id of the Appointment
See a video how the resource System works here: https://youtu.be/30js2pZWWdc
|
ForeColor
|
On the Calendar Source you have the option to set a fixed fore-color (text), but alternative you can make the text-color dynamic by providing a SQL Column called 'ForeColor' with the name of the color you wish to use.
This is handy in situations where you example want to highlight an object (Important or Special in some way) or if you object have some sort of status and you wish to use color-coding to highlight this.
Example: In a Service call you could add the following to the Service Call SQL to control the text to be Red or Black depending on status
SELECT
...
CASE T0.Status
WHEN -2 THEN 'Red' --Pending
ELSE 'Black' --Open
END AS 'ForeColor',
...
FROM ...
Below shows the supported color-names.
|
BackColor
|
Like ForeColor but controls the background color
Example: In a Service call you could add the following to the Service Call SQL to control the background to be Red if the Service Call is pending, else Green
SELECT
...
CASE T0.Status
WHEN -2 THEN 'Red' --Pending
ELSE 'Green' --Open
END AS 'BackColor',
...
FROM ...
|
Image
|
On the Calendar Source you have the option to set a fixed Image, but alternative you can make the Image dynamic by providing a SQL Column called 'Image' with the path to a png image (default images are 16x16 pixels but you can reference bigger if needed) you wish to use.
Example:
...
CASE T0.Status
WHEN -2 THEN '\\myServer\B1_SHR\Images\run.png'
ELSE '\\myServer\B1_SHR\Images\stop.png'
END AS 'Image',
...
|
Optional @Keywords (aka can be used but not mandatory) [Warning: Keywords are case sensitive]
Keyword
|
Description
|
@UserId
|
This Keyword can be replaced with current logged in user's UserId (Field OUSR.INTERNAL_K in the database)
|
@UserName
|
This Keyword can be replaced with current logged in user's Username (Field OUSR.USER_CODE in the database)
|
@EmployeeId
|
This Keyword can be replaced with current logged in user's EmployeeId (Field OHEM.empID in the database for the Employee that is linked to current User)
|
@SalesEmployeeId
|
This Keyword can be replaced with current logged in user's SalesEmployeeId (Field OHEM.salesPrson in the database for the Employee that is linked to current User)
|
Tip: You can in Calendars with source linked to Universal Functions, use the Dynamic Syntax to make a calendar context sensitive
|
Tool Tip SQL
|
ToolTip SQL is optional. Simply write a SQL Sentence with a @PrimaryKey where Conditional. We will then turn each colum and its value into a tooltip
Example: The following SQL on an Invoice Source
SELECT CardCode AS 'Customer', CardName AS 'Customer Name', Address AS 'Bill To Address' FROM OINV WHERE DocEntry = @PrimaryKey
Will on an invoice result in a tooltip that show the customer code, customer name and the bill to address
|
Resource SQL
|
See a video how the resource System works here: https://youtu.be/30js2pZWWdc
In a calendar with a single calendar source it is possible to provide a Resource SQL so the calendar will split the resources into a Resource view
The resources shown at the top are controlled by this SQL.
The SQL have 2 Mandatory Columns
Column
|
Description
|
Id
|
The Id of the resource. This should match the data SQL "Resource" column. You can make this any value if your Resource Switch Action is set to none. Else it needs to be one of the formats below:
Action
|
Description
|
Service Call: Change Handled By
|
Since a Service Call can be handle by either a User or a Queue you need to prefix the resource Id with a "U_<id>" (User), "E_<id>" (Technician in multi-schedule mode) or a "Q_<id>" (Queue)
Here is an example on how the resource SQL is in most Service Calls:
SELECT 'U_'+CAST(INTERNAL_K AS NVARCHAR(100)) AS 'Id', U_NAME AS 'Name' FROM OUSR WHERE GROUPS <> 99 AND INTERNAL_K IN (SELECT assignee FROM OSCL WHERE status<>-1) UNION ALL SELECT 'Q_'+queueID AS 'ID', queueID AS 'Name' FROM OQUE
|
Activity: Assigned To
|
Since an Activity can be handle by either a User, an Employee or a Recipient List you need to prefix the resource Id with a "U_<id>" (User) or a "E_<id>" (Employee) or "R_<id>" (Recipient List)
Here is an example on how the resource SQL is for Activity Users:
SELECT 'U_'+CAST(INTERNAL_K AS NVARCHAR(100)) AS 'Id', U_NAME AS 'Name' FROM OUSR WHERE GROUPS <>99 AND INTERNAL_K IN(SELECT AttendUser FROM OCLG WHERE Closed = 'N')
|
|
Name
|
Then name of the resource. You can give any value here
|
|
ForeColor (Text)
|
Here you define the text-color of the Calendar Entry
|
BackColor (Background)
|
Here you define the background-color of the Calendar Entry
|
Image
|
Here you optionally define an image that you wish to represent the calendar entry
|
Drag/Drop Action
|
Here you optional define a Drag/Drop action for the Source (aka what happen when you drag/drop the Calendar Entry around to new dates)
Action
|
Description
|
None
|
Drag and Drop is disabled (do nothing)
|
Activity: Change Date and Time
|
Change the start date/time/duration of an activity (Require that ObjectType is 33 of the source)
|
Document: Change Due Date
|
Change the Due Date of a Sales/Purchase Document (Require that ObjectType is one of the supported Document Types if the source)
|
Opportunity: Change Predicted Close Date
|
Change the Predicted Close Date of an Opportunity (Require that ObjectType is 97 of the source)
|
Employee: Change Absence From/To
|
Change an employees Absence From / To Date (handy if an Employee is sick one more day - Require that ObjectType is 171 of the source and SecondaryKey is defined)
|
Employee: Change Education From/To
|
Change an employees Education From / To Date (handy if an Employee's education is rescheduled - Require that ObjectType is 171 of the source and SecondaryKey is defined)
|
Picklist: Pick Date
|
Change a Pick-list's Pick Date (Require that ObjectType is 156 of the source)
|
Batch: Change Expire Date
|
Change a Batch Expire Date (Require that ObjectType is 10000044 of the source)
|
Serial Number: Change Expire Date
|
Change a Serial Number Expire Date (Require that ObjectType is 10000045 of the source)
|
Production: Change Start/Due Date
|
Change the start/due date of an production order (Require that ObjectType is 202 of the source)
|
Service Call: Change Date and Time
|
Change the start date/time/duration of a Service Call (Require that ObjectType is 191 of the source)
|
|
Resize Action
|
Here you optional define a Resize action for the Source (aka what happen when you resize the Calendar Entry around to new dates)
Action
|
Description
|
None
|
Resize is disabled (do nothing)
|
Activity: Change Date and Time
|
Change the start date/time/duration of an activity (Require that ObjectType is 33 of the source)
|
Employee: Change Absence From/To
|
Change an employees Absence From / To Date (handy if an Employee is sick one more day - Require that ObjectType is 171 of the source and SecondaryKey is defined)
|
Employee: Change Education From/To
|
Change an employees Education From / To Date (handy if an Employee's education is rescheduled - Require that ObjectType is 171 of the source and SecondaryKey is defined)
|
Production: Change Start/Due Date
|
Change the start/due date of an production order + UDFs linked to Start/EndTime in UDFs. See this video for more info: https://youtu.be/va5RXcoEHik (Require that ObjectType is 202 of the source)
|
Service Call: Change Date and Time
|
Change the start date/time/duration of a Service Call (Require that ObjectType is 191 of the source)
|
Change Document Date and Time (Require UDFs for Time Fields)
|
Change the Due Date of a Sales/Purchase Document + UDFs linked to End Date and Start/EndTime in UDFs. See this video for more info: https://youtu.be/va5RXcoEHik
|
Change Opportunity Date and Time (Require UDFs for Time Fields)
|
Change the Predicted Close Date of an Opportunity + UDFs linked to End Date and Start/EndTime in UDFs. See this video for more info: https://youtu.be/va5RXcoEHik
|
|
Resource Switch Action
|
Here you optional define a Resource Switch action for the Source (aka what happen when you move the Calendar Entry from one resource to another while the calendar is in resource mode (aka 1 source and Resource SQL is defined))
Action
|
Description
|
None
|
Resource switch is disabled (do nothing)
|
Activity: Change Assigned To
|
Change the Assigned to (User, Employee or Recipient List)
|
Service Call: Change Handle By
|
Change the Handled By (User of Queue)
|
|
Description
|
Here you can write an optional description (information only)
|
Users
|
Here you have the option via the edit text and the radio buttons to make your configurations user-specific (aka, who should have access to it). You simply enter the usernames of the users that should be included or excluded.
|
Custom SQL Filters
|
This button opens the Filters configuration form
|
Restore this Calendar Source
|
This option will restore the Calendar Source and the associated default filters to their original state
NB: this does not work for Calendars Sources there does not come out of the box
|
