By the end of this chapter you will be able to:
- Use the switch() function
- Use the forEach() function
Additional assistance can be found in Expression Basics.
Using switch() to apply multiple tests
- Create a new model,
Attribute Scripting
- Drag the datasource
CRM
onto this model. - From the datasource hover menu use the
Table option to display all available tables. - Find the table
SOURCE_BANK_ACCOUNT_TRANS,
right-click and select create a collector. - Create a table from this database collector, remember to use the
Create New Table button in the hover menu so that the table attributes are automatically configured. - Add an attribute to this table to hold a description of the transaction type:
- Name:
TransactionTypeDesc
Expression: work out the transaction type description from TRANSACTION_TYPE
, using a switch()
statement, according to the rules below – Search the help and use the examples in the help for switch()
to get started.
- Type,
switch(
, and the inline help will appear. At the bottom of this popup is a link to its help page.
TRANSACTION_TYPE | Description | Example Logic |
1 | "Direct Debit" | The logic being constructed will be, where the attribute TRANSACTION_TYPE is 1 set the value for the description to be "Direct Debit". |
2 | "Standing Order" |
|
3 | "ePayment" |
|
4 | "Cheque" |
|
Any other value | "Unknown" | This will be the last value with no specified condition. If the value held in the attribute TRANSATION_TYPE is not cover in the logic above this will be the value set. |
- Run the table and check that your translation of transaction type to transaction type description is correct.
Using forEach() to handle lists
- Create an aggregate table, with name
Bank Account Summary
- Add a pipe from the table you created in the previous exercise (this should be called
SOURCE_BANK_ACCOUNT_TRANS
) to Bank Account Summary
- Add to the pipe the grouping:
ACCOUNT_NUM
- Drag the attribute
ACCOUNT_NUM
into Bank Account Summary
from either the pipe or the input table. - Add an attribute:
- Name:
TotalPosTrans
- Type: Float
- Expression: using a
forEach()
loop, add up the total of all the positive transactions, and store it in this attribute.
- Run
Bank Account Summary
to test this new attribute. - When this is working, update
TotalPosTrans
to also calculate the total of negative transactions, and the total of all transactions – and hold them in local variables ($variables).- See Using Variables for additional assistance.
- Add two further attributes –
TotalNegTrans
and TotalTrans
- use these to store the values (in your local variables) of all negative transactions and all transactions that you calculated in TotalPosTrans
- Run
Bank Account Summary
again to test the new attributes.