Global Addition Import

[Dezza12]

I have a database of 11,000 records, and would like to update a checkbox field for around 4,000 of those records. We exported all records to an excel file and then pared down to the ones we wanted to update. I would like to globally add the check by importing if possible...is this possible?

We are using FM 4.1v3 for Mac

[ September 27, 2001: Message edited by: Dezza ]

[LiveOak]

It kind of depends upon what you mean by "update checkbox". Since checkbox field can have multiple values, you can't just do a replace after you locate the reocords to be updated, this would wipe out the other selected values in all the found records. Remember, a checkbox field is stored as a text field with multiple values separated by carriage returns. But first, how to locate the subset of the 11,000 records to be updated.

Create a new FM file by dropping your excel file on top of FM. I'll call this file "FromXL". Build a relationship between FromXL and your exisiting file (I'll call it "Main"). I hope you maintained some sort of unique identifier (Record ID?) in the excel spreadsheet or you're pretty much doomed. If you did maintain some unique way to connect FromXL to Main, use it to build a relationship (I'll call it "RelUpdate").

In the FromXL file, create a script that includes the step:

Go to Related Record (RelUpdate, show only related records)

This will find the subset of the 11,000 records to be updated.

Now, how to change the values in the checkbox field. To check or uncheck a value without altering the other values checked, you will need to use a looping script and a Set Field script step. Exactly what the parameters of the Set Field look like depend upon exactly what you are trying to do (check or uncheck) and whether the value may ALREADY be checked in the subset of records.

Say you wanted to check the value "Dog" in a field "Checkbox" in a found set of records, the script might look like:

Go to Record/Request/Page (first)

Loop

Set Field(Checkbox, If(Pattern Count (Checkbox, "Dog"), Checkbox, Checkbox & "

[Dezza12]

Thanks for help, I'll create a copy and give it a try.