Monday, May 6, 2019

Search that will show the transactions in a specific account separated by each transaction currency.

Search that will show the transactions in a specific account separated by each transaction currency.

Sample Scenario:

An expense account is set up to be used in multiple subsidiaries. Transactions are entered for this account in different currencies. If the user views the account register, the amount shown is converted to the base currency. If the user would like to have a list of transactions for this account separated by each currency used, the user may try to create the search below:

* If for example the account is used with Philippine Peso and ALB currency transactions, the search may use the following formula:

1. Navigate to Lists > Search > Saved Searches > New.
2. Select Transaction search type.
3. Enter a Search Title.
4. Under Criteria tab > Standard sub tab, set the following:

- Account > then set the specific account.
- Date > set the date range of the transactions.

5. Under Results tab > Columns sub tab, set the following:

- Number (to show the transaction number)
- Type (to show the transaction type)
- Formula (Numeric) > then set the formula below:

case when {currency} = 'Philippine Peso' then {fxamount} else 0 end

*Set Custom Label for this result column. Example: Custom Label = PHP

- Formula (Numeric) > then set the formula below:

case when {currency} = 'ALB' then {fxamount} else 0 end

*Set Custom Label for this result column. Example: Custom Label = ALB

**Amend the formula depending on the currency that is used for the account. For example, from the first formula, change 'Philippine Peso' to 'USD'. Make sure to enter the currency name.
**Add other fields that is needed on the result.

6. Click Preview or Save & Run.

The result should show transactions in Philippine Peso in PHP column while ALB transactions in ALB column.

No comments:

Post a Comment