Hi everyone. I'm relatively new to Application Express, having only developed two applications so far. My current application is a bit more complex than the previous ones and has some challenging requirements I need to overcome. One of the screens is required to show a list of projects with particular fields, and then alongside each a rolling 18 month view of capacity figures. The way I and my work colleague have decided to tackle this is to store the project details in one table and then link across to another table that stores the capacity figures. Each row in this capacity table contains the Project ID which links back to a record in the main table, a field identifying which month the record is for, and then the value. My colleague has then built a complex SQL statement which brings back a rolling 18 months of figures from this table based on the current system date. However he says that he cannot find a way to bring back dynamic field names for the month values. Currently the SQL returns the required project fields and then alongside them fields named Month1 to Month18.
I wish the main screen to be an interactive report screen which will lead to an amend record screen via the usual method. I can of course link this SQL to an interactive report, but the months will be displayed with titles Month1 to Month18. What I want the screen to display is the relevant months and years in MMM YY format e.g. if ran today it would show Aug 14, Sep 14, Oct 14 etc. If ran next month it would show Sep 14, Oct 14, Nov 14 etc.
The other issue is of course the amend record screen. I need the dynamic field names to appear on this too, and for the link between the two screens to work in the usual manner.
Does anyone know how to do this without the need for complex Java code? Is there an automated method for doing rolling months in APEX? If not I am surprised, as I feel it is something which would often be required for reporting. If you know of a method by which rolling months can be used with an interactive report which is totally different to the way we currently have the tables set up, I am more than happy to change whatever is necessary to get it to work. Please remember that I am still new to APEX so I will need a detailed step-by-step solution to this issue that I can follow.
Thanks in advance