importing records replacing field content

[freebord]

Hi guys,

I've been using FileMaker 7 as my customer database for years now and have been very happy with it.

I have a problem now though.... we havent been doing data entry for a while now, and it's finally time to catch up. When a customer orders, we would normally check to see if they're in the database already (search by email), and if they arent, we'd enter them and paste what they ordered into the comments field.

 

We're so backlogged, we need to find a solution where we can import a bunch of customers at once to save time....

 

We're at a point where we've gotten all the customer info into an excel spreadsheet and can import it into the proper fields just fine (creating new records). That is all great for new customers, but we're running into a problem for existing customers:

 

FileMaker is able to match customers (by email) that already exist to update the info, but it is overriding and deleting previous info in all the fields- this is fine in all cases except for the comments field- when a record is updated, it loses all of it's previous comment field data.

 

Is there any way to set FileMaker to ADD to a single field instead of replace the data that's in it? This is the single issue that is creating a huge problem for us.

 

 

We can get our customer info into an excel spreadsheet no problem- if anyone knows of a better way than the one we're trying to import that data into FileMaker 7 from there, PLEASE help. We dont want to create duplicate copies of our existing customers, but we dont want to erase all their history when we just want to update their comments (when they order).

 

Thanks so much for your time. Even if the solution comes as a cost, we're willing to try anything as this is very time sensative. You can also contact me directly at jessica@freebord.com or 415-285-2673 if you have a proposal.

 

-Jessica R

Office Manager

[AHunter3]

Use this technique

[freebord]

hi,

 

Thanks you so much for your response.

I've very new at the more technical aspect of FileMaker... running scripts, loops, setting relationships... this is all pretty new to me.

 

I'm going to spend my whole day messing around with what you're saying to do, but as I am not close to fluent in the language, are there any articles or how-to guides you might be able to point me towards to help me along the way? I'm walking pretty blindly thru this and have committed to having it solved by next week smiley-cry

 

like, this part:

 

Loop

... Set Field [OtherTable::Field A, Case (IsEmpty (OtherTable::Field A), Property Tax::Field A, OtherTable::Field A)]

... Set Field [OtherTable::Field B, Case (IsEmpty (OtherTable::Field B), Property Tax::Field B, OtherTable::Field B)]

...

... Go to Record [Next, Exit After Last]

End Loop

 

I have no idea what that means, and I'm sure that sounds pretty dumb to all of you (sorry!) If there's any type of quick start guide out there that I could spend some time with, I could probably manage (I had to teach myself PHP coding last week to solve a problem with our website so I'm used to cramming knowledge, lol).

 

Thanks again for all your help.

[AHunter3]

You've never scripted?

 

Or you've done scripts but never written a script with a loop in it?

 

I need to know where you're starting off from.

[freebord]

I've never done any scripts in FileMaker before Opps!

[freebord]

I made a copy of our existing customer database (I'll call it old-data table) and renamed it new-data table before clearing out all the existing records in it. I then imported the excel sheet of new customers and am happy with the way that it has dumped into the new-data table. It now looks exactly like the old-data table, all the data went into it correctly, and all of the fields are exactly the same as on the old-data table.

 

I opened our old-data table and found the relationship section, and set (old-data table)email = (new-data table)email

I checked the box for Allow record creation via this rel on the new-data side.

 

I'm not exactly sure what to do now.... I see the section for running script at the top, but I'm not sure what to write in it. The only part that I do not want overridden when I merge the 2 tables, is the 'comments' field.

 

So overall, my goal is to merge new-data table with old-data table, and when the emails match, new-data tables content can replace the info in old-data's fields, EXCEPT 'comments' field. I would like the new data content to be added- not taking away the comments that already exist for that person in old-data.

 

Thank you so much for your support in all this

[AHunter3]

I opened our old-data table and found the relationship section, and set (old-data table)email = (new-data table)email

I checked the box for Allow record creation via this rel on the new-data side

 

Actually you want to do that in the other direction instead. (What you did will not hurt anything, but you want to do this defining in the NEW-DATA file, defining the same connection using the same fields, allowing creation of new records on the OLD-DATA side of the relationship.

 

 

Once you've done that, onwards to scripting.

 

Scripting is to FileMaker what food is to supper. What going downhill is to skiing. Scripting is where you discover that you can make FileMaker do dang near anything. caution: addictive!

 

Open the ScriptMakerâ„¢, from the Scripts menu. click button to make a new script. You'll get two panes, a list of possible script steps on the left and a (so far) blank empty window on the right that will contain your actual script.

 

Loop

... Set Field [OtherTable::Field A, Case (IsEmpty (OtherTable::Field A), Property Tax::Field A, OtherTable::Field A)]

... Set Field [OtherTable::Field B, Case (IsEmpty (OtherTable::Field B), Property Tax::Field B, OtherTable::Field B)]

...

... Go to Record [Next, Exit After Last]

End Loop

 

That means you find the script step "Loop" on the left. (you can click over there and type 'lo' real fast and it will jump down to "Loop", usually). Then click the button to add that script step to your actual script over on the right.

 

You'll notice when you do so, End Loop automatically goes along with it. That's true of a couple of types of script command. Another that does that if If, which bring along End If. They're like bookends — the stuff in between them is the Loop itself, or the If clause, etc. Anyway...

 

your script should look like this so far:

 

Loop

End Loop

 

the Loop step will be highlighted, and that means that the next step you pick from the right and add will go AFTER the Loop but BEFORE the End Loop, as long as you don't change the highlight.

 

Click on the left panel again. Find the Set Field command and add it. You now have:

 

Loop

Set Field

End Loop

 

...with Set Field highlighted. At the bottom below the right-hand panel are two buttons, both labeled "Specify...", the top one with a checkbox next to it reading "Specify target field" and the bottom one with the additional label "Calculated result". What that all means is that the top button is where you pick WHICH field to set to some value, and the bottom button is where you specify WHAT VALUE to set it to. Click the top "Spefify" button.

 

A dialog pops up waiting for you to pick a field. It will be pointed by default to your local table (in this case NEW DATA file local table) which is NOT what you want; so use your mouse arrow to click the name of the table, which is up above the list of fields. SWITCH to the OLD DATA which should be available from the dropdown list of existing tables. Once you've done that, find the first field you want to set from the list of fields in the OLD DATA table. Click "OK".

 

Now click the bottom "Specify" button. A dialog pops up that you can type freehand into, or you can select from a list of fields at upper left, or you can select from a list of functions at upper right. Click into the blank space and type this much out, freehand:

 

Case (IsEmpty (

 

now go to the upper left and find the field in OLD DATA that you want to fill with info from NEW DATA but only if the OLD DATA version is indeed empty. To do this you will once again probably have to click the dropdown list of tables, because once again it will default to the local table which in this case is NEW DATA. Once you find that field, double-click on it and its name will appear in your freehand-formula area so it will now look something like this:

 

Case (IsEmpty (Old Data Table::Field Name

 

or, if you knew for sure exactly what both the table name and the field name were, you could have freehanded that part in as well.

 

You can do the rest freehand:

 

Case (IsEmpty (Old Data Table::Field Name), New Data Table::Field Name, Old Data Table::Field Name)

 

the field name will be the same for both tables, because you based NEW DATA file on a copy of OLD DATA file; and you can easily check on the exact spelling of the NEW DATA version of the table name if you need to.

 

That finishes defining WHAT VALUE to set the field to, so you click "OK".

 

Now your script looks like this:

 

 

Loop

Set Field [Old Data Table::Field Name, Case (IsEmpty (Old Data Table::Field Name), New Data Table::Field Name, Old Data Table::Field Name)]

End Loop

[freebord]

you are my GOD.

 

I'm going to dive head first into this and try to digest and apply it all smiley-laughing

 

THANK YOU SO MUCH!!!!

[freebord]

ooooook so:

 

I've done all of those things in your instructions were very clear and easy to follow. Here are a couple pics of what I'm lookin at incase it's helpful:

 

http://img131.imageshack.us/img131/9669/data1lj3.jpg

 

http://img131.imageshack.us/img131/3646/data2ky0.jpg

 

The script looks good, and the 'email' relationship is linked between customer (old-data) and orders (new-data).

 

But when I go to run script, my curser becomes an hour glass and ever 5 seconds of so a little "ESC" comes up by the bottom corner of the hour glass. I thought maybe it was scanning all the files (the 13,000 from the customer table) so I left the computer alone for just over an hour. I've come back, and it's still doing the hour glass thing with no sign of progress. So I pressed escape (as implied by the 'esc') and it stopped (I assume stopped running the script?)

Is this whats supposed to happen? Should I have waited longer? Is there a step I am missing?

 

Even though is hasnt worked yet, I'm stoked that now it seems there is light at the end of this tunnel smiley-smile

[AHunter3]

Well, so far you have this for a script:

 

Loop

Set Field [Customer::Comments; Case (IsEmpty (Customer::Comments; Orders::Comments; Customers::Comments]

End Loop

 

 

That's well fine and good as far as getting started. But it's not ready for you to actually run it yet.

 

Reopen that script in your script editor.

 

a) if there any OTHER field in the old system, aside from Comments, that you want to update based on info send in from Orders? If so, you need a separate Set Field script step for each fields. Example:

 

Loop

Set Field [Customer::Comments; Case (IsEmpty (Customer::Comments; Orders::Comments; Customers::Comments]

Set Field [Customer::SomeOtherField; Case(IsEmpty (Customer::SomeOtherField), Orders::SomeOtherfield, Customer::SomeOtherField[

Set Field [Customer::YetAnotherField; Case (IsEmpty (Customer::YetAnotherfield; Orders::YetAnotherField; Customer::YetAnotherfield]

End Loop

 

 

b) Once you've got ALL of your fields taken care of, you have to add one more script step. You see, a Loop keeps going until some condition is met. You haven't set up any condition! So it's going to stay on the first record in Orders, setting those corresponding fields over in Customer over and over and over again, ad infinitum. Not what we want.

 

There are a few standard ways of exiting a loop. The one we're going to use is probably the most common:

 

Go to Record [Next, Exit After Last]

 

Go to the last Set Field script step, then fish around on the lefthand side for the instruction go to Record/Request/Page. Highlight it and move it to the right, it should land directly under your last Set Field script step.

 

Look down at the bottom. You have options again! You can tell it to go to FIRST one, NEXT one, PREVIOUS one, or LAST one. Select the option NEXT. Once you do that, you'll see a new checkbox appear to the left, "Exit after last". Check that.

 

What that means to your script: set this field, set that field, set yet another field, etc etc, then go to the next record, oh here is End Loop so we START OVER, set this field, set that field, set yet another field, etc, etc, then go to the next record..... until there's no more "next record" to go to because we did 'em all! In that case? Exit after last! Go PAST the End Loop script step when that happens, and if you have additional script steps that continue after End Loop, that's when it would (finally) get to them.

 

You could also (for your information) start with the last record and use Go to Previous [Exit After Last]. Same idea, reverse direction.

 

 

OK once you've got that set up, you can actually run your script.

 

 

Meanwhile, though, as long as we're in tutorial mode...

 

The other main ways of setting up an Exit Loop condition are:

 

Go to Portal Row [Next, Exit After Last]

 

A portal is a "window" from one table to another. Like an invoice that has a batch of individual Invoice Line Items. You can have a loop that does stuff to each portal row, goes to the next portal row, does stuff, goes to the next portal row, etc, then when there are no more portal rows, the loop exits.

 

 

and...

 

Exit Loop If

 

 

This one lets you specify a calculation. As soon as the calculation rings true, the loop exits, because you told it to:

 

Loop

Set Field [some Table::Field]

Exit Loop If [some Table::Some Other Field = "Stop when you get to this value"]

End Loop

 

 

or to give a practical example:

 

Loop

If [Table::KeepFlag = "Do Not Delete This Record"]

.. Omit Record

Else

.. Delete Record [no dialog]

End If

Exit Loop If [ (Table::KeepFlag = "Do Not Delete This Record" and Get(RecordNumber) = Get(FoundCount) ) or Get(FoundCount) = 0]

End Loop

[freebord]

dude you are AMAZING!

 

So if I dont make a 'set field' command for other fields, does that mean it will not transfer that content at all?

 

As in:

 

I'd like the adresses entered into the address field if no address exists- but is there IS already an address in the old-data address field, I dont want it adding to the data and creating new data. It's fine if the new address replaces the old address, but I just dont want it to add to the address field (like I want the comment field to be able to do).

[AHunter3]

Unless you want to totally IGNORE the "orders" (NEW FILE) version of info for any given field, you have to do a Set Field for that field. It won't overwrite any existing data, the way these are set up, they'll just add data if that field is currently EMPTY in the old data set.

 

PS: if you want the Comment field to ADD TO the existing Comment field, not overwriting it but also not leaving it untouched if there happens to be something in it already, you need to do something slightly different for the Comment field in your Set Field script step:

 

Set Field [Customer::Comments; Customer::Comments & Case ( Length (Customer::Comments) * Length (Orders::Comments) > 0; "¶") & Orders::Comments]

 

 

what that does: set it to itself (what it already is, in the old existing data set); then it multiples the LENGTH of the old time the LENGTH of the new. Anything times zero is zero, so you only get a value greater than zero if both fields (old AND new versions of Comments) actually have content in them; if they both do, i.e., if the product of the two lengths is greater than zero, then stick in a hard return ("¶"); then, finally, the Orders::Comments field.

 

result if old Comments field is empty and new (orders) comments field has stuff in it: new comments field

 

result if old Comments field had data in it and new (orders) comments field is empty: old comments field

 

result if they both have data: old comments field, hard return, new (orders) comments field.

[freebord]

AHunter3, u r awesome. theres no way i would have been able to do this without your help.

THANK YOU!!!!!!!!!!!!!!!!