Move Fields Between Tables

[Hawkcode]

Hi,

I am looking at trying to convince my boss to move our associations data base application to File Make from MS Access.

I'm developing on a Mac but is they do it it will be on Windows.

Well this database is a mess. Before I got there 4 years ago the Boss the Executive director modified things.

Anyway I just started looking at scripting and working with FM is a whole new mind set.

I will need to move some fields out of 1 table into another to better normalize the Data.

I was only able to get it to work by creating a variable. Isn't there a way to referance the data in one table and put that into another table without using a variable?

I can't figure out how to copy the code...so here's the gist:

Got to Layout of Table1

Sort Recs

GO to first rec

Loop

set var from Table 1 Firls

enter browse mode

goto layout table 2

New Rec

Set field from the Var

commit rec

goto layout table 1

go to next rec exit if last

end loop

Can any one steer me in the right direction.

And how can you copy and paste code?

Thanks

Rich

[Ocean West]

is this a one time operation or a task that has to happen multiple times?

One option is

go to layout1 (table 1)

find

sort

open new window

goto layout 2 (table2)

import records from table 1 matching names.

if it a one time issue just to normalize your data you can just import the table one in to a new table creating a new table then just delete the un needed fields from the newly crated table.

[Hawkcode]

This is a one time thing, but it can't be done on import. I will have import to temp tables, then go through the temp table and pull out fields 1 at a time and drop them in the proper tables.

It's not the entire table but only parts.

I suppose the other option is to do it in Access as I know SQL very well, but this is something I need to learn how to do in FM.

Thanks

Rich

Edited April 6, 2008 by Guest

[aldipalo]

How many fields are you talking about?

Are you talking about moving the fields or the fields and the data within the fields?

If the later and it is a one time thing, can't you transfer your access data into an excel spreadsheet? Then let's say you wanted to have 3 separate tables in FM, as an example:

Names

Addresses

Contact Info

Set up your 3 tables in FM with the fields you want:

Names:

Prefix

Fname

Lname

Suffix

Address:

Address

City

State

Zip

Contact info:

blah

blah

When importing from excel only map those fields you need in each table as well as the "ID" field which goes into each table. Now you'll have your data separated and related.

HTH

[Hawkcode]

I see what you mean. With the way FM can do selective importing that could work.

But I still need to know how do this. Another scenario is where I pull data from a bunch of different sources to make essentially a flat file CSV to import to a web app.

There has to be a way to do this with out variables, or is there.

Rich

[Fenton]

If you could be more specific about an operation, we could give more useful advice. We often have to import data from different sources, Excel files, csv, etc., often with all kinds of nasty little problems (multi-valued fields, different fields, duplicate data, etc.).

What I often do, for one-time operations, is to open the source data with FileMaker; it can open Excel, csv (though check the # of records match the # of lines), tabbed data. Then parse out any multi-value fields, etc., to get the fields for your real FileMaker file's table(s). Check for internal duplicate data. Create relationships to the FileMaker file, to check for existing records (maybe you don't even have that yet).

With FileMaker Pro Advanced, you can just copy/paste entire tables (in Manage, Database, Tables). You can also selectively copy/paste fields (Fields tab). Hold the Shift key to select blocks of contiguous fields. Hold the Command key to pick and choose.

Or you can choose the option to create a new table on Import (bottom of the drop-down list of target tables, top right of dialog); that creates the table on the fly, and brings in the data in one operation; though you'll likely want to rename the table afterwards. It will bring in all the fields (columns) of the source however.

In either case, when you Import pick the table and the fields you want. If you are converting a "flat" source into a (better) relational tables structure, one method is:

1. Create a serial ID in the source data. Show All Records, Replace, with auto-enter serial to populate the field.

2. Import the main data fields into the real file's table first. Bring in the source serial ID also, into a temp field.

3. The main table should have its own serial ID which populates on import.

4. Go to the source file. Using a relationship based on the Source serial=::Main temp serial, Replace the Main file's serial ID into the source file. Now you've got the real ID in the source table.

5. Import data from source into main file's other tables. Import the real parent serial ID as a foreign key. Relationships from the main table to its children will work.

In short, get the real serial ID into the source data, so it can be imported into child tables. Maybe you've already got a good serial ID for your records and a good structure; in which case you can ignore the above :-]

Edited April 6, 2008 by Guest

[Hawkcode]

Ok here it is in sql terms as this is what I understand the most.

Insert into TableA (Field1, Field2)

Select Field11, Field12 from TableB

Where TableB.ID = 23423;

So moving Field11 and field12 to TableA from TableB where the TableB.Id field is 23423.

Big differences in the paradines!

I hope this helps. This may not be a one time thing. Just want to know how to script that.

Rich

[mr_vodka]

If you create a global field you can use that to temp store the ID number that you want to find each time for your script. So lets say that you have already set the global field (gID) to 23423. Then you can use:

Go to Layout [TableB]

Enter Find Mode []

Set Field [ ID; gID ]

Perform Find []

Set Variable [$f1; Field11 ]

Set Variable [$f2; Field12 ]

Go to Layout [TableA]

New Record

Set Field [Field1; $f1]

Set Field [Field2; $f2]

Commit Record []

If you wish, an alternative to using a find, you can also create a new relationship keyed on the gID to the ID field and use Go to Related Record []

[Hawkcode]

John,

Thanks, this is what I was looking for. It is sure a different mind set from the way I would do it now.

I will have to think a little different.

Rich