Re-filling database

[Charles van Dortmond]

Hi All. I'm just starting with FM and I have a questions regarding the maintenance of the application. When I would develop the next version of my application (a small CRM for a friend) I would take a copy of the running application and then do my development. When finished I have a problem: the running version has different data then the copy I took for developement...

My plan:

1) Delete all records in the new version

2) Import all records from the running version into the new version

3) Make the new version the running version

Problem: when activating the "import records" function I can select every table in my source database, but I can only select the current table in my target database..

Question: how do I need to proceed in order to have all records from all tables to be copied into the target database?

Thanks in advance!

[Fitch]

If you script it, the Import Records command lets you select a target table.

[Charles van Dortmond]

Great Fitch....Many Thanks, it gave me the option to move forward!!!

I have seen the import records command in the script editor. It seems to work.. I have made a line in the script for every table.. but I now get the source/target screen for every table.. is there a way to skip those screens and make everything 100% automatic?

Regards

Charles

[Fitch]

"Perform without dialog" checkbox.

[Charles van Dortmond]

Thxs... so final question: any way that I can prevent the login dialog box from the source database to apear for every table I import?

[Fitch]

You need to set up the same access privileges in all the files; at least one account in each file needs to match the account you're logged into, and have sufficient privileges.

[Charles van Dortmond]

Done... works perfect!

Thxs a lot

[gephry]

Biggest recommendation for that:

1. Have an Update login account for both databases (logging in the one as Update will open the other with the same privileges).

2. Have a specific update script that's called in the old file.

3. Have the update script in the new file.

4. Close both databases.

As for #2 above, if someone performed a find and then closed the database your import will not work correctly! If they are viewing 50 of 100 records and you import, you'll only get those 50 records.

To remedy this I created a --PREP FOR UPDATE script in all of my databases. It goes to one layout per table and performs "Show All." This script is called by the new database.

Then (#3) I have the an update script in the new database that goes to one layout per table. In each table it performs an automated import with "Perform without Dialog" checked.

Once completed (#4), I have the script close the old database and log out of the new one. That way the user is automatically locked out of any access that the Update account might have.

[Vaughan]

Remember to update any and all auto-entered serial numbers! It's not a good look having duplicate primary keys. ;(

[gephry]

Oh yeah! Good call, Vaughan. You can't forget that!

....is that a winking frown-face?

[Digitalbrit]

Apologies for jumping onto someone elses thread but ...

How do you reinstate user accounts that would have been in the original database?

This is the scenario where you have shipped an application to a client, they have added users and you are releasing the next version. I've played with data migration as discussed above (great advise, thanks!) but how do you keep user accounts?

Oh, and I've just thought, what about value lists where the customers have added their own values.

Hmm, is there anything else, other than data, accounts and value lists (and resetting serial #) you need to think about?

Cheers

Tim

[Digitalbrit]

After seeing this Link, it looks like external authentication is the way to go. It does look a bit heavy though.

Still, if anyone has any suggestions for easily porting user accounts between FM db's then I'm all ears.

Also, what about those user specified value lists?!

Cheers

Edited October 11, 2008 by Guest

[Vaughan]

Accounts are tricky.

The solution could have a user account table where the account names (but not passwords) are stored. This can also be imported and then the accounts re-created from this.

These re-created accounts won't have their original passwords because there is no way to discover them (and storing them in a table is poor security). Typically the passwords will be reset to a default that requires changing on first log-in.

[Vaughan]

I was going to mention external authentication, but it requires a bit of IT infrastructure to make happen.

If the solution has user-specified *anything* then it must have a user table in it somewhere already.

[Digitalbrit]

Cheers Vaughan. Yeah, I think we'll stick to a simple solution.

I have a users table and so can export a list of users from there. I guess then my release script needs to import the users and then whizz through creating the accounts. I will need to record their privileges and use this in the process.

As I understand it, this should leave me in the same state albeit that each users password will have been reset. Not ideal but hopefully workable.

Tim