Migrate tables to new fiscal period

[bpex]

Hi. We have an IWP-based solution with several generations of one-to-many tables of objectives, projects, tasks, etc. All roll up under a particular fiscal period. We would like to add the capability to create a new fiscal period in the same file, then populate it with copies of all (or most) of the existing tables into it, along with all relationships.

When completed, both fiscal periods should exist "side by side" with their respective copies of the child records and relationships beneath them for editing as desired. (Locking the old fiscal period afterward is not necessary, although we can probably figure that out.)

Since export/import is not Web-compatible, is there a technique that others have used for such a thing? Most likely a "brute force" recursive script approach? Having a tough time figuring out where to start with this.

Thank you for any push in the right direction.

DW

[comment]

Adding tables and relationships is a structural change - it cannot be scripted and certainly cannot be accomplished from an IWP client. In any case, having duplicate tables for the same thing is not a good idea. The fiscal period of any record should be just a field (probably could be calculated from the date?).

[bpex]

Thank you for your reply, comment. I may have described one aspect poorly. It is not that we want to create duplicate tables -- just duplicates of certain records and fields in selected tables.

Consider a table of Objectives, some of which are not met as planned, so it is desired to "roll them over" into the new time period. Each such record would have its own unique S/N in the single table for Objectives; it simply would have been "cloned" to save the user much manual re-typing or copy/paste. Going forward, the "new" version of the Objective would have its fields modified as appropriate, not affecting the "historical" version (which is why, of course, we probably will lock the previous tables). The ERD will be unchanged; we just want the "new" records to point to the new time period.

You're right about not making the time period its own table. In this case it has variable details describing it, so we treated it as a "thing". Even so, the challenge would be the same, just with Objectives being the top level, and all child records pointing correctly to their "new" parents.

This seems very similar in principle to closing out an accounting year (I think). Anyway, sorry to ramble.

Thanks,

DW

[comment]

I still don't see why it would be necessary - or desirable - to duplicate records, just because some arbitrary date has passed. If an objective has not been met - has it now become a NEW objective? If it were a table of shoes, I would ask: did you throw out all your shoes on New Year's Eve and bought new ones instead?

[bpex]

Yes, exactly. An unmet objective, if carried over, IS in fact a new objective -- if for no other reason than its time parameter is now changed. The user may choose to rethink other parameters of it, too. But it is a unique objective with its unique S/N. Basing it on the historical version is simply a time-saving starting point for the user. And having the history available can help with decisions going forward.

[comment]

What happens if the user chooses to rethink some parameters in the middle of a fiscal period? Are they not allowed to do this?

I am trying to say you are digging a hole for yourself here. It's all right to duplicate a record here and there as a shortcut to filling a new record. But "closing a period" by duplicating entire sets of records, with children (or some of the children) - you are really talking about a migration here. A complex and vulnerable process. And unnecessary, IMHO. Does your bank duplicate your account every year, or every time you get a balance statement?

[bpex]

Yes, parameters can be changed during a time period -- with the exception that the time element should not go beyond the end of it. When the company starts a new business management time frame, they want to be dealing with a "fresh" set of objectives, projects, tasks, etc. that relate to the current budget, resources, and priorities. I am only trying to give them a snapshot of last year to be used as the starting point. The goal is to automate the steps that the user otherwise would have to do manually -- either a lot of re-typing, or a lot of copy and paste. It doesn't seem like it should be that complex or risky.

So... back to my original question: ) "Is there a technique that others have used for such a thing?"

(Yes, this is something like a migration; that's why I used the word in the subject line.)

Thanks for your help.

DW

[comment]

It IS complex: you need to find the records you want, duplicate them and renumber them. But you need to keep their previous ID's, so you can find their children. Then you find the child records, duplicate them and replace their old parent ID's with the new ones. But you need to be careful to do this only to the duplicates, not to the originals. This requires quite complex scripting, and it needs to be absolutely perfect because if it runs into any kind of problem, you'll be left with a mess. You also need to make sure the process cannot be initiated more than once, and that there is no mechanical/electrical failure while it runs. And that's even before considering the limitations of IWP.

No offense, but I won't be helping you with this. You might start by searching the forums for threads on duplicating a found set - I recall there were a couple of times this came up.

[bpex]

Thanks for the caution, comment. The way you have outlined the process sounds risky indeed. I wouldn't attempt it that way, even though it's probably the proper programming approach.

Instead, I am thinking of a long recursive script that "walks the tree" using nested loops. I just completed a fairly complex script that is similar, used for creating an 8-level hierarchy with multi-line text fields indented. While maybe not very elegant under the hood, it does a surprisingly good job at displaying the result, even with IWP.

Just as with that hierarchy tree, I see the record-copying script as a long series of nested GTRR, CopyFields, SwitchLayout, CreateNewRecord, Paste, etc. In other words, mirroring the actual steps a user would have to go through manually, dealing only with text.

It may turn out to be too cumbersome, but we have clients and prospects asking for it, so...

Thanks again.

DW