Rolling 12-Month Dashboard - Dynamic Column Names

[kve (Kevin Veldman)]

I was working on a dashboard that included rolling 12-month sales data. Initially I was just using ints as Column Names (M12 = today - 12 months, M11 = today - 11 months, etc). But that annoyed me, so I was searching around on the site. This post was a huge help, but it wasn’t super clear so I wanted to do a post in case anyone is trying to do this later. Here’s what my dashboard looks like now:

The column names are dynamic, so in a couple days when the calendar flips to October, the first column after the Rolling 12 Total will be Oct 2024, and so on.

Steps to add dynamic column names:

1. Build your dashboard. You can do it with a new application or with the Wizard.

2. Identify the GUID of your data grid. The post I linked above has a complicated method to do this which didn’t work when I tried it. YMMV. I found it easier to just export the application, and open the layout.jsonc file in a text editor to find it
   

   

3. Create an event using the “property-set” widget to set your column names.

4. In the property-set Parameters, put your guid in both the Component Id and Guid fields:
   

   

5. In the property-set > Parameters > Props area, add a new property. Under “Prop”, add the text gridModel.columns[16]. In my case “16” is the zero-indexed column number (so the 17th column) for the column name I want to change. In “Value” add what you want to change the name to. If someone has a better way to ID the column (using the name, for example, let me know.
   

   

6. Then just add more props to the same widget. You don’t need events for each column:
   

   

7. NOTE: For some reason, most of the action triggers I usually use for something like this (After Form Load, for example) weren’t present in my dashboard. I attached this event to a button click. You can skip the workaround for getting these names to load automatically if you have an event in your app that works for you.
   

   

8. I’m not a JavaScript wizard, so to get the dynamic column names I wanted, I created an Epicor Function that returns the column names I want. I then set an event that calls that Function, triggered by the Window onLoad event. The function is very simple:

var dt = DateTime.Today;

m12 = dt.AddMonths(-12).ToString("MMM yyyy");
m11 = dt.AddMonths(-11).ToString("MMM yyyy");
m10 = dt.AddMonths(-10).ToString("MMM yyyy");
m09 = dt.AddMonths(-09).ToString("MMM yyyy");
m08 = dt.AddMonths(-08).ToString("MMM yyyy");
m07 = dt.AddMonths(-07).ToString("MMM yyyy");
m06 = dt.AddMonths(-06).ToString("MMM yyyy");
m05 = dt.AddMonths(-05).ToString("MMM yyyy");
m04 = dt.AddMonths(-04).ToString("MMM yyyy");
m03 = dt.AddMonths(-03).ToString("MMM yyyy");
m02 = dt.AddMonths(-02).ToString("MMM yyyy");
m01 = dt.AddMonths(-01).ToString("MMM yyyy");

That gives me the names I want in the actionResult dataView. In the screencap above, you can see I set my first column to "{actionResult.m12}", which is one of the response parameters in my Function Signature.

9. As I said above, I attached the property-set widget to a button click. To get the column names to load, I just attached an event-next widget after my function call, and triggered the button click event. You could do this with no trigger instead of a button, but I was using the button for testing, so it was already there.
   
   

And that’s all there is to it. Open up the dashboard, and your column names should update automatically before you even see the original column names. Since I was using column Index numbers to identify the columns, I was a little worried that personalizations that changed the column order / numbering would cause a problem, but it doesn’t seem to. I tested it with rearranged columns and removed columns in the personalization, and it still changed the correct column names every time.

[utaylor (Utah Taylor)]

That’s pretty slick! Thanks for sharing! Wish the labels for fields in BAQs could be expressions of the data itself so that they’d just come through like this.

[kve (Kevin Veldman)]

That would be sooo much easier. Or even if the label field in Grid Model > Columns could evaluate expressions.

[Randy (Randy Stulce)]

This is awesome thanks for making this post. It’ll come in handy for sure.

[jbooker (Josh Booker)]

Yeah the various columns collections need another abstraction to drop the assumption that colName=fieldName

Columns, columns, columns

Been trying to wrap my head around the various columns collections situation and think I’ve come up with something of an idea… just putting this here for comment Problem its a long list, maybe we’ll add it here but we all know at least a few Idea columns have two properties field and title and we need a third for abstraction, say ‘role’ or ‘id’. BEFORE (what we have today, grid column id assumed to equal field) gridModel.columns = { Part_PartNum: { field: "Part_PartNum", title: "Part #"…

PS - nice solution, BTW.

[jbooker (Josh Booker)]

@kve

to get the dynamic column names I wanted, I created an Epicor Function that returns the column names I want. I then set an event that calls that Function, triggered by the Window onLoad event.

Here’s the client-side way.

The MetaFxTransactionService handles dataViews and is in scope as trans when evaluating expressions. It has an addRows() method which’ll load any array of objects into a dataView like: trans.addRows("dataViewName",[{field: "value", field2: "value"}])

So tossing a js one-liner that builds your object array into a condition action expression like so does the trick:

trans.addRows("actionResult", [
    Object.fromEntries(
        Array(12)
            .fill(0)
            .map((_, i) => [
                'm' + ('' + (12 - i)).padStart(2, '0'),
                new Intl.DateTimeFormat('en-US',(o = new Object(), o.month = 'short', o.year = 'numeric', o)
                ).format(new Date(new Date().setMonth(new Date().getMonth() - (12 - i))))
            ])
    )]
);

saves a server round trip and function maintenance with millisecond execution times.