10 Replies Latest reply on May 21, 2009 8:55 AM by philmodjunk

    Moving data from one table to another

    barfaz

      Title

      Moving data from one table to another

      Post

      I have a database with details of a large number of individuals (upwards of 100,000), each uniquely identified by a 6 digit number and two other fields. Each week I enter another 100 individuals with date of birth etc. In subsequent weeks I have to enter a lot of additional data about those individuals that comes from laboratory tests identified by dates and screen numbers. I have previously run this application in Dataease, and found the easiest was to enter the lab tests in a new Lab results table, so that I could copy all the data about the date, screen number etc from record to record while simply changing the last digit of the identifying number and the new results,if necessary. I then ran a script to enter the screening data into the matching records of the main file, and to delete each matching record in the Lab results file.

      I can't work out how to write a script to do this in filemaker ie to find a matching record in the main table, enter the data from the Lab results table in the appropriate fields, then return to the Lab results table and do the same with the next record.

       

      I would also like to recursively find sets of records related to each record in the main file and then number them according to date entered, but I can't work out how to get the find command to work in a script without it stopping and asking me for data in the appropriate fields.

       

       

       

        • 1. Re: Moving data from one table to another
          philmodjunk
            

          I'll answer your specific question first and then comment on the more general issue of what you are trying to do.

           

          "I can't work out how to get the find command to work in a script without it stopping and asking me for data in the appropriate fields."

          In your script editor, when you add "enter find mode" to your script, it's automatically added with the "Pause" script option selected. You can clear this selection by unchecking the check box at the bottom of your script editor. Now your script will enter find mode and move on to the next step in your script with out pausing for user input. You can then use script steps such as Set Field to specify find criteria and use the "Perform find" script step to find your records once you have specified all your search criteria.

           

          "I then ran a script to enter the screening data into the matching records of the main file, and to delete each matching record in the Lab results file."

          This strikes me as a needlessly complex approach. Using the right key fields and a relationship, you should'nt need to do this kind of "double entry". Granted, I don't have the full picture of how you've defined your tables and why you believe this is necessary, but it's very likely that you can avoid this step entirely with a fairly modest amount of table re-design.

           

          • 2. Re: Moving data from one table to another
            barfaz
              

            First - about find mode in scripts: Yes, I know about the don't pause option, but when I go into find mode and then try to get the script to enter values from the related table and run it with the debugger, it does not put any values in and then gives me an error message. If I set up variables such as $mom and set them to the appropriate values, it writes "$mom" in the field rather than the value just assigned to $mom, and then gives me an error message. I think the problem is that I don't understand the differences between the different script commands such as "replace field value", "set field" etc etc and I must say that the explanations are not very helpful. I would find it much easier if there were actual examples of which command is used in which circumstances.

             

            About the second question, the database in question is a mouse breeding database. The mice have complex names in 3 fields eg "-D" "BR"
            "64197" and are unique only with all three fields begin equal. Because the records go back 20 years (in Dataease) and relate to a lot of other data, I can't change the names retrospectively. When we enter the mice, they are usually in litters with many fields the same for each mouse eg 4 in a row will be males with black coats in cage 15597. All the litter information eg DOB, parents etc is in a related table with a unique "litter #" defining the relationship. So our procedure to reduce typing is to enter the first mouse in the litter eg 64197 -D BR etc, then duplicate the record so that the cursor is at the end of "64197". Then we delete the 7 and replace with 8 to enter thte next one. This is very quick.

             

            Now we need to do 2 or 3 different assays to sort out the particular genes in the mice. This happens at a different time and we need the original table filled so we can print out lists of what needs to be screened. The final gene results are kept  in a table related by the 3 part mouse name to the main file. However, lets say we want to enter the data into that gene file. For one assay, we would need to enter the name of the screen "transgenic screen #257", the date, which gene was screened etc, and then the result, plus the three part mouse name. This takes time for the first record but then, as for the main mouse file, we duplicate the record and change only the last digit of the mouse number and the screen result if necessary. But then when we do the second screen, we would have to find each individual record and type in "trangenic screen #54" and all the rest of the fields, then go to the next record and type it all again. Unless we could do a complex cut and pastte with several fields at a time, this would be very tedious. Instead, we are used to having a temporary table in which we repeat the entry process for the new assay, as it is much quicker to re-enter when using the duplicate record strategy, and then transfer the new fields to the other file. We could set up a complex chain of relationships to do this in portals, but there could be half a dozen different files involved, and we would have to have lots of portals. I was worried that this would be very confusing. I thought it might be simpler to run a routine to enter the new data in the related records instead.

             

            I will also have to do a similar data entry process from record to record in different tables in order to move the data out of Dataease into Filemaker. There are old records in Dataease that do not have related records in other tables, and I was hoping that I could write a filemaker routine that would find these particular records, enter a new record for each one in the linked table, and then put the appropriate data into the fields, some of which involve combining fields eg in the old records, "-D" was in two fields, "-" and "D, and the & function is very useful to turn them into "-D".

             

            Hope this makes sense

            • 3. Re: Moving data from one table to another
              philmodjunk
                

              You have Three significantly different issues: Why find isn't working for you like you want, the structure of your database, and methods for importing and "cleaning" the data from your current database. Each of these could be a completely different thread.

               

              I've only got time to deal with item 1 now. I'll check back tomorrow and see where you are with the others.

               

              "If I set up variables such as $mom and set them to the appropriate values, it writes "$mom" in the field rather than the value just assigned to $mom..."

              I suspect that you have a pair of quotes in the wrong place. When you set up your set field script in a script like I list below, make sure that you enter $mom and not "$mom" as the second parameter.

              Set Variable [$mom, table::field]

              enter find mode []

              Set Field [table::field, $mom]

              set error capture [on]

              Perform find []

              Set error capture [off]

               

              Set Variable is a good way to copy data from non-global fields into your find request. Once you enter find mode, it's as though you have a blank record and you can no longer get to data in fields in your current table or any other table. Thus, capturing any such data in variables is the typical way to make it work.

               

              • 4. Re: Moving data from one table to another
                philmodjunk
                  

                Now for the next issue. I get it that you are using duplicate record to avoid having to re-enter similar data for multiple records that are nearly but not quite identical. What I don't see is how that makes it necessary to do this in a temporary table from which you then move blocks of data into another table. You can do this using the Import Records tool, I'm just not convinced that this is necessary or a good idea.

                 

                "So our procedure to reduce typing is to enter the first mouse in the litter eg 64197 -D BR etc, then duplicate the record so that the cursor is at the end of "64197". Then we delete the 7 and replace with 8 to enter the next one. This is very quick."

                 

                You likely can define a serial number field for your key. When you duplicate the record, which can be set up with a button to do this in a single click, all fields will be duplicated, except the serial number field will automatically increment. This would be even quicker.

                 

                The third paragraph where you describe how you record your screen results is very confusing to me as I don't know exactly how you've structured your data tables.

                I'd have a "Litter" table that records all data common to the mouse litter. I'd have a "mouse" table that records all basic information about the mouse and a third "Tests" table where one record records the type of test and recorded data for one test performed on one mouse. Using this table structure, you could still use your method of filling out all the data by hand for the first mouse in the litter and then duplicating the record to start a new record for the next mouse to undergo the same lab test and not need to use a temporary table. There are even ways to automatically copy data from the previous record that can allow you to replicate the data from some fields and not others.

                 

                Keep in mind that all three tables (plus any others you might need) can be kept in a single file. You do not need a separate file for each table.

                 

                I, of course, am not familiar with the details of your lab process so you may have reasons for structuring your data differently.

                • 5. Re: Moving data from one table to another
                  barfaz
                    

                  Thanks for your advice about incrementing and file structure.

                   

                  Unfortunately we have a number of different series of mouse numbers incrementing simultaneously but all kept in the same table, so we can't have mouse # as an automatically incrementing number. This is to do with staff practices assigning mouse numbers in different rooms within the colony and I can't change it. I have a method that increments the previous mouse number so that data entry can start at different numbers, but when you start a new data entry and enter the first number, it automatically increments it so you have to remember to start by typing in number minus 1. The people who do our data entry find this confusing and tend to get it wrong. In future, we will have a routine that enters the numbers automatically, but this will only be for entering the mice, not for entering test results.

                   

                  As regards the tests file, the structure you have suggested is exactly as I have set it up (it's called the screening results table) BUT what do I do when multiple tests have to be performed on the same mouse? Entering the first test is easy, as you suggest, but it is the second and third tests where we can't just duplicate records, as the records for that mouse already exist in the tests table. So either I need further tables and further portals, or I need a data transfer method, which is what my original question was about. I would just like a routine that will find the matching record in another table and enter the data in defined fields.

                   

                  The problem is that I am having trouble finding the related records. If I set up variables that contain the match fields, then Enter Find Mode and have the following in the Specify Find Requests box

                   

                  screening records::mouse line: [$mouseline] AND screening records::mouse strain: [$mousestrain] AND screening records::mouse #: [$mouse#]

                   

                  then it types $mouseline, $mousestrain and $mouse# in the appropriate places, rather than typing the values of the variables themselves, and of course then the script stops. 

                   

                  If I say 

                   

                  Go to Related Record [Show only related Records; From table: "screening records"; Using layout "screen data" (screening records)]

                   

                  then it only works if all three match fields have data in them. But many of the old records have only mouse # and mouse line. I guess I could fill them all in with a fake strain. But I was hoping there would be an easier way.

                   

                  This brings up the general question - is there a way for relationships to recognise that when one of three match fields is blank in two related tables, then the records are still related? Or do I need to put some character in all the blank fields so that they can allow the relationships to work?

                   

                  "There are even ways to automatically copy data from the previous record that can allow you to replicate the data from some fields and not others." - how do I do this?

                  • 6. Re: Moving data from one table to another
                    philmodjunk
                      

                    "As regards the tests file, the structure you have suggested is exactly as I have set it up (it's called the screening results table) "

                    I'm not sure that you do have exactly the structure I describe. I'm describing a table where each test is a separate record. In that case, you can enter the data for the first test and then duplicate/edit to enter each subsequent test for that one mouse. It sounds to me like that is the structure of your temporary table, but it sounds like your screening results table has either multiple fields or repeating fields so that results from multiple screening tests are entered into a single record. If so, I strongly recommend that you change your table structure to one test result per record.

                     

                    "I would just like a routine that will find the matching record in another table and enter the data in defined fields."

                    If you insist:  Given the complexity of your data, instead of using a relationship and Go To Related Records, I'd take a look at using a script similar to the following:

                     

                    Set Variable [$mouse; yourtable::MouseIDfield]

                    Set Variable // keep using set variable to capture each value you need to find the record or records you want into a different variable for each field.

                    Go To Layout [Your layout name (Screening Results Table)]

                    Enter Find Mode[]

                    Set Field [YourTable::MouseIDfield; $mouse]

                    Set Field // keep using set field until you've entered all the criteria you stored in variables

                    Set Error Capture [on]

                    Perform Find []

                    Set Error Capture [off]

                    If [Get( FoundCount) > 0]

                      Import Records [//set up the options needed to copy records from one table to another here]

                    Else

                      Show Custom Dialog ["No matching records were found"]

                    End If

                    Go To layout [original layout]

                     

                    This is just a general outline, you'd have to customize it to fit your tables and fields. To handle situations where fields are blank, you may need to add code that adjusts the find process to handle the fact that some fields are blank.

                     

                    Your example:

                     

                    screening records::mouse line: [$mouseline] AND screening records::mouse strain: [$mousestrain] AND screening records::mouse #: [$mouse#]

                     

                    Doesn't make any sense to me. I'm surprised you don't get an error message after entering it. It might make more sense if I saw it in context. (is this the definition for a calculation field, an expression used in Set Field, or ?)

                     

                    "This brings up the general question - is there a way for relationships to recognize that when one of three match fields is blank in two related tables, then the records are still related? Or do I need to put some character in all the blank fields so that they can allow the relationships to work?"

                    We usually avoid this problem by putting a value such as a negative number that uniquely identifies all records where the original value is missing. Replace Field Contents can be a useful tool for modifying a field's value in multiple records at once.

                     

                    ""There are even ways to automatically copy data from the previous record that can allow you to replicate the data from some fields and not others." - how do I do this?"

                    When you use Manage | Database | Fields to work with your field definitions, double click a field to bring up its field options. Now click the Auto-enter tab. There are a number of options for automatically entering data into a new record that possible here. Some will copy data from the last record you edited. You can define a calculation that enters data drawn from another field. The Looked up value option can sometimes be set up for this as well. Many of these methods require setting up a self-join relationship that links the current record to another record in the same table. All of that is just a general description. If I were you, I'd look over these options and research them in the online help after resolving some of your other challenges.

                     

                    Final note for this post: You seem to be using "file" and "table" interchangeably. What version of FMP are you using? Are your tables all in the same file or in separate files? If you are using a fairly recent version of FMP, you can put all your tables in one file, which is almost always the better way to go.

                    • 7. Re: Moving data from one table to another
                      barfaz
                        

                      Let me explain the structure in more detail (and I apologise for sometimes using file when I mean table - I am new to filemaker and have previously used a database in which the tables were called files, so I am used to that terminology. I have everything in the same file, in different tables).

                       

                      The mouse table has mouse #, mouse line and mouse strain fields that uniquely identify the animal. These 3 fields form the relationship between this table and every other table linked to the main table, apart from litter number which uniquely links to the main table to the litters table where the DOB, parent info etc are stored.

                       

                      The screening table has been designed to hold the final information for the fields we screen for, for example mhc. But some mice don't need to be screened because we already know what their mhc will be, based on that of their parents, so we have their mhc already entered in the litters table. We have an mhc field in the mouse table that is a calculation field that looks up the litters table mhc. I need this field to then look up the screening table. Currently I have the calculation (on the advice of an earlier contributor)

                       

                      Unstored, from mouse records, = Case ( not IsEmpty (litters::litter #); litters::mhc; screening records::mhc)

                       

                      This finds the litters mhc but I'm not sure that it then goes on to find the screening records mhc. Do I also need the matchfield for the screening records table eg

                       

                      Unstored, from mouse records, = Case ( not IsEmpty (litters::litter #); litters::mhc; not IsEmpty (screening records::mouse #); screening records::mhc)

                       

                      You suggesting that I should have multiple records in the screening table for the same mouse. Then I need a much more complicated calculation for the mhc field in the mouse table. It will have to recursively go from matching record to matching record in the screening table, checking whether the mhc field has been entered. Could you please show me what the calculation would be?

                       

                      • 8. Re: Moving data from one table to another
                        philmodjunk
                          

                        Remember that I can't see your database and am unfamiliar with all the different screening test that you do. I'm imagining that most of your lab tests resolve to a test name and one or more numbers or text needed to record the test results. They might be as simple as

                         

                        MouseID, test name, Value1, value2, ...

                        23456,    mhc,           953245, 2345

                        23456,    xyz,            23456,  123.5

                         

                        Thus, you might have one field to identify the type of test and one or more data fields to store the values recorded. This may or may not work for you. I can't tell from here.

                        You could use a number of methods to extract specific test results for a given mouse without having "to recursively go from matching record to matching record in the screening table."

                         

                        That's why I went ahead and mentioned Import Records. This is a tool that can be used to copy records from your temp table to your screening results table should that turn out to be the best solution. It has a lot of options and can modify data in larger numbers of records in a single operation, so you'd want to read up on it's options and experiment with a copy of your file until you are confident you've got it working right for you.

                         

                        • 9. Re: Moving data from one table to another
                          barfaz
                            

                          I would prefer to do a real time calculation as then the data in the main table changes automatically if we change our screening result.

                          So, if I have multiple related records in the screen table, and I want the main table field to calculate a result based on the one with data in that field, how do I do it? Currently, I have the calculation field looking to see whether there is a value before screening (in the breeder pairs table) and then at the screening table, but it looks up only the first matching record and then stops. Can I get it to look at all the matching records to see if any of them have an entry in the field?

                           

                          This is my current calculation

                           

                           Unstored, from mouse records, = Case ( not IsEmpty (breeder pair records::mhc) ; breeder pair records::mhc; not IsEmpty (screening records::mhc) ; screening records::mhc) 

                           

                          This finds the screening data in the first matching record. If there is no screening data in the appropriate field in the first record, it does not search subsequent matching records in the same file, which do have data in the appropriate field. Is there any way to search through all matching records if the field is blank in the first one?

                          • 10. Re: Moving data from one table to another
                            philmodjunk
                              

                            With the example I gave, the "first field" would not be blank.

                             

                            In my example, If I wanted to see the mhc screening value, I'd use a relationship that linked to just the mhc labeled records for the designated mouse. Note that the name of the screening test is a text field in my example. Thus you can create a relationship that includes a field set to match to the test name field in addition to the fields that match in order to isolate data for a specific mouse.

                             

                            Please remember, I don't know if this will work for you in a way that's practical, you know your lab data collection needs and I don't. The method works if the data format for each collected data point is fairly similar regardless of the type of test. If they're not, we're just exchanging one level of complexity for another and using the Import Records tool to copy data from your temp table becomes a more practical option to consider.

                             

                            In your example, if you know that only one record has the data you want and all other records for that mouse have no data in that field, the Max() function can extract the desired value.   Max(screening records::mhc), for example, would return the largest value in the mhc field of all the related records in the screening records table.