OTRS user already exists

When adding a customer account to OTRS 3.0.11, you may encounter the "user already exists!" message but the customer account isn't visible in the Customer Management page. The problem is that the page doesn't indicate that you may have more than one page of customer accounts and there is no way to see more pages (i.e. no "Next" or "1, 2, 3 ..." links). The workaround is to restrict the search, e.g. if you are looking for "johnsmith", enter "john" or "smith" in the Search field.

Workaround to conditionally format for a range of cells in Excel

You can format cells in one column based on the value of cell in the same row in another column in Excel using conditional formatting but you have to workaround some quirks in the Excel Conditional Formatting Rules Manager (CFRM) user interface to get that result. Say that you want to format cells in column A based on the value of a cell in column B for the same row, i.e. A1 is depends on B1, A2 on B2, A3 on B3, etc. The steps for creating a conditional formatting rule for column A are as follows:

1. In Excel, select menu item Home, Conditional Formatting, Manage Rules.
2. In the CFRM dialog, press the New Rule button.
3. In the New Formatting Rule (NFR) dialog, select Use a formula to determine which cells to format.
4. In Format values where this formula is true, enter B1.
5. Set the format required.
6. Press the OK to close the NFR dialog.
7. Back in the CFRM dialog, you should see your new rule. In the Applies to field, enter $A:$A to use this rule for Column A. At this stage, if you press the OK button, the conditional format does not work for Column A. Use the following steps to workaround the
8. Press the Edit Rule button to display the Edit Formatting Rule (EFR) dialog.
9. In Format values where this formula is true, change ="B1" to =B1 (i.e. remove the double quotes) then press the button.
10. In the CFRM dialog, press the OK button.

After applying these steps, when a cell in Column B is TRUE, Excel applies the specified formatting to a cell in the same row in Column A.

Not automatically delete mail in Lotus Notes

After being spammed by "out of office" messages, I wanted to add a Lotus Notes (LN) rule to move these types of messages from my Inbox into the Trash folder. However, LN doesn't provide an action that moves messages to the Trash folder. Instead, the action that sort-of fits my requirement is to "Delete (don't accept message)" which I think means that LN will just delete these messages, sight unseen. I prefer to review my mail before permanently deleting them so my workaround is to create an action to "Move to Junk" then review the Junk folder before deleting messages.

Ref: Filtering new mail using rules

Cannot redo changes in Lotus Notes editor

There is no redo feature in the Lotus Notes 8.5 (LN) editor, so don't use the undo feature expecting to be able to redo your changes. I got caught out when I undid one change too many while editing a message. There is an Enhancement request: Redo feature in Lotus Notes client from 2009.

BI+ Report User Point of View

Note to myself on how to add a dimension to the user point-of-view (POV) in Hyperion BI+.

1. Open the report using Financial Reporting Studio (FRS).
2. Click on the POV in a grid. FRS should open the Select Members dialog.
3. In Select Members, select the tab for the dimension you require.
4. In the dimension tab, set the member to be "User Point of View for [dimension]" (go to the bottom of the list).
5. Press the OK button to close the Select Members dialog.

The required dimension should now be in the user POV at the top of the report.

HFM Extended Analytics application name and table prefix

When we ran an Extended Analytics (EA) template in a new HFM application, the job halted with this error:

ERROR 2: Error during Extended Analytics export (Attention:  The metadata that currently exists for the Prefix %0 was extracted from a different Financial Management Application.  Extracted App: %1   Current App:  %2. You must use the Create option to overwrite this extract or use a different Prefix.)

According to the HFM Administrator's Guide, running an EA template would create a set of tables with the same prefix (e.g. DEMO_FACT, DEMO_YEAR) and master table called HFM_EA_EXTRACT. If you look in HFM_EA_EXTRACT, you should find an association between the application name and the tables with the same prefix. For this problem, my fix is to change the application name in HFM_EA_EXTRACT. The "Create" button in the HFM EA page didn't do anything and I didn't want to change the template's prefix because the applications that use the data would also have to be reconfigured.

List users in a Windows local group

Note to myself: use net localgroup [groupname] to get a list of users in a Windows server's local group (e.g. Remote Desktop Users). Refer: Net localgroup.

Excel find first word without IFERROR() or IF(ISERROR())

In Excel, to find the first word in a cell, find the position of first space then return all characters up to but excluding the space: =LEFT(A1,FIND(" ",A1)-1).

The formula above fails with #VALUE if the string in A1 doesn't have a space or the cell is empty. A Excel 2007 solution is to wrap the entire formula in IFERROR to return a default value: =IFERROR(LEFT(A1,FIND(" ",A1)-1),A1). Excel 2003 doesn't have IFERROR, so the formula is much longer with the same formula for the condition and output of the IF formula: =IF(ISERROR(LEFT(A1,(FIND(" ",A1)))),A1,LEFT(A1,(FIND(" ",A1)-1))).

A shorter solution for Excel 2003 onwards is to append a space to the cell value in the FIND formula so that a default value can always be returned: =LEFT(A1,FIND(" ",A1&" ")-1). Note: Test if it works for OpenOffice Calc.