8 Replies Latest reply on Jan 23, 2010 5:24 AM by drushton

    Moving records between tables



      Moving records between tables


        I have a file containing a layout, (Machine Parts) and multiple (Drawings) layouts. When finding records (Parts) in the (Drawings) layouts each record is given a unique number corresponding with the drawing so the find is singular. When finding records in the (Machine Parts) layout, the record is found by information and may return multiple records. I would like to pick a single record from the layout (Machine Parts) along with records from multiple (Drawing) layouts and copy them to a new layout (Parts Request). When the records are in the (Parts Request) layout additional information fields were added. Two check boxes were added (Part Issued), (Stock Out) and a button Request Complete. After the record is complete, the button "Request Completed" is clicked. The records are sent to two new layouts, (parts Issued) or (Stock Out) depending on which check box was picked and the (Parts Request) Layout is cleared. I have created tables for the layouts Machine Parts,Parts request, Parts issued and Stock out. I thought it would work similar to a internet shopping cart but I am having trouble getting it to work without corrupting the original Machine Parts layout or not moving the records correct.


                                                                               Thank You 

        • 1. Re: Moving records between tables

          So you have tables in a single database

          The tables are

          Machine Parts  that is related to XXXX


          You speak of layouts when I would expect Tables.

          How many Tables do you have?

          How many Fields do each table have?

          How are the Tables related?

          You can do screencaptures and post them on free imagehosting sites and reference them here. 

          • 2. Re: Moving records between tables

            To expand on davidanders' reply.


            Instead of sending records to a "layout", you'd base your layout on the parts table and write a script that uses the information on the current record displayed on the first layout to find the same or related record and displays it on your second layout. Exactly how you'd do that depends on how you've designed your database tables and related them.

            • 3. Re: Moving records between tables

              Thank you for your interest. I am trying to learn FMP 9 PC version from my previous experience on FMP 2.0 Mac. Enough said. What I have done is started with a table Machine parts which includes drawings with a single find return, a find screen by description with multiple returns, a input screen that collect all information and a main menu directing you through. Machine parts is related to a table Lookup reference which I use to auto enter information into the Machine parts input layout screen. My next table is Parts request. This Table has a single layout, parts request. It is related to Machine parts by part number. It contains the layout that I am trying to copy the records into. At this point I am having trouble picking from multiple records and making sure I do not corrupt the original Machine parts table. Next I have tried to relate another table Parts issued to Parts request by a pop up box (answer yes) and then part number to move the record. The same relationship between table Stock out and Parts request a pop box (answer Yes) and then the part number. Each record on the Parts request table would have these two choices. I am trying to get each record that is on this layout to be moved to either table Parts issued or Stock out. A button which would run a script to check that all information is filled out and move the records to the desired table before clearing the table Parts request for the next request. Parts issued and Stock out tables need to retain all records. Maybe I need to tie everything together with scripts? Hope fully you can see my intent so any suggestions or ideas for a different approach would help and be greatly appreciated...... David 

              • 4. Re: Moving records between tables

                OK, a basic change in approach will do what you want in terms of seeing the information you want on the screen but will be many, many times safer for your data.


                Don't move the actual records from one table to another. Instead use the part numbers of your fields to make entries in your other tables that log them in and and out and reference drawings etc.


                You can use the advanced search link above, to find a number of discussions on creating and using an Inventory Log to determine inventory levels and document all inventory changes (such as checking items in and out). Just search for the key words "inventory log" and you find several such threads.


                To briefly describe this method, you'd set up a system that works like your check register in your check book. Each time you recieve a part in inventory, you add a new line that logs the part number and the quantity recieved (like a deposit) by entering that number in the "recieved" field. Each time you remove a part from inventory, you make the same type of entry, but enter the quantity removed (like a withdrawel) in a "removed" field instead. A combination of calculation fields, summary fields and how you sort the records enables you to tell the total on hand for each item.

                • 5. Re: Moving records between tables
                         Thanks again for your reply.. I am going to use the advanced search as you said as well as utube to find a way to proceed. I originally had the database setup for the requester to write down the parts needed and then have the attendant type in a input screen the parts requested but I was hoping to eliminate that step and add the stock out table to be able to streamline the process and red flag the issues that may need attention. I am not good with scripting but am trying to learn. I am exploring the possibility of coping the records when asking for a request to a new file that will separate the records at that point assuring the integrity of the original records. Since the Machine parts table will be basically for reference the new file may be the answer for reports and information that will be dated,where used, etc.. and then stored in that criteria. Am I on the right track? Any scripting ideas? Start over at the basics maybe..... Thanks again David
                  • 6. Re: Moving records between tables

                    I don't think so.


                    What I am suggesting is that you do, not, copy, Records from one table or file to another for any reason other than to provide back up copies of your data. It's much simpler to simply work with ID numbers that unquely identify each record. In your case this would seem to by your part numbers. If you need to record the fact that a part was removed from inventory, you don't copy over the part name, description, drawing etc, you simply record the part's number. You let the database do all the heavy lifting for you by defining relationships so that the database can use that part number to pull up all the info you want from the original table just by looking it up.

                    • 7. Re: Moving records between tables
                         I will try working on the relationships between the tables. Also incorporate primary and foreign keys to allow each record that may be modified to be unique. Thanks again  David
                      • 8. Re: Moving records between tables
                            Just reading over your reply again and I am trying to write a script that moves the records thats been assigned a indentifing number. Reworking the relationship between the tables so when the modified record serves it usefulness it can be deleted without cahnging the original records....breaking the database into smaller segments I may can digest. Learning from self teaching, advice and trial stays with me when a part does come together. Mainly thanks again for your help. David