1 Reply Latest reply on Mar 7, 2010 5:14 AM by LaRetta_1

    copying a field between tables



      copying a field between tables


      Hi everyone, total filemaker pro newbie here. If you could help me out with this script I'm working on, I'd really appreciate it.


      My basic structure is this: I have 2 tables in a database, one with fewer records (table 1) than the other (table 2). Table 2 may or may not have a duplicate entry in table 1. What I want to do is to copy a certain field (let's call it the "special identifier" field) from table 2 into table 1 if there happens to be a duplicate. I have created a unique ID for each record in both tables. Should there be a duplicate entry, those ID's will be the same. What I am having problems with is writing the script that will do the following:


      For each record in table 2, search through table 1 to see if the unique ID's match. If so, copy the "special identifier" field from table 2 to table 1. I'd like this to repeat until every record in table 2 has been matched with table 1, and at the end, table 1 will have some entries with that "special identifier" field.


      Does this make sense? I spent the last 2 hours trying to figure out how to do this. If anyone can help, you will totally make my weekend. Thanks in advance!


      edit: sorry forgot to say I am using Filemaker pro 10

        • 1. Re: copying a field between tables

          "Should there be a duplicate entry, those ID's will be the same."


          Without knowing whether this is a one-time thing or ongoing process, without knowing what these tables represent and without knowing why you have duplicate information, I can't really suggest the best approach.  But no matter the reason, you will need to create a relationship between them in your graph as:


          Table1:: ID = Table2:: ID


          Then just place that 'special identifer' from table 2 directly on your layout from table 1.  There is probably no need to duplicate it in Table1 (but I don't know why you have duplicate in two tables to begin with).  If you really must place that special identifier in table 1, then you can easily do so after they are related (as described above).  Some various approaches to consider:


          1) Manually: You can place table1's special identifer field on a layout from Table 2.  Show all records in table2.  And then place your cursor in table1::special identifier and run Replace Field Contents (by calculation) and the calculation would be:  table2::special identifier.


          2) If this is regular process, don't place table 1's special identifier field on table 2's layout and instead script it:

          Go To Layout [ layout based upon table 2 ]

          Show All Records

          Replace Field Contents [ without dialog calculated result ; Table1:: special identifier ; table2:: special identifier ]


          3) After they are related, set Table1:: special identifier as auto-enter Lookup to table2:: special identifier then all future records will automatically look up the value.  To bring the current records up to date, perform script :

          Go To Layout [layout based upon table 1 ]

          Show All Records (or only find records without special identifier value?)

          Relookup Field Contents [ table1:: ID ] *


          * keep in mind that this will relookup ALL of your fields in table 1 which have auto enter lookups.