Sometimes you want to select data from a data source depending on a context. E.g. you want to select all invoices for a currently selected business partner in a sequential workflow or you need to print an actual invoice by its Id using a report.
In order to be able to filter data using variable values, you have to define the Parameters. You create a parameter by adding a Parameter element under an entity. Parameter elements give a list of all the possible parameters that you can use. The parameter can be then used (referenced) in a Function Call Field especially inside an entity filter.
Please note that when using the parameters in the data structure filter set the data structure entity name will be added to each parameter. E.g. for the entity Invoice with the parameter parId the resulting parameter name that will be passed to the data service will be Invoice_parId.
How Parameters Are Used In The Database Engine
We use parameters because they can have a great impact on the performance as multiple SQL statements can be reused when the parameters have been set correctly. For more information see http://www.mssqltips.com/sqlservertip/2981/using-parameters-for-sql-server-queries-and-stored-procedures/.
Attributes
AllowNulls | True if this parameter accepts empty values passed to it. If set to False and a query is started without this parameter passed in, you will get a runtime error. |
DataType | The Data type of this parameter. |
DataLength | In case of a String data type, maximum length of the text that can be passed to the parameter. |
Example
You want to retrieve all invoices by a business partner.
-
Add a
parBusinessPartnerId
parameter to theInvoice
entity. -
Create a Filter by calling the function Equal with the following operands:
Left Field Reference: refBusinessPartnerId
Right Parameter Reference: parBusinessPartnerId
-
Use this filter in a data structure filter set. When using this filter, you will have to pass a customer Id to the parameter Invoice_parBusinessPartnerId. Only invoices for this customer will be returned.
This will be the resulting SQL statement sent to the database server (Microsoft SQL Server syntax):
SELECT Id, Number, Price, ...
FROM Invoice
WHERE refBusinessPartnerId = @Invoice_parBusinessPartnerId
Note that it will be always the same regardless the value passed to the parameter. This way the query can be easily cached both by the ORIGAM runtime and the database server.
System Parameters
There are some predefined parameters associated with the with the IAsapEntity2. These parameters have a special functionality. Actually you can define these parameters on any entity (even one not inherited from IAsapEntity2) since the system parameters are evaluated by their names. All parameters with these names will get a special treatment.
You do not have to pass any values to these parameters because their values will be automatically assigned by the system on every usage.
parCurrentDate | Current date (without a time part). |
parCurrentDateLastMinute | Current date + 23:59:59. This is useful e.g. for creating filters to get everything for today (BETWEEN parCurrentDate AND parCurrentDateLastMinute ). |
parCurrentDateTime | Current date and time. |
parCurrentUserBusinessUnitId | Current user’s business unit id. |
parCurrentUserId | Current users’s id. |
parCurrentUserOrganizationId | Current user’s organization id. |
parCurrentUserResourceId | Current user’s resource id. |