append to a field instead of overwriting it
can FM import records but instead of overwiting the data in a particular field, append it with the data in the imported field?
Not directly as part of an Import Records operation, but you can set up this process with a little work.
Here's a rough outline of how you might do this:
Import the data into Temp--a temporary table.
Create a relationship linking Temp to MainTable--the table where you want to append your data.
Either write a script to loop through the data and use the following script step to append data for each such field:
Set Field [MainTable::FieldName, MainTable::FieldName & Temp::FieldName]
Use Replace Field Contents (Found in the Records Menu) to replace with a specified calculation:
MainTable::FieldName & Temp::Field
as your specified calculation.
You can also use a global field but you must apply caution. You would import (update and add to) from your new text to the global field. Your standard text field (AppendField) would have auto-enter (replace existing value) with TrimAll ( AppendField & " " & global ; 0 ; 0 )
Be sure you match on an ID so the proper records are updated. And show all records first.
After your import, it is important to clear your global field in case you later (during that User session) wish to change any data in the append field. If you leave data in the global and you change ANY appendField data while the global contains a value, then the global data will append to that record.
I really think your suggestions are confusing the issue.
The original post describes using Import Records. This suggests, but I could be wrong, that they want to append different data to different existing records. Using a global field doesn't make sense for that task.
That's why I've posted a general description and am waiting for a response from the original poster. If I've made correct assumptions, then maybe they have all the help they need or a follow up post from me can fill in more detail. If I'm wrong, we can move in a different direction.
Let's wait to hear from the person who made the original post.
Webguy262 said, "can FM import records but instead of overwiting the data in a particular field, append it with the data in the imported field?"
My process does EXACTLY that. Did you even give it a try?
Phil said, "Let's wait to hear from the person who made the original post."
Hmmmm, now nobody can make a suggestion besides you? I will suggest whatever I wish to suggest ... just as everyone else is capable of doing. My process DOES use import if you've had bothered to try it.
"Hmmmm, now nobody can make a suggestion besides you?"
Nope, never said that. I suggested waiting since the original post is somewhat vague.
My process DOES use import...
I can't really follow what you are suggesting. Feel free to provide more detail. Collaborative suggestions make this forum very valuable.
I never said your process didn't use Import, I simply expressed my opinion that it didn't make sense to use a global field since there could well be a different value to append from every imported record to a matching existing record.
I repeat my suggestion to wait, because all suggestions, including mine, could be way off base here without hearing more detail about what they really want to do.
I'm trying to help, not criticize.
You crack me up! You contradict yourself, saying "Feel free to provide more detail. Collaborative suggestions make this forum very valuable” and then again telling me to stop with my suggestion. Truth is, I didn't want to publically correct you when you said it was not possible using a straight import and it IS POSSIBLE. Your suggestion was to create another table, create another relationship and then use Set Field or Replace Field Contents!!
"I simply expressed my opinion that it didn't make sense to use a global field since there could well be a different value to append from every imported record to a matching existing record."
Again, it can all be handled with a straight import, where differerent values in records coming in, appends to the same ID records in the existing table. And all I did was suggest an alternate method. So shoot me.
And yet you still haven't even tried it.
Okay, here is the exact process:
<!-- [if gte mso 9]><xml>
</xml><![endif] --><!-- [if gte mso 9]><xml>
<w:LatentStyles DefLockedState="false" LatentStyleCount="156">
/* Style Definitions */
p.MsoNormal, li.MsoNormal, div.MsoNormal
font-family:"Times New Roman";
mso-fareast-font-family:"Times New Roman";}
margin:1.0in 1.25in 1.0in 1.25in;
--><!-- [if gte mso 10]>
/* Style Definitions */
mso-padding-alt:0in 5.4pt 0in 5.4pt;
font-family:"Times New Roman";
Main table with:
textField (which needs to be appended with incoming imported field data)
textField has auto-enter calculation (replace existing value) with:
TrimAll ( textField & " " & global ; 0 ; 0 )
Import script would be:
Go to Layout [ MainTable layout ; MainTable ]
Show All Records
Import [ importFile ; Update matching records in found set only ]
Set Field [ global ; "" ]
… and match in the import map:
Import::newText to MainTable::global field
Import:: ID = MainTable::ID
Yes webguy, then my suggestion will work. You map on the players ID and simply import into the global as suggested and it will append the text field.
But you realize that this data should be split out into a normalized structure, make up of related tables, right?
So for each imported record, you need to append new data from matching imported data. Using import records to pull data directly into your table overwrites existing data with the imported data which is the problem you wish to avoid. You need to import the data into a different location and then use a 2nd step in the process to combine the data into a single field. Let me know if I've got that wrong.
You can either Create an extra field in your existing table or create a new table, (It's easy to creat a new table with the import Records tool as you can specify a "new table" as the target for your imported data) and link that table with a relationship.
In either case, the next step is fairly simple:
Use Replace Field Contents with a specified calculation:
MainTable::NameofFieldWithCurrentData & "¶" & TableName::NameofFieldThatReceivedTheImportedData
I'm using place holders for the table and field names as I don't know the current field and table names, but hopefully, you get the idea.
Now with that said, I'd recommend taking this accumulated data and breaking it out into separate records in a related table. Then each import could simply add a new record to the related table. That would do an even better job of solving this problem.
Note to Laretta, I'm sending you a private message, check the upper right corner of your screen for the envelope Icon. we've opened up a separate issue that has no place here (in my opinion) in this thread.
Very clever, once all the details were spelled out so I could follow it. That wasn't how I read your original post at all. :smileywink:
I never realized you could import data into a global field and have it process through in this fashion.
You do have to be sure that your auto-enter option is selected during import. That may not be possible in some cases if you have other auto-enter options set that you don't want the import to change. You can test with a copy of your file to make sure it works.
I'd also separate the values with a carriage return (use the paragraph symbol) instead of a space.
The only other issue which is so minor that I don't think it really matters is that using a Global field means you can't review the imported values after import to make sure you pulled them in correctly.
Retrieving data ...