Issues with Autogenerate Errors

[raynac]

I have written an autogenerate script for a calendar and it's working perfectly for me:

Temp:

Load

               min("CM Prov Sched Date 1") as minDate,

               max("CM Prov Sched Date 1") as maxDate

Resident cmprovschd;

Let varMinDate = Num(Peek('minDate', 0, 'Temp'));

Let varMaxDate = Num(Peek('maxDate', 0, 'Temp'));

DROP Table Temp;

TempCalendar:

LOAD

               $(varMinDate) + Iterno()-1 As Num,

               Date($(varMinDate) + IterNo() - 1) as TempDate

               AutoGenerate 1 While $(varMinDate) + IterNo() -1 <= $(varMaxDate);

MasterCalendar:

Load

               TempDate AS "CM Prov Sched Date 9",

               week(TempDate) As "CM Prov Sched Week",

               weekend(TempDate) As "CM Prov Sched W/E",

               Year(TempDate) As "CM Prov Sched Year",

               Month(TempDate) As "CM Prov Sched Month",

               Day(TempDate) As "CM Prov Sched Day",

               //YeartoDate(TempDate)*-1 as CurYTDFlag,

               //YeartoDate(TempDate,-1)*-1 as LastYTDFlag,

               //inyear(TempDate, Monthstart($(varMaxDate)),-1) as RC12,

               date(monthstart(TempDate), 'MMM-YYYY') as "CM Prov Sched MonthYear",

               //ApplyMap('QuartersMap', month(TempDate), Null()) as Quarter,

               Week(weekstart(TempDate)) & '-' & WeekYear(TempDate) as "CM Prov Sched WeekYear",

               WeekDay(TempDate) as "CM Prov Sched WeekDay"

Resident TempCalendar

Order By TempDate ASC;

Drop Table TempCalendar;

Weirdly enough...one of my clients is getting an error on hers.  (See the error on the attachment).  We are both running 8.5 - and I have many other clients running several different versions...and no one else is reporting errors.

Does anyone know what's going wrong?  I tried removing all the data and re-saving it, and sending it to her.  Same issues.

Any help you can give me would be much appreciated!

[Clever_Anjos]

First of all, I suggest you to upgrade, since 8.5 is no longer supported.

Could you check how many rows are retrieved by this code?

Temp:
Load
              min("CM Prov Sched Date 1") as minDate,
              max("CM Prov Sched Date 1") as maxDate
Resident cmprovschd;

[raynac]

Hi there,

Looks like it's retrieving one line on my system when I run it.  I would have to ask my client for access to her server in order to see what it's retrieving on hers.  (Was that the point? To see if she was getting something there, not whether I was?)

As for the upgrade, the company I work for had made the decision to stick with this version due to a change that came along in Version 9 that we had not wanted.  That being said, I do have a couple of clients using later versions as we could no longer obtain 8.5 and they don't seem to be having issues with this piece of code.

And I just had a thought as I was writing this....if that table is totally empty for some of my clients because they aren't using that module of our software suite, would that be the issue?  This is the first time I have ever implemented an Autogenerate table in a script, and it didn't occur to me, unbelievably!

[Clever_Anjos · Accepted solution]

"if that table is totally empty for some of my clients because they aren't using that module of our software suite, would that be the issue? "

you got the point...

If that line is empty both of your variables will be empty too.

[raynac]

Well, don't I feel like a right idiot?  All of my testing was done on our inhouse demo database where all modules are in use so the problem didn't present itself in development and testing.

Thank you so much for helping me see the problem!  I very much appreciate it.

[Clever_Anjos]

That´s what Community Foruns are made,

Best regards from Brazil