AnsweredAssumed Answered

How do I write records from TableA to TableB

Question asked by MikeTV on Jan 28, 2009
Latest reply on Feb 1, 2009 by MikeTV


How do I write records from TableA to TableB


Hi all.


I am an experienced DataEase (for DOS) user who is in the process of evaluating Filemaker Pro Advanced as a potential replacement for DataEase.


So far, I very much like what I see of Filemaker. Despite one being a DOS product & the other being Windows/Mac, there is a fair bit of similarity in how they both go about things. The idea of a form & table being created in one hit is conceptually much the same & many of the Filemaker function & script commands are very similar to the DataEase equivalents. Much of the syntax isn't a million miles away either.


I am at the stage where I am recreating an existing, very simple DataEase application in Filemaker as a means of starting to get up to speed. This application analyses telephone bills using data downloaded from the telephone service provider (e.g. British Telecom). Although simple, the application contains a number of basic procedures & techniques that are commonplace, so it's a good start point.


In general I'm getting on fine, but have stubbed my toe on one important principle that I haven't discovered a straight forward means of achieving yet. This is to be able to write records from one table to another, sometimes selectively, whilst transmogrifying some of the information on the way.  


To explain further: 


Essentially, the application contains three tables PHONE_DIRECTORY, CALL_LOG & NEW_CALLS.


PHONE DIRECTORY stores one instance of the each known telephone number along with other relevant data (e.g. PersonName, Town, BusinessOrPrivate, etc). Records are unique on a field called AreaCodeTelNumber.


CALL_LOG stores records for each individual phone call made and has no uniqueness (none needed). As well as the telephone number (AreaCodeTelNumber), it stores date, time & cost of call, plus some other stuff.


These two tables are related, one to many, on the AreaCodeTelNumber field.


The third table, NEW_CALLS, is used to temporarily contain fresh imports of the most recent itemised telehone bill data, which I download quarterly from the Service Provider. This is in a pre-existing CSV format determined by the provider. This data is then used to populate the other two main tables.


What I want to do is this:


1: Clear NEW_CALLS of all data (know how to do this OK)

2: Import CSV file into NEW_CALLS (know how to do this OK)

3: For each record in NEW_CALLS test to see if phone number is already in PHONE_DIRECTORY:

    3a: If it is, do nothing and go to the next record

    3b: If it isn't, write a new record in PHONE_DIRECTORY to add the new number

4: For each record in NEW_CALLS, write an equivalent new record in CALL_LOG.


Where new records are created in steps 3b and 4, some fields will contain a straight copy of the data from NEW_CALLS, others fields will be 'transmogrified' in some way by using Filemaker functions (e.g. use distinctive patterns in the AreaCode data to create a flag to show what type of call it is - Mobile Phone, Freecall, Overseas, etc - I know how to do this bit OK).


At present, I have 95% achieved what I want by using imports from NEW_CALLS to the other two tables, but this is really an unsatisfactory workaround and is not giving me as much control as I want.


What I am after is the syntax for two scripts which essentially do (in laymans language) the following:


Script1 :


  For each and every record in TABLEA

          Write a new record in TABLEB

                   Field1 = Field1

                   Field2 = Field2

                   Field3 = Field3 

                   Field4 = Data derived by function 

           End (after last record in TABLEA has been processed) 



Script2 (more complicated, as it is seeking to only add new non-matched records):


Create a temporary relationship where (TABLEA MatchField = TABLEB MatchField)

For each and every record in TABLEA


            Matching record in TABLEB exists


             Do nothing


             Write a new record in TABLEB

                   MatchField = Matchfield

                   Field2        = Field2

                   Field3        = Field3 

                   Field4        = Data derived by function 

            End (after last record in TABLEA has been processed)




In DataEase, the 'script' would actually look very like what I have written above, but I haven't found an equivalent 'Write new record in specified table' command yet. I suspect that the 'Loop' 'Copy' & 'Paste' functions come into it, but the solution isn't staring me in the face yet.


I am sure that my initial unfamiliarity with FileMaker is almost certainly making me miss the bleedin' obvious, but I could do with some help here.


Many thanks in anticipation.....