Rolling query display

Mark HHP

Hey guys,

Say I have a forecasting module.

At the moment it displays the months from January through December at the top.

Is there a way to specify it so that the query display rolls over a 12 month period?

So now, if you opened it, it would display July - June at the top. Then next month August - July. This would make my life simpler.

I've never used rules, processes etc to manipulate the display of a query so I'm unsure if it's possible. Could you maybe assign them number values then order them by number according to date?

I'm spitballing here, which I guess is the best way to figure out the problem.
[/img]

RocketRod

Not sure how your data is structured but one way.

Create 12 queries one for each month displaying what you want. Then on the menu use applicability rules ....current_month=April display April query.

Mark HHP

At the moment, it is displaying ForecastLineItems within a Forecast object as a table of items.

I need the headers at the top of the table (as well as their corresponding details) to shift accordingly. At the moment, it looks like so:

DEC/JAN | FEB | MAR | APR | MAY | JUN | JUL | AUG | SEP | OCT | NOV

But if I was in August:

AUG | SEP | OCT | NOV | DEC/JAN | FEB | MAR | APR | MAY | JUN | JUL

Perhaps, if I could set them with year values as well, it could show the current month and rather then pull in DEC/JAN again (possibly confusing it with the previous DEC/JAN) it would pull in the next DEC/JAN.

As I type, I'm thinking of other things. Someone else (Tford, I think) suggested a show_date and last_shown_date so that after the last_shown_date has past, it's not visible anymore.

Again, I'm not sure whether dynamic displays like this are possible.

RocketRod

Food for thought.

Lets say you wanted to hold 24 months forecast. Set up a table with:

customer
product
qty1
qty2
qty3
...
qty24

Jan query shows:
qty1 heading Jan
qty2 heading Feb
...
qty12 Heading Dec

Aug query shows
qty8 heading Aug
qty9 heading Sep
....
qty 19 heading Jul

Your users can always edit 12 months out.

At then end of the year you have a process that moves qty13 to qty1, qty14 to qty2 etc. and zeros qty13 to qty24.

Off you go again.

Cheers Rod

Mark HHP

Any chance you could give me an example of the process? I've never attempted anything like that.

RocketRod

I think its good to play with this yourself as i find its a great way to learn. However this should start you off. Remember there are many ways to achive this. Basically I'm suggesting creating a second record type which you realign the data into. Then remove the original set and make the realigned set the original. This is just one and is UNTESTED so it may have a bug!

Process Forecast

FIND Forecast Where ForecastType=1 IN BATCHES OF 1
Forecastconvertsub
FIND Forecast where ForecastType=1
DELETE
FIND Forecast where ForecastType=2
ForecastType=1

Process ForecastConvertSub

CREATE Forecast with ForecastType=2,ForecastTemp.Qty1=Forecast.Qty13...ForecastTemp.Qty13=0...

Cheers Rod

Mark HHP

Thanks, Rod. I do prefer to try it myself but I find sometimes after a day of troubleshooting, my brain shuts down and I need a spark 😛

Thanks again.