For example, if we have to find account names separately for journal entry transactions do the following,
Goto Reports -> Saved Search -> New, Select Transaction
- Choose the type of transaction as – Journal under criteria subtab
- Under results subtab,
- For parent account, Select Formula(Text) as field and in formula section –
- CASE WHEN INSTR({account.name},’ : ‘) != 0 THEN RTRIM(REGEXP_SUBSTR({account.name},'[^:]*’)) ELSE ‘ ‘ END
- Since the format is as Parent : Child, this formula checks whether the account name has ‘:’ in it. If so, then the regular expression gets the string before ‘:’ and extracts the same.
- For child account, Select Account join fields with Localized Name
- By using this formula, all accounts which doesn’t have parent-child account relationships will be displayed in ‘Account Name’. And ‘Account Group’ column remains blank
Results
