Add/Edit Calendar Sources

Previous  Next |  Direct link to this topic

On this screen you define where a source comes from (via the SQL) and how it should look.

 

clip0232

 

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(T0.DocEntry AS NVARCHAR(254)) + '|' + CAST(T0.LineNumber AS NVARCHAR(254)) AS ObjectKey

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

clip0262

 

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)

 

clip0263

 

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.

Colors

 

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

 

clip0301

 

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