7 Replies Latest reply on Feb 1, 2009 10:33 AM by MikeTV

    How do I write records from TableA to TableB

    MikeTV

      Title

      How do I write records from TableA to TableB

      Post

      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

                If

                  Matching record in TABLEB exists

                Then

                   Do nothing

                 Else

                   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.....   

        • 1. Re: How do I write records from TableA to TableB
          TSGal

          Mike TV:

           

          Thank you for your post.  Your description is very clear.

           

          My first thought is to create a link/relationship between PHONE_DIRECTORY and NEW_CALLS using the Phone field.  However, in the back of my mind, I was thinking of phone numbers that have different extensions, and/or phone numbers that may not be formatted the same way.  Hopefully, I'm reading more into this than I should, and you have that figured out.

           

          If you haven't set up a Relationship, pull down the File menu and select "Manage -> Database...".  Click on the Relationships tab, and you will see a graphical representation of the tables.  Connect the PHONE_DIRECTORY table to the NEW_CALLS table by clicking on one "key" field and dragging it to the "key" field in the other table.  Click OK.

           

          Next, go to the layout that displays "NEW_CALLS".  Pull down the View menu and select "Layout Mode".  This allows you to control the form/layout and place fields.  Along the top, there are several tools represented as icons.  About five icons from the right, there is a portal tool.  (When you place your cursor over each icon, a tooltip appears after 1-2 seconds).  The portal tool allows you to see records from a related table.  You draw a box with the portal tool by pressing down on one corner and dragging it diagonally.  When you let go, a dialog box appears asking for what table you want to display.  Select "PHONE_DIRECTORY".  After that is selected, you will be prompted for field names.  Choose one of the fields that always has data (other than the phone number) like "Name".  Once this is done, go to Browse Mode, and if any records exist in PHONE_DIRECTORY associated with the NEW_CALLS phone number, they will appear.

           

          Next, create a new calculation field entitled "NumRelated", resulting in Number, with the formula:

           

          Count ( PHONE_DIRECTORY:: Name )

           

          This counts the number of related records.  If there is one related PHONE_DIRECTORY entry for the phone number, then this will return 1.  If there are no related records, then 0 (zero) is returned.

           

          Now, you can search for this field for 0, and it will show all records in NEW_CALLS that don't have a record in PHONE_DIRECTORY.  Does that make sense?

           

          Next, switch to the layout that shows the PHONE_DIRECTORY information.

           

          Pull down the File menu and select "Import Records -> File" and select the database file that contains the NEW_CALLS information.  It can be the same file if you have the PHONE_DIRECTORY table and NEW_CALLS table.  Once selected, then select the table NEW_CALLS and match the field order.  Since you have a found set of records, only those records with a value of zero in "NumRelated" field.

           

          That should do it.

           

          You can put this into a script to automate the process.  The script step would be something like:

           

          Go to Layout [ <layout with NEW_CALLS information> ]

          Enter Browse Mode []

          Set Error Capture [On]

          Perform Find [Restore]    (note: the Find request is NumRelated = 0 )

          If [ Get (FoundCount) > 0 ]

             Go to Layout [ <layout with PHONE_DIRECTORY information> ] 

             Import Records [ No dialog; <file name that holds> ; <field mapping> ]

             Go to Layout [ <layout with NEW_CALLS information> ]

          End If

           

          -------

           

          That's it.  You can add an "Import Records" script step at the beginning to import from the CSV text file.  Also, at the end, you can add a "Show All Records" and "Delete All Records" to start fresh the next time you import.  I'll leave that to you.

           

          If you need clarification for any of the above steps, please let me know.

           

          TSGal

          FileMaker, Inc.

          • 2. Re: How do I write records from TableA to TableB
            MikeTV
              

            Hi TSGal

             

            Thank you very much for your extremely prompt & comprehensive reply.

             

            As it happens, I had already made use of the Portal and Count of Related Records functionality elsewhere in the application. My PHONE_DIRECTORY form has a Portal showing the related CALL_LOG records as a sorted list. It also has a field showing the number of calls made to each number, which uses the Count of Related Records functionality to provide the data. Both these functions have direct equivalents in DataEase, so they are familiar territory.

             

            I had, in fact, already been down the approximate route you suggest before posting my question on the forum. I had created a relationship between PHONE_DIRECTORY and NEW_CALLS based on my AreaCodeTelNumber field, then used the Import facility to do an 'add or update', ensuring that there were no instances where the 'update' part would overwrite fields with data I needed to preserve. I had wrapped the whole process sequence in a single script broadly along the lines you have illustrated.

             

            Your suggestion of using the Count of Related Records functionality in this context would add a further refinement in giving the ability to filter out any pre-existing phone numbers, effectively making the import an 'add-new' only.

             

            However, the main reason for posting my original question is that I was trying to avoid using the Import function. Although it works fine in the very simple scenario that my phone logging application presents, there will be many occasions where I will want much more control over the how the raw incoming data is processed. I am thinking of several typical client applications that I have worked on which, in some instances, may have 50~100 tables - some of which may contain a million plus records - and around 1000 scripts. In this sort of thing, the are many instances where a straight import procedure is too crude a tool to do what is needed when shunting data from one place to another.  

             

            There will often be the case where incoming external data needs to be extensively worked on before being amalgamated with existing data already in an application.  Alternatively, there might be a table whose data needs to be selectively split & posted to two or more subsequent tables. In these scenarios, I would want much more control down to individual field level when posting data to the receiving table(s) than an import procedure will give.

             

            This is where my request for an example script to write records from TableA to TableB comes in.

             

            I appreciate that there will be umpteen different ways to skin any particular cat with FileMaker, just as there are with DataEase. But this basic concept of writing data between tables is a 'must have' as, in my experience, the need to do it crops up so often. At the moment, my lack of familiarity with Filemaker means that the method of achieving it is just not staring me in the face.

             

            Any further help on this will be much appreciated. 

            • 3. Re: How do I write records from TableA to TableB
              TSGal

              MikeTV:

               

              Thanks for the clarification.

               

              Without using the "Import Records" command, you can replace the following:

               

              If [ Get (FoundCount) > 0 ]

                 Go to Layout [ <layout with PHONE_DIRECTORY information> ] 

                 Import Records [ No dialog; <file name that holds> ; <field mapping> ]

                 Go to Layout [ <layout with NEW_CALLS information> ]

              End If

               

              ...with

               

              If [ Get (FoundCount) > 0 ]

                 Go to Record/Request/Page [First]

                 Loop

                    Set Variable [ $match ; Matchfield ]

                    Set Variable [ $f2 ; Field2 ]

                    Set Variable [ $f3 ; Field3 ]

                    Set Variable [ $f4 ; <your "Data derived by function" for Field4> ]

                    Go to Layout [ <layout with PHONE_DIRECTORY information> ]

                    New Record/Request

                    Set Field [ Matchfield ; $match ]

                    Set Field [ Field2 ; $f2 ]

                    Set Field [ Field3 ; $f3 ]

                    Set Field [ Field4 ; $f4 ]

                    Go to Layout [ <layout with NEW_CALLS information> ]

                    Go to Record/Request/Page [ Next ; Exit after last ]

                 End Loop

              End If

               

              ------

               

              This should be fairly self-explanatory.

               

              If the number of records found is greater than 0, then we go to the first record of the found set and loop through the following.

               

              We store the relevant fields to variables (all variables start with a dollar sign), switch to the layout where PHONE_DIRECTORY table information is displayed, add a new record, and replace the fields with the variable information.  We then return to the NEW_CALLS layout and go to the next record and loop until we reach the last record and exit.

               

              I hope this helps.

               

              TSGal

              FileMaker, Inc. 

              • 4. Re: How do I write records from TableA to TableB
                MikeTV
                  

                Hi TSGal

                 

                I suspected the Loop function would come into it somewhere & what you are suggesting here makes absolute sense - will give it a go.

                 

                In this instance, it looks like FileMaker doesn't have a direct equivalent of DataEase's "Enter a Record" function, so I'll have to do a bit more work to achieve the same result here. However, it is already apparent that FileMaker has some very nice functions that will make other tasks a lot easier, so it's swings and roundabouts.

                 

                Once again, many thanks for your helpful response.  

                • 5. Re: How do I write records from TableA to TableB
                  MikeTV
                    

                  Hi TSGal

                   

                  Nearly there!

                   

                  I have done as you suggested, but ran into a problem where the incoming NEW_CALLS data does not have uniqueness on phone numbers.

                   

                  There may a number that has not cropped up prior to the billing period covered by this batch of NEW_CALLS, but this new number has been called several times during this billing period and therefore appears several times in the NEW_CALLS data. Where this is the case, I only want to add a single instance of each new number to my PHONE_DIRECTORY table.

                   

                  I managed to solve the problem by adding a repeat intance of the 'Perform Find [Restore]' step right at the end of the loop. This means that that the 'Count of Related Records' flag is refreshed before the loop processes the next record. As a result, any incoming records further up the table which have the same phone number as the one just processed are reflagged as 'already known' and are excluded from processing.

                   

                  Current script now looks like this:

                   

                   

                   

                  Go to Layout [ <layout with NEW_CALLS information> ]
                  Enter Browse Mode []
                  Set Error Capture [On]
                  Perform Find [Restore]    (note: the Find request is NumRelated = 0 )
                  If [ Get (FoundCount) > 0 ]
                     Go to Record/Request/Page [First]
                     Loop
                        Set Variable [ $match ; Matchfield ]
                        Set Variable [ $f2 ; Field2 ]
                        Set Variable [ $f3 ; Field3 ]
                        Set Variable [ $f4 ; <your "Data derived by function" for Field4> ]
                        Go to Layout [ <layout with PHONE_DIRECTORY information> ]
                        New Record/Request
                        Set Field [ Matchfield ; $match ]
                        Set Field [ Field2 ; $f2 ]
                        Set Field [ Field3 ; $f3 ]
                        Set Field [ Field4 ; $f4 ]
                        Go to Layout [ <layout with NEW_CALLS information> ]
                        Go to Record/Request/Page [ Next ; Exit after last ]
                        Perform Find [Restore] (REPEATED TO ENSURE NO DUPLICATES OF RECORD JUST WRITTEN)
                     End Loop
                  End If

                   

                   

                  Whilst this works, it is very inefficient as the Find function is invoked again for each record within the loop. Tolerable for small found sets, but not good for record counts of any size. Any suggestions for something better?










                  • 6. Re: How do I write records from TableA to TableB
                    TSGal

                    MikeTV:

                     

                    Try the following:

                     

                    Go to Layout [ <layout with NEW_CALLS information> ]

                    Enter Browse Mode []
                    Set Error Capture [On]
                    Perform Find [Restore]    (note: the Find request is NumRelated = 0 )
                    If [ Get (FoundCount) > 0 ]

                     

                       Sort Records [ Restore ]     (note: this would be sorted by phone number order)

                     

                       Go to Record/Request/Page [First]
                       Loop
                          Set Variable [ $match ; Matchfield ]
                          Set Variable [ $f2 ; Field2 ]
                          Set Variable [ $f3 ; Field3 ]
                          Set Variable [ $f4 ; <your "Data derived by function" for Field4> ]
                          Go to Layout [ <layout with PHONE_DIRECTORY information> ]
                          New Record/Request
                          Set Field [ Matchfield ; $match ]
                          Set Field [ Field2 ; $f2 ]
                          Set Field [ Field3 ; $f3 ]
                          Set Field [ Field4 ; $f4 ]
                          Go to Layout [ <layout with NEW_CALLS information> ]

                     

                          Set Variable [ $phone ; Matchfield ]

                          Loop

                             Go to Record/Request/Page [ Next ; Exit after last ]

                             Exit Loop If [ Matchfield ≠ $phone ]

                          End Loop

                     

                          Exit Loop If [ Matchfield = $phone ]

                     

                       End Loop
                    End If

                     

                    --------

                     

                    The first change is the remaining records are sorted by the phone number.  If there are duplicates, they will occur next to one another.

                     

                    After the record is updated, I store the Matchfield information into the variable $phone.  I then loop through the next set of records.  If i go past the end of the file, I exit the loop.  Otherwise, I check to see if the phone number has changed.  If not, then I loop back again and keep checking.

                     

                    Once I reach the end of the file, the variable $phone will equal Matchfield, so the script ends.

                     

                    Therefore, no need to keep refinding records.

                     

                    I hope this is clear enough.  If not, let me know.

                     

                    TSGal

                    FileMaker, Inc. 

                    • 7. Re: How do I write records from TableA to TableB
                      MikeTV
                        

                      Hi TSGal

                       

                      I found that a little bit glitchy, but it set me on the right road. Eventual solution was: 

                       

                       

                      Set Variable [ $Temp ]   (note: contains no information initially) 
                      Go to Layout [ <layout with NEW_CALLS information> ]
                      Enter Browse Mode []
                      Set Error Capture [On]
                      Perform Find [Restore]    (note: the Find request is NumRelated = 0 )
                      If [ Get (FoundCount) > 0 ]
                         Go to Record/Request/Page [First]
                         Loop
                            If [ $Temp ≠ Matchfield ]  (note: empty first time round, then reflects last record processed) 
                               Set Variable [ $match ; Matchfield ]
                               Set Variable [ $f2 ; Field2 ]
                               Set Variable [ $f3 ; Field3 ]
                               Set Variable [ $f4 ; <your "Data derived by function" for Field4> ]
                               Go to Layout [ <layout with PHONE_DIRECTORY information> ]
                               New Record/Request
                               Set Field [ Matchfield ; $match ]
                               Set Field [ Field2 ; $f2 ]
                               Set Field [ Field3 ; $f3 ]
                               Set Field [ Field4 ; $f4 ]
                               Go to Layout [ <layout with NEW_CALLS information> ]
                            End If
                         Set Variable [ $Temp ; Matchfield ]  (note: i.e. the record just processed)
                         Go to Record/Request/Page [ Next ; Exit after last ]
                         End Loop
                      End If 

                       

                       

                      Provided the found set is sorted before the processing loop, this works reliably. I am also happy that it is doing as little processing work as possible. Whilst not important in the context of my test application, it would be if there were large record sets involved.

                       

                      Further advantage is that it made me think for myself a bit :smileyhappy:

                       

                      I still have a question regarding the use of relationships in this context, but I will start a new topic for this.

                       

                      Once again, many thanks for your help