Jump to content
clivemas

Copy records from 1 table to another

Recommended Posts

clivemas

I have 1 table that contain records and want to copy some of them to a new table. I curently copy 1 record at a time (to 3 global fields) and then paste it to the second table. Once I isolate the records in a found set (say 3 fields, 20 records) I was wondering if there is a way to copy the entire found set to a new empty table field for field in one shot (like in Excel)?

Share this post


Link to post
Share on other sites
Robert Schaub

2 ways.

 

1] Import the records from table 1 to table 2

map the fields to import

 

OR

 

2]

A]Create a relationship Table 1 to table 2

B] In table 2 define field to lookup fields in table 1

C] Either move them 1 at time by pluging ID number into related field

in table 2 or create a loop script to do all record in the found set.

 

1 is easier only make sure in the import auto entry features option is clicked on....Or else ID's and serial number won't work.

Share this post


Link to post
Share on other sites
clivemas

Thanks Chopper, Import works great.

Share this post


Link to post
Share on other sites
LizK

To move files from one table to another in the same file do you have to export than import or can you do something like copy all records then paste in the other table???

Share this post


Link to post
Share on other sites
rdal

I have a similar problem. FileMaker NEWBIE here. I really need all the help I can get. I have been to several FM fora and FM eGroups. And so far, no luck.

 

Ok, for starters, I am working on my very first FileMaker project. I want to create a simple inventory system which will track all "stock movements" in my warehouse.

 

I have basically, the following as my "Reference Tables":

 

1. Transaction Type Header - this table contains the following information

 

ID Description

--------------------------

001 Cash Sales

002 Cash Purchases

003 Creadit Sales

004 Credit Purchases

005 Returned Goods

006 Borrowed Goods

007 Spoiled Goods

008 Goods for Donation

009 Goods for Consignment

etc.

 

Simply put, this table contains all the "possible" causes of my "stock movement", in or out of the warehouse.

 

2. Transaction Accounting Entries - this contains the accounting entries for each Transaction Type. Sample Contents would

 

be:

 

ID Account Code DR/CR %age

---------------------------------------------------

001 (Cash Sales) Inventory DR 100%

001 Cash on Hand CR 100%

002 (Credit Sales) Inventory DR 100%

002 Accounts Payable CR 100%

 

I am not an accountant so I am not sure if the entries above are correct. But I hope you get the picture.

 

When I create an "Incoming Document", I ask the user to supply the Transaction Type (via a Dropdown List), and when a TranType is selected, I want to copy the contents of the Transaction Accounting Entries table into a separate table, where I can do the computations, and Add/Edit/Delete the Accounting Entries.

 

I am stuck. I really need your help.

 

Thanks a lot.

 

Danny

Manila`

Share this post


Link to post
Share on other sites
Josh Ormond
To move files from one table to another in the same file do you have to export than import or can you do something like copy all records then paste in the other table???

 

No. Just import and select the originating table as the data source, and the second table as the target.

 

But a bigger question, is why duplicate the data to another table. You may have good reason for it, but that always raises a red flag regarding the db design.

Share this post


Link to post
Share on other sites
AHunter3

rdal — For what you are describing you should not have to import or export any data at all. You already HAVE the data. It's already IN FileMaker. With rare exceptions, it is a bad idea to duplicate your data in another table just to "do something different with the same data".

 

You should do your math on the records already being created. You have not described your structure or your desired calculations etc in sufficient detail for anyone to tell you exactly how to do them, but under the assumption that inventory is inventory...

 

You either already have, or need, a table in which each record is the record OF a specific type of item; not one exact physical item like that particular red shovel which you can pick up and put over your shoulder but of SKU# 12345 which is a red shovel of a specific length made by a specific manufacturer, etc.; in this table, SKU #12345 would only appear once. It would have a relationship to whatever table holds information about your inbound (i.e. purchases from the vendors you deal with, or shipments from the parent company, however you acquire your stock), and you would sum up all quantities of those inbound records and the result is your IN STOCK before you subtract the outbound. Then you would have a relationship to whatever table (which may or may not be the exact same table as the inbound) contains your outbound records (i.e., sales or other mechanisms by which items leave your warehouse). You sum up the quantities via that relationship and that's your total decrement which you subtract from the total inbound and the difference is the actual IN STOCK.

Share this post


Link to post
Share on other sites
Jack Rodgers

One good reason for exporting data or importing into another file is to create a 'concrete' file that no one will fiddle with. The big cheese can select his own hidden script to export the eom data to a secret file so he can see later on if anyone is altering the data file. This is a hush hush idea so don't tell anyone.

Share this post


Link to post
Share on other sites
sim2ple

Good Afternoon Everyone,

 

yet another Newbie who has a similar problem.

 

I am working on a database that will use an existing SQL table as its base. This table is a list of current employees.

 

However, I need I would like to enter additional information for every employee.

 

My idea, have one table with the base information from the SQL database and have a second table which only mirros the Employee ID number with the additional information.

 

Table 1 (from SQL)

EE_ID

Name

Street

......

 

Table 2:

EE_ID

Picture

Internal Rating

....

 

I have established a relationship between the two EE_IDs but now I need to make it so that table two creates automatically the number of entries that table 1 has.

 

Thanks,

 

S

Share this post


Link to post
Share on other sites
AHunter3

You can't make it "just happen automatically" but you can run a script that generates the table 2 record for each table 1 record that doesn't already have a table 2 record.

 

Or you can just make a table 1 LAYOUT and put the Table 2::Picture and Table 2::Internal Rating fields on that layout; enable record creation via your relationship on the Table 2 side and vóila (or is it vòila, I always forget?) you've simultaneously entered the rating / inserted the pic and also generated the Table 2 record on-the-fly.

Share this post


Link to post
Share on other sites
mikey123
and vóila (or is it vòila, I always forget?)

 

Neither. It's voilà.

 

I always wondered if one day I would finally know something that you didn't. I thought that day would never come. ;)

Share this post


Link to post
Share on other sites
sim2ple

thank you very much, makes sense, I have to get it in my head that in filemaker you don't need to create records that you don't need, like you have to in xls. Thanks!

Share this post


Link to post
Share on other sites
johnlaw

I have an application running Software QA scripts. (By the way, I'm using the word "scripts" here to refer to QA process, not Filemaker "scripts")

 

In it there is a table called "script_library" which represents each test that can be run. The script_library has a one-to-many relationship to the steps for the script stored in a related table called "script_library_lines". F or example, the "Log In" script in "script_library" has 10 related steps/records like "1. Enter your user name", "2. Enter your password", "3. Click Login", "4. Confirm entry", etc.

 

There is a another one-to-many relationship between a table called "test_header" and a table called "test_lines". These are used when the operator executes one of the test scripts:

1. The QA operator selects one of the entries from "script_library" table.

2. A new "test_header" record is created and the recordID from this new record is set in a global field (could be a variable but I don't trust them...)

3. from the script_library_lines based on the script_library record that was selected.

4. The script_library_lines are imported to the "test_lines" table, with the parentID being replaced by the test_header recordID that was captured in the global.

 

The QA operator then has a fresh set of steps/records pulled from the template, now related to "Test" that they can pass/fail, comment, etc. That's the reason I'm trying to duplicate records...

 

The problem is that the web client does not allow import. What?! Even from table to table within the app? Frustrating.

 

So how do I generate a duplicate set of records and insert a new parent keys? I've tried going to the related records in "script_library_lines" table and duplicating them one-by-one, inserting the new test recordID from the global as I go, but it seems cumbersome and unreliable.

 

Is there a better way?

Share this post


Link to post
Share on other sites
AHunter3

Two relationships, one hardwired to the special key that anchors the template steps, one representing the current test-taker's current session.

 

Set Variable to primary key of current test-taker record (or question or test or whatever the heck it is) then go to related records (template childrecords), set different $variable as specified record to go to, to starting value 1, loop, go to record [calculated value, $variable], dupe, set foreign key to primarykey $variable, omit, set different $variable to itself + 1, exit loop when specified record number exceeds found count.

Share this post


Link to post
Share on other sites
johnlaw

Thanks. I did get it to work, I'm not sure I did exactly what you described...The key thing you gave me was the Omit. When I duplicated, the new record shows up at the bottom - so I omitted the new record, went back to the first record (now duplicated) and omitted it a well. So the loop basically always started from the first record in the set. Thanks again.

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.




×
×
  • Create New...

Important Information

Terms of Use