Add Flexibility to User Defined CSV Column Heading by Supporting Variables
Currently, the User Defined CSV Export column heading is predefined and is static. Sometimes a CSV layout is defined to compare current month, last month and prior month. For example, if current month is December, last month is November, prior month is October. In the layout, you can specify the month as "CP" = Current Period, LP=Last Period, PP=Prior Period. But You can't put December, November and October as the column headings, since next month the column headings will change. Therefore, it is proposed we support the following variables in the column heading:
* <MON>: The three digits month like Jan, Feb, Mar...etc.
* <MONTH>: The full month name like January, February....etc.
* <MM>: Two digits month like 01, 02...,11, 12.
* <YR>: The two digit year.
* <YEAR>: The 4 digit year.
* <TODAY>: System date in MM/DD/YY format.
These variables must be in upper case and can be combined with other constant or variables. If the column heading has any characters other than the above variables, the variable will be replaced by the appropriate "To" period month or year value.
If the "From" and "To" dates are the same, only one value will appear in the column heading. Ex: "<MONTH>, <YEAR>" would become November, 2020
If the "From" and "To dates are not the same, the column heading will show both dates. Ex: "<MON>/<YR> to " would become Sep/20 to Nov/20
Keep in mind that the maximum column heading in layout maintenance is 17 digits. You must define the heading including variables with less than 17 digits. It is not an issue If the translated result from variables yields a heading longer than 17 digits since the actual CSV heading has no limit.
In the column heading, we will offer "F7=Help with Variables" option which will display a window of the possible variables and how to combine them. Nothing will return back from F7.
In the attached example, the column heading of the following in December 2020 would be:
* Column 1: <TODAY> Item#
* Column 13: <MON> <YEAR>
* Column 14: <MON> <YEAR>
* Column 15: YTD <YEAR>
will become like:
* Column 1: 12/01/20 Item#
* Column 13: Dec 2020
* Column 14: Nov 2020
* Column 15: YTD 2020
The variable <TODAY> in the column heading is used to indicate the date when the CSV is created. But since we don't want to waste a column for that, we can put it in column 1 with item#. In theory, you can also make column 1 a constant column with no value and use <TODAY> in the column heading to display the heading as system date.
It is possible that a variable value can't be rendered. In that case, the variable will be kept as it is and display in the heading.
Avi Weissbard - Cybermac software LLC commented
Excellent idea. Also the U with previous periods functionality is excellent !!!