Import data into a database clone (no records), find and delete empty field records, import to working database.
Here's another option I learned from LaRetta:
Depending on what auto-enter options are set on your fields, you might be able to import your data into a set of global fields with auto-enter calculations set on the data fields to only copy the data from the globals if they are not empty. Then import your data aligning the global fields with your data fields or columns in the input file and enable the auto-enter options.
Don't do this If you have other auto-enter options enabled that you don't want to have execute during the import.
So if I define the field "xyz" with validation that requires "not empty"...
and then import records...
will a matched record has a value for "xyz" retain that value if the imported record has no value for "xyz"?
I hadn't considered that approach. I'm describing an auto-enter by calculation rather than a validation rule.
You might test that option on a copy and see if it works the way you need. Off hand, I'd expect that validation rule to keep the entire record from being imported, (I haven't tested this, so I might be wrong) if that particular field is blank. That might or might not be the result you want here.
So how would I use a calculation?
I assume I need to do an if/else of some sort?
You'd use calculations and additional fields. Keep in mind that this may or may not be the best approach based on what you have designed and whether this is a one off import or one to be performed regularly.
Let's call the field to receive the data "Name"
Define a global field gName and import the name data into gName.
FInd the definition for Name in Manage | Database | Fields and double-click it.
Select the auto-enter tab and click the "by calculation" option.
Enter If (Isempty (gName) ; self ; gname ) as the auto-enter calculation.
Clear the do not replace existing value check box so that newly imported values will replace the current value.
When importing, import the data for this field into gName instead of Name.
So what that calculations says is "if gName is blank, keep what's in Name; if it's not empty, overwrite Name with gName.
Is there a similar calculation for appending data already in the record with the data coming in from the new record?
Just use an expression like:
self & gName
or if you want it separated by a carriage return:
self & ¶ & gName
I need to wrok through this over the weekend. Any chance you'll be online if I get stuck with something?
Sorry, I don't participate in the forum over the weekends.
Thanks for the heads up!
Using a global field does work (I learned the technique from Comment) but I am unsure if it is the best approach here. It would help to have a bit more information:
1) Is this an ongoing process or a one time need?
2) What is the format of the incoming data? Excel, FileMaker, csv?
3) How many fields are you comparing?
4) Is it correct that, if the incoming source field has a value then it should overwrite the target field even if it has a value but never remove an existing target value if source is empty?
5) Are you also adding new incoming records that aren't in target or is this simple update existing data?
6) A bit of background on the purpose would also help.
My sense is that it would be easiest to relate the two sets of data and loop set each target field with the source (if the source isn't empty) which would save creating a global for each data field (in essence doubling the number of fields). If you have one or two fields it would be fine. Always back up before trying anything new on your data and back up between EACH attempt and step. If you wish to try the auto-enter global import, you would need one global for each field and it would work like this:
Field1 would have auto-enter calculation (with Do Not Replace Existing Value UNCHECKED). Auto-enter calculation on Field1 would be: Case ( IsEmpty ( globalField1 ) ; Self ; globalField1 ). When you import, use UPDATE and match your keys. Then you map your source field1 to globalField1 and be sure to check 'perform auto-enter' during the import, when asked.
It is a good concept when used in the right circumstance; I'm just not convinced that it is the best option here. Only more information will help in that determination. :smileyhappy:
Thanks Phil & LaRetta for you help!
Here's some more info.
We run baskerball camps and have an FM file with PLAYER INFO. We also run AAU tournaments and coaches submit their rosters for those tournaments and want to use that data to update and add to our PLAYER INFO.
The ROSTER data goes into mysql via the web. We import the ROSTER data info FM and create a ROSTER INFO file.
We plan to match records on nameCheck (fname lname) & zipCode. If we get a match, we want to update the PLAYER INFO using the corresponding ROSTER INFO.
At this point, however, not all the fields in the online roster form are required, and many are left blank (next year we are going to require everything). So we only want to overwite a field if the ROSTER INFO is not blank.
And we have another field (eventCode) that we need to APPEND with the ROSTER INFO data in that field.
One question I have regarding globalFields and using a calculation..are you saying we need to permanently redefine the fields that are in our PLAYER INFO file? They need to become calculations?
Still need some help with this.
I created a clone of the file and modified the fields as suggested.
THe data imported and the records were updated correctly.
Works fine for the import, but do I need to leave them as calculations after the import is done?
If I do, the data cannot be modified in the original field, it has to be edited in the global field.
Am I missing something?