Looping Month/Year increments

[LaRetta]

Hi everyone,

I've written a process which works. However, to help me learn, I'd like to throw it open to the Forum for improvement. This will need to be generated periodically and my existing process doesn't appear to be very efficient.

Can anyone give me pointers on how I could have handled this better please?

I have a table of Products. I need to generate new records within the Sales table - one new record for each product for each month and each year; from 1/1992 through 12/2005.

I ended up creating 2 scripts (geez). This is what I have:

Sales Creation Script:

Import [ ... entire Product table ]

Go To Record/Request/Page [ Last ]

Loop

Set Field [ gMonth; 1 ]

Set Field [ gYear; 1992 ]

Loop

Duplicate Record/Request

Set Field [ Month; gMonth ]

Set Field [ Year; gYear ]

Omit Record

Perform Script [Month Counter ]

Exit Loop If [gYear = 2006 ]

End Loop

Delete Record [ Perform Without Dialog ]

... added late ... Exit Loop If [ not Get(FoundCount) ]

End Loop

Month Counter Script:

If [gMonth = 12 ]

Set Field [ gMonth; 1 ]

Set Field [ gYear; gYear + 1 ]

Else

Set Field [ gMonth; gMonth + 1 ]

End If

I will be changing the hard-coded start of month/year from 1/1992 to the month/year I need to create (using starting globals, of course. And will also use 'Ending' globals to stop the process. But I'd like to get a feel for how best to approach this ... AND, I need to create the history of records in this one generation (above) to bring it up to date.

If you are asking why I'm doing this, it's because Owner wants Excel spreadsheet (yep) in Cross Tab form. Existing summary export on LineItems only gives what EXISTS. He needs to see the 'holes.' So I've figured I need to create the static records ahead of time. Then my relationship can display the sum to export for him (based upon ProductID and Year/Month from LineItems) and the 'holes' will export also. This info is currently provided in dynamic form for viewing in FM. But he wants worksheets he can take home and manipulate in Excel. I hope this made sense.

A small part of me says I can handle this better, maybe using relationships but I just don't see it. Either way, I think I need to create a unique record (ProductID, Month, Year). But I'm open to all suggestions. I hate horsing data using scripts. Ideas?

LaRetta

[LaRetta]

Ooops, and I need to add an Exit Loop If [ not Get(FoundCount) ] ... I think. Script hasn't ran clear through the records yet so I'm unsure. But it needs to know when to exit and I didn't have that specified.

I've added that line in the above script, stopped my loop and will re-start it. It looks like it works fine and is incrementing correctly. I guess I'll find out when it gets to the last record. And yes, I learned the hard way to leave Allow User Abort [ On ] on things like this. I've trapped myself in these puppies before.

And of course, I'm performing on Form layout, adding Freeze Window and Go To Field [ any field ] before it starts.

LaRetta

[comment]

See if this gives you any ideas.

reportGen.fp7.zip

[LaRetta]

The long silence on this thread (my lack of response) is simply because I've been so excited about the concept that I've been reviewing many other aspects of my solution in which this (theory) can be incorporated. What a sweet little (totally unrelated) Report Generator tool. Wow!!!

There is one thing that is unclear to me ...

Date ( Serial# ; 1 ; Year ( Globals::gStartDate ) )

Those Month serials exceed 12.

If I reset the demo, change the next Month serial to 190 and just create a new Month record (manually) based upon gStartDate (Year 1992), it creates 10/1/2007! Soooooo ... one can increment the month ONLY and (based upon Year Start Date) it creates the advanced date (converting those excess months into years??)!!

I suppose I'm the only one that didn't know this ... but I didn't. And it tickles me - almost as much as when I found out one could use Auto-Enter serial (number) to create incremental dates (by day); learned from Ray (CobaltSky). If you would be so kind, can you explain FM's thinking on this month-increment portion?

I'm glad I posted my original problem. I dislike showing my ignorance but every time I do, I learn a great deal - a small cost in my mind. Again, thank you so much, comment. And I ADORE the 'no-line' look in the graph. I can't wait to convert some of my solution (eliminating spider webs), taking greater advantage of the power of globals.

Oh ... is there a reason you put the @ in front of your script name on that import? I mean, what does this tell you about it that is different? Do you put @ in front of all scripts which include an import or is it something else?

LaRetta

[comment]

1. Because FMP keeps its dates internally as a serial numbers, and the translation to and from date format is done "on-the-fly", you can do all kinds of neat things with the Date function ( e.g. Date ( x + 1 ; 0 ; y ) to get the last day of month x in year y). The 'thinking behind it', I think, is let FMP do the hard work...

2. You can increase the demo's efficiency by incorporating Ugo's record generation method.

3. My own private convention: @ is a prefix for a sub-script.