Create Window based on view

Overview

Normally you would base a window on a table so you can alter the data displayed in it.
In this guide you see how the window feature is used to retrieve data that you then filter, view and export.

View

create a database view showing the data you want to see

add required default columns

1000000 as ad_client_id, --id of your client. usually 1000000
1000000 as ad_org_id, --id of your organisation. usually 1000000
now() as created, --or a fixed timestamp
100 as createdby, --or any other ad_user_id
now() as updated, --or a fixed timestamp
100 as updatedby, --or any other ad_user_id
'Y' as isactive,

add more columns
If you want to add more columns like a partner name, you want to use the name of the partner ID instead and let metasfresh resolve to a value and name later in the window:

c_bpartner_id, --instead of partner_name, etc.

add unique id to each row

SELECT row_number() OVER () AS <viewname>+"_id" --or any other id

for example: SELECT row_number() OVER () AS rv_invoicingforecast_id

Hint: Don’t use postgres sequences. They can spill over and might not work.

Application Dictionary Configuration

Logon the Java Client with role System Administrator

Create a new Application Dictionary table (“AD Table”) in the Window “Table and Columns”

tick “view” to indicate its not a real table

name the AD Table exactly as your view

run the process “Create columns from database” to create the AD columns

the AD column holding your unique id needs to be named <viewname>_ID

Hint: If you dont follow the convention your view will not work

also tick “key column” on that AD column holding your unique id

tick the AD columns as search columns that you want to use for filtering the data later

Create a AD Window

link it to the AD Table you created

run the process “create fields from columns”

run the process “create default UI elements”

create a menu node for your window

add the window to your menu tree

Usage

run the process “update role access” to grant your automatic roles access to the new window

log on with admin and test it

open the window

filter + select records and then use the process “download selected” to export them in xls format