ORA-00933 when editing a Apex report

Have you encountered this error before in working with Apex? I do regularly wen editing reports.
At first I have created a nice report. After getting the query right I make some columns invisible, change the header texts and do some formatting.
Then I run the report and it looks nice. No errors on the SQL.

Then I apply a few changes and suddenly the report will not show up anymore. Instead I get this ugly error message:

failed to parse SQL query:
ORA-00933: SQL command not properly ended

What happend? It took me some time to figure it out. In my case there was an order by in the report query. This means that none of the report columns may be sortable.
At first this was the case. But one column was changed from Hidden to Plain Text. And in this change the Sortable attribute was set to Yes:

And Apex gets confused when a report query has an order by and an item is also sortable. So the query Apex creates has an incorrect syntax, hence the error message.

Setting the sortable attribute of the item to No immediately solves the problem. The report executes again without any error message.

Happy Apexing...

Adding a bit of Excel to tabular forms

When speaking to users they often ask me whether the editing of data can be like Excel. Using tabular forms is a long way from using Excel. It is hard to operate the tabular form just with the keyboard and copying of values needs to be done in a number of keystrokes.

With this in my mind I created a JavaScript function to perform a (single) copyDown action. The value of an item is copied to the item below and the focus is set to that item. When this can be executed using a key(combination) the user is able to do a fast copy down action.
After creating this function I realized it also can be used to move up and down in the tabular form using the arrow keys. And I decided to create a Plug-In around it.

The Plug-In can be called from a Page Load dynamic action. No settings are needed.
The actions will execute on any text, date, and select item in a tabular form. Checkboxes are not yet supported.
The available function keys are:

• [Up] move up to previous row
• [Down] move down to next row
• [Ctrl] + [Down] copy content of current cell to cell below and set focus to this cell
• [Ctrl] + [Up] copy content of current cell to cell above and set focus to this cell
• [Ctrl] + [Shift] + [C] copy content of current cell to cell below and set focus to this cell ( needed for Select items because they have another function for [Down] )

I could have included the [Right] and [Left] arrow keys but then the editing of the cell content would be affected. These keys cannot be used anymore to navigate within the content. So the keys [Tab] and [Shift][Tab] can be used to go to the next and previous item in the row.

I think many of you can make your users even happier with their Apex application when implementing this Plug-In in your application.

You can see and experience the Plug-In at this demo page: http://www.speech2form.com/ords/f?p=141:copydown

As always this plugin can be downloaded from apex.world.

One last question: does anyone know how to access the state of checkboxes in an Universal Theme tabular form?

Happy Apexing

Copy hidden items to clipboard plugin

At the beginning of 2017 I published a plugin to copy the content of Apex items to the clipboard. I use it on a regular basis in my applications but always for visible elements.
Yesterday I got a comment that the plugin does not handle hidden Apex items correctly. So I set out to create a new version of the plug-in.

In the new version of the plug-in the hidden Apex items are handled correctly and also added support for JQuery selectors.

The new version 1.1 now supports Apex items of type Hidden. This means you can build a string in  hidden item and provide the user with a button to copy the content to the clipboard. Just create a button, add a Click dynamic action to it and supply as action Copy to clipboard [Plug-In]:

In this example the hidden item is called P800_HIDDEN_1.

For the developers that feel at home with JQuery there is the option to reference to DOM elements using JQuery selectors. On the example page you will see a button Introduction to clipboard (JQuery selector) in the header of the Introduction. This button fires the following dynamic action:

The Introduction region has got a static ID INTRO and if you open the inspector you will see that the introduction text is contained in a div element with class t-Region-body. So if you click on the button the text will be copied to the clipboard.
NB Only the first element from the JQuery selection will be used

You can download the new version on http://apex.world

Happy Apexing