Script import into multi-table file

[LaRetta]

Well, I decided not to go with the separation model. I have a file with 26 tables which is networked. Problem is ... I'm not finished with it and need to work on it. I realize I can't work in design while it's hosted, right? So from 7am to 6pm it is unavailable. So I'm limited to evening work now. Server 7 purchase won't happen for another month.

I have been downloading the file at night and uploading it every morning but I'm wondering if scripting an import into a clone would work better - so I can also work on it during the day. The file is 415mb (414mb is straight data). But with auto-enter calc keys etc, I'm nervous about the process. Has anyone done this yet? I figure I'll have to not 'Perform auto-enter' and import my keys as well; and then reset my serials to the last serial (in each table). How would you approach this, and what else should I watch for?

Other than suggesting I should have gone separation model, would you all attempt a mass import nightly, or just download the whole file and only design at night? I don't think I like our system networked - it really limits my work on it.

If, from the host, I drop into layout mode to make a small change - or if I modify a calculation, will it dump my Users or mess them up? Is there anything I can do in this file while hosted? I'm terribly afraid I'll crash the Users if I do anything! Also, when it says 5 users, does that count the host? Or is it 5 users PLUS the host? Thank you!

LaRetta

[LaRetta]

Sorry for the bump ... I'm desperate to work on this system and I'm nervous about a full import into a clone. Has anyone done this yet? Today, I dropped into layout mode and made a change on a list report. No users crashed or anything. Am I taking a risk by doing this? Are there limits in what I can do design-wise, while we are networked? I hate to find out the hard way.

Also, if I want to use the system on the host computer, do I place an Opener file on that system also, and open as a guest? Or do I just start working on the hosted system directly. My first network ... does it show?

LaRetta

[Fenton]

I'm no great expert on all the ins and outs of working on remote files. I believe it works better than you think, quite well in fact, amazing well in version 7. You can do almost everything. But it is not 100% safe (and certainly not as fast, though it's not bad now). It is less safe than working on files locally, which is also not 100% safe; nothing is. Basically there should always be an up-to-date backup of the data, and the structure (not necessarily the same thing), which could be fairly easily used to replace ANY crashed or even frozen files.

I don't understand why you are needing to update your files every night to work on the structure. Why can't you just wait until you're done (or almost done), then do one big import, with all the files on a local machine (wherever that might be)? Or, at worst, do a weekly update on the weekend. Why do you need the latest data, and why do the users need the latest structure every day?

I feel like I'm missing something about the whys of the situation. Yes, you can do serious development work remotely, but personally I'd rather not. It's really best used for the odd tweak.

[LaRetta]

Hi Fenton, thanks for helping me.

"I don't understand why you are needing to update your files every night to work on the structure."

Part of the 'structure' work involves normalizing the incoming data for assigning Value Lists; balancing GLs from a Peach system by identifying payments made (that were never backed-up (from old system) and thus not in the new system) and generating system reconcoliation payments; identifying two customers that were assigned the same ID (in Act/Peach) structure before FM and combining the data; the list goes on, I'm afraid. I need the current data because Staff is sending notices identifying these duplicates and I'm also importing PeachTree invoices, transactions & Customers; and script-converting them to the new system formats (using unique IDs, etc.). We are currently straddling a fence and I'm working on pulling the plug on both Act and PeachTree -- but I'm not quite there yet.

So every late-afternoon, I import and massage the data - which needs to display correctly for the next day in FM. I am scripting the process as I go but much is still 'design as I correct.' And I had to implement before it was ready. It's purpose was an intermediate step to help us normalize the data. It is helping the company tremendously to clearly see the problems (as only FM can present) - combining many Act customer dbs plus PeachTree customers and invoices; but now they want it all. Now.

Some changes need to happen immediately - a window producing a 'cannot modify record because it's being used by another user', needs a Commit Record/Request added quickly - some things can wait a bit. They wanted to simply view and edit the Contacts data - and now they realize they can't send them a fax or letter and they have to open Act again. So tonight, I'll be creating some templates for them - and finish matching their Customer Accounts. My Office Manager postion and Developer position are intermixed on a 24/7. Well, that's probably more than you wanted to know, but that's why.

I have read not to open as guest on the host system; and I assume that's true. So I will open the host directly if I need to work on it - I hope not very often. And I will zip copies of the file frequently throughout the day. What if I'm in layout mode when a User tries to run a script?! Well, I'll take the full file with me when I can; and I'll script a full import for the rest. But I must be able to work on this most of the day - and all evening - in one form or the other - usually both.

LaRetta

[Fenton]

The solution seems to be for there to be 2 of you. I see now more what's happening; and you seem to be doing OK. Personally I would have committed suicide or murder in the same situation, but that's just me :-/

Careful backups are necessary in this kind of situation, even if it involves slowing production. Both the data and the structure are being changed, in possibly major ways, with no real testing, other that what you can manage at the moment, on live data. This a dangerous situation, but the dangers can be lessened greatly by backing up. Of course, the fact that they are not using Server compounds the problem, since you can't backup plain FileMaker hosted files without kicking everyone off.

If they will not take the time to back up the entire files very often, which I can understand, though I don't really agree, you should at least try to back up your structure as a clone. This takes very little time, so no matter what your structure will be safe; the data will likely be OK, you can import and keep going. You do not want to have to start over, or continue with a crashed structure.

It's kind of ironic that the reason they are in this situation is that they didn't really trust that FileMaker could handle the business, so didn't really plan properly for a complete makeover. But now, partly (mostly) because of FileMaker's ease of use and reporting, they see what a mess their data is in; and decide the FileMaker solution should to do it all, right away. Sigh.

[LaRetta]

Fenton? Am I wrong in assuming I am getting a good backup copy if I zip our file (from the host computer) while it is being hosted?

My other problem is that we have four systems with WinXP (counting the host) that have FM loaded ... but not my computer yet ... because it must remain Win98 for PeachTree. And they have another station ready for FM that they are anxious to get going (we have 10-license) for someone else and that's why I was asking about the number of Users running without Server. If they want me to work on it at work, I need a computer with FM or I will be forced to only use the host system - even if working on a clone and I am trying to leave the host alone - no activity whatsoever. I need a system with XP. Today looks like a good day to draw a few lines. We need Server now - not in a month.

Thank you so much for helping me. Wish me luck.

LaRetta

[The Shadow]

Don't zip a file that's open. Use the "Save a Copy..." menu item from the host to make a backup. In FM7 this doesn't kick any guests off, they will just be sluggish for a minute.

Personally, I would suggest you doing design work as a guest, rather than on the host machine. Even with FM6, we do this all the time for layout design. When using the host directly, if you would crash the whole thing comes down.

As Fenton mentioned, in FM7, things work even better. There is no problem changing layouts or scripts as a guest. Changing schema also works, but if you make a change to a table that is being actively used, you need to commit the table ASAP by leaving the dialog, or your users will start to see messages like, "Admin has locked table Foo..." and not be able to continue.

Of course, be very careful when working like this, you could easily "trap" someone inside a script that can't complete because you deleted a field and didn't check for that error, or worse.

[Fenton]

The Shadow certainly does know. I did not know that regular FileMaker 7 would allow Save a Copy As while guests were on. This is great, for my cheapo clients (of course they're mostly still on 6).

You can script this, calling several external scripts from a central one. If you also have a Save a Copy As Clone chain, it is very fast (relative to Save a Copy as Copy). It can even be a separate little file only on your workstation, like an opener file, but a Save Clones file, in order to hide it from everyone else. Every time you take a break, even to get a drink of water, run it and your structure will be protected. It's a good feeling. You would, however, have to stop everyone and import the data if there is a crash. But that would be a rare occurrence hopefully.

[LaRetta]

Thank you both for your ideas.

Today, I performed a Save A Copy As and it only took 18 seconds!! And Users didn't even notice. I plan to use both your ideas and back up several times throughout the day - gotta love that 7!! Maybe overkill, but I'm still pretty nervous about this whole network thing without Server.

After discussion, Management has decided to speed up the Server purchase (within 10-15 days) and I will have a new system tomorrow (with FM and XP) - they were setting it up as I left - so now I will have two computers at my desk. This max's us out until Server arrives. Sometimes I get so busy trying to just make everything work, that I neglect to fill them in on my needs. They are a supportive bunch ... just VERY excited about FileMaker. One of these days, my 'anything is possible' attitude is going to do me in! But for now ... life is wonderful.

LaRetta