07-17-2011, 07:12 AM #1
- Join Date
- Sep 2009
Importing/Updating Records - a User Experience
Hey all. I haven't posted for awhile, but here's a new question:
I work at a school and am the developer of the system (as well as a 5th grade teacher). The office staff needs to be able to import record information, updating such things as addresses, phone numbers, etc for each student in our database. They export this information from another system, the district-mandated database called Infinite Campus.
I'd like them to be able to update the records, and do so without me. I'd like to set up a script to do this. My question for you is do you allow the user to import or update records, or do you as the developer deal with that? If you allow them, how do you allow them to do that?
Some things I was thinking about with this:
1. do you directly import/update into the main table, or do you first use an import table to to then copy/update records?
2. I imagine there needs to be some sort of data validation for each field that is updated so that the wrong kind of info doesn't get put in a field.
3. Do you let them see the fields to import (the incredibly unfriendly match-fields box that pops up when you go to import), or do you program the fields to be matched to the import spreadsheet?
I'm very interested in how you accomplish this task - allowing non-developers to access a mildly complicated part of the work.
07-18-2011, 09:39 AM #2
- Join Date
- Mar 2006
- Southern California
- FileMaker Version
- FM 9 Developer, FM 10 Developer, FM 11 Developer, FM 11 Server, FM 12 Developer
- Windows & Mac
- Skill Level
Re: Importing/Updating Records - a User Experience
First of all, I am not a developer. I do develop applications that are in use where I work and my wife works.
The application I wrote for her dental office is most analogous to your question. I started with a DOS application custom written in Clarion Professional Developer, a niche player 20 years ago. I was able to export the data in .dbf files, and then import these .dbf files into Filemaker.
I had several problems to overcome:
1) Dates - the switchover was not without problem. I had to use a known Clarion date and then use a correction factor to get the correct FM date (for each date field).
2) Key - the primary (patient) key field in use was a text file using five letters followed by three numbers (derived from the last names - or last and first - and then numbers to keep patients keys from being duplicated). The problem was that I did not want to continue using this key field calculation as it was cumbersome, so I switched over to a FM numeric auto-entered field. First I had to generate this key for 1,200 patients, then loop through 125,000 using the "old" primary key in order to generate a new "new" child foreign key. One problem: I first used a portal to find the child records and looped through the child records in order to assign the new numeric key. I used a portal with 25 lines - and it only assigned numbers to the first twenty-five child records (if there were more than 25). I had to change the portal to allow vertical scrolling in order to let the script "scroll" through all the child records.
3) There were other issues similar to the above. I had to change the text fields - some of the existing were in all capital letters, so that was another script. I finally combined all of the import script into one gigantic script that took three hours to run on a core two duo laptop.
1) Import all the records and get the database working. Do not let anyone else work with the import settings. If you are FM savvy enough to do this then do it yourself. Then teach the users about updating the records.
2) You will get so familiar with this database if you do the above you will take for granted the difficulty factor. Build in safegaurds so that the users cannot delete all of the records, for instance. Limit privileges.
3) If you are in a hurry you might want to hire a professional. There are many that hang out here that could do a good job for you.
Users Browsing this Thread
There are currently 1 users browsing this thread. (0 members and 1 guests)
By LoneScrunger in forum FileMaker Pro 9Replies: 1Last Post: 10-19-2010, 09:47 AM
By gobosox in forum FileMaker Pro 8.5Replies: 6Last Post: 03-06-2009, 02:12 PM
By JoeDu in forum The Ray Cologon FilesReplies: 6Last Post: 04-07-2008, 03:58 PM
By droznick in forum FileMaker Pro 7.0Replies: 4Last Post: 03-19-2006, 07:45 PM
By Jason in forum FileMaker Pro Versions 5 & 6Replies: 2Last Post: 12-15-2003, 04:17 AM