6 Replies Latest reply on Dec 21, 2009 3:18 PM by TSGal

    How to convert/consolidate filed data from multiple records

    NeoTekCorp

      Title

      How to convert/consolidate filed data from multiple records

      Post

      We're looking to work with a business directory database. Problem is we want to convert data and possibly automate merging redundant records. Any quick thoughts on scripting to do this in FM Pro

       

      Job One: We want to change the category field on multiple fields. Example: If "Education" then replace field data with "Educational" I know how to do this by filtering and performing a find/replace. Ideally we'd like to have a script where we do this for multiple "if/then" scenarios.

       

      Job Two: The business listing we have has the same business listed multiple times, each under a different subcategory heading. Is there a way to perform a script to somehow automate a consolidation process. I guess the easiest way to explain this is visually. Say...

       

      XYZ Business 1234 Smith Street Anytown IL Internet Service Provider

      XYZ Business 1234 Smith Street Anytown IL Web Design

      XYZ Business 1234 Smith Street Anytown IL Web Hosting

       

      This would be like a clip from the excel file. My thoughts -- can a script be set up that basically say if the certain fields are the same (business name, street) then the records are merged into one record, with the subcategory fields combined into a new field called "keywords."

       

      Any help or thoughts would be appreciated. 


       

        • 1. Re: How to convert/consolidate filed data from multiple records
          TSGal

          NeoTekCorp:

           

          Thank you for your post.

           

          Job One: Yes, this can be done automatically with a script.  You will need to find all records that contain "Education" (and not already "Educational") and replace the information with "Educational".  Assuming the field that contains "Education" is named DATA, here is a script that may not be the most efficient, but it is easy to understand (all comments are preceded by a #):

           

          # following statement is needed in case no records exist

          Set Error Capture [On]

           

          # find the records that contain "Education" but not "Educational"

          Enter Find Mode []

          Set Field [ DATA ; "Education" ]

          New Record/Request 

          Set Field [ DATA ; "Educational" ]

          Omit Record

          Perform Find []

           

          # if no records are found, display a message and exit the script 

          If [ Get (FoundCount) = 0 ]

             Show Custom Dialog [ "No Records" ; "No Records Found" ]

             Show All Records

             Exit Script

          End If

           

          # records are now found

          # go to the first record and loop through the records 

          Go to Record/Request/Page [ First ] 

          Loop

           

             # replace "Education" with "Educational"

             Set Field [ DATA ; Substitute { DATA ; "Education" ; "Educational" ) ]

           

             # go to the next record.  Exit the script after the last record 

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

           

          End Loop

           

          -----------------

           

          If there was no "if/then" scenario, then the entire Loop structure could be replaced by the script statement:

           

          Replace Field Contents [ No dialog ; DATA ; Substitute ( DATA ; "Education" ; "Educational" )

           

          You didn't mention what was the "if/then" scenario, so the Loop structure above would be written:

           

           

          Loop

           

             If [ <condition> ]

                Set Field [ DATA ; Substitute { DATA ; "Education" ; "Educational" ) ]

             End If 

           

             # go to the next record.  Exit the script after the last record 

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

           

          End Loop

           

          -----

           

          That is, if the condition is met, then make the substitution.  Otherwise, skip and go to the next record.

           

           

          Job Two: Yes, you can create a script to merge records.  Create a new Text field, "LAST".  I'll explain later.  Here is a sample script:

           

          # create a variable to be used with Business Name and Street fields.  Initialize it to a large value.

          Set Variable [ $sort ; "zzz" ] 

           

          # sort records by Business Name (Ascending) and Street (Ascending)

          Sort Records [ Restore ; No dialog ]

           

          # go to the first record and enter the loop

          Go to Record/Request/Page [ First ] 

          Loop

           

             # check to see if new Business Name and Street

             If [ $sort ≠ Business Name & Street ]

           

                # check to make sure this isn't the first record

                If [ Get ( RecordNumber) ≠ 1

                   # if this is not the first record, then go to the previous record and mark it as "Last". 

                   Go to Record/Request/Page [ Previous ]

                   Set Field [ LAST ; "Last" ]

                   Go to Record/Request/Page [ Next ]

                End If 

           

                # set $sort to the Business Name & Street 

                Set Variable [ $sort ; Business Name & Street ]

                # set a string variable to the subcategory field 

                Set Variable [ $string ; subcategory ]

           

             Else

           

                # this is a multiple of the previous record. 

                # add the subcategory to the existing string variable and separate the values with a carriage return.

                Set Variable [ $string ; $string & "¶" & subcategory ]

           

             End If

           

             # replace the "keywords" field with the string variable

             Set Field [ Keywords ; $string ]

           

             # go to the next record.  When last record reached, exit.

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

           

          End Loop

           

          # all records have been processed.  All records marked as "Last" have the full set of Keywords for that group.

          #   Therefore, find all records that are not marked "Last" and remove them.

           

          Enter Find Mode []

          Set Field [ LAST ; "=" ]

          Perform Find []

           

          Delete All Records

           

          --------------------

           

          This leaves you with a set of merged records with a set of Keywords.  You can then remove the Subcategory and LAST fields if you wish.

           

          This should get you pointed in the right direction.

           

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

           

          TSGal

          FileMaker, Inc. 

           

          • 2. Re: How to convert/consolidate filed data from multiple records
            NeoTekCorp
              

            Thanks for the very detailed and informative solution. Kudos to you!!!

             

            One question. Is there a way to set the script and have the find value and replace value entered at the time the scripts runs. So, when you run the script a dialogue box pops up Replace "_________" with "___________" (or something along these lines).

             

            Thanks again for your help. It really is -- well -- very helpful... 

            • 3. Re: How to convert/consolidate filed data from multiple records
              TSGal

              NeoTekCorp:

               

              I'm glad you were able to get my solution to work.

               

              I should have mentioned that in "Job One", there is a much simpler way to find and replace values.  Put your cursor in the field that contains "Education", pull down the Edit menu and select "Find/Replace -> Find/Replace...".  In that dialog box, set the following parameters:

               

              Find what: Education

              Replace with: Educational

               

              Direction: All

               

              Check the option to "Match whole words only".  This ensures you don't change records that already have "Educational".

               

              Search across: All records

               

              Search within: Current field  (assuming "Education" only occurs in one field)

               

              Then click "Replace All".

               

              This should make it easier if you have several words that need updating and/or several fields to update.

               

              If this is going to be for a customer solution, you may not want to give this ability to a customer.  Instead, you could use two global fields and reference them in the script rather than "Education" and "Educational".  This way, you can prompt the user to enter information into the two global fields, and once you have that information, you can execute the script.

               

              TSGal

              FileMaker, Inc.

              • 4. Re: How to convert/consolidate filed data from multiple records
                wdockery
                  

                Thanks for your script about consolidating multiple records into one record.  My database problem is the opposite--the need to de-aggregate records--and I've tried to think like you, in reverse, but I can't quite get there.  I have inherited a church-related database in which each record is a name followed by 40 "profile" fields (field names "profile1", "profile2", . . ., "profile40") that may contain descriptive information about the person.  Examples are "received 2008 donation request" or "attended a stewardship education class" or "attends Sunday school in 2009" (these are actually coded with 5-character codes).  Some persons will have a code in 3 of the profile fields; others who are very active at church might have codes in 32 profile fields.  Almost no individuals have codes in each of the 40 profile fields.  The problem is:  for one person/records, "received 2008 donation request" might be in field "profile1", while for another, "received 2008 donation request" might be in "profile13".

                 

                Each name/record ALSO has 40 ADDITIONAL ProfileComment fields that are slightly more complicated:  field "ProfileComment1" is followed by field "Comment1", up to "ProfileComment40" and "Comment40".  Example:  "ProfileComment1" = BAPT (baptized) and "Comment1"= 3/14/57 (date of baptism).  These fields create a problem similar to the simpler "ProfileN" fields:  For one person, BAPT is in "ProfileComment1", while for another, BAPT is in, say, "ProfileComment27".

                 

                I have been asked to do all types of sorts on the data, and it looks to me like I need to break each Profile field into its own record, which would contain the fields "Name", "ProfileCode", and "ProfileComment".  Does this sound reasonable?  Then I could do some creative sorting and analyzing of data.

                 

                Now for the practical issue:  How do I write a script that does this?

                 

                Thanks for general thoughts or specific answers.

                • 5. Re: How to convert/consolidate filed data from multiple records
                  newlyuser
                    

                  TSGal, I have a similar situation and need to convert from multiple records to one record but your solution didn't seem to work for me. Would you be able to help me here? Basically, I have this report which has employee information like ID, Name and Form Name. Each employee will have 4 records in the report with Form Name different for each record. Example,

                   

                  ID     Name          Form

                  123   John Doe     Letter

                  123   John Doe     Application

                  123   John Doe     Application 2

                  123   John Doe     Letter 2

                   

                  Even if the employee is missing Form or Forms, he/she will still have 4 records in the report but just have the Form blank like

                   

                  123   John Doe     Letter

                  123   John Doe     Application

                  123   John Doe

                  123   John Doe  

                   

                  I have a database that I need to import this report. The database have ID (Text field), Name (Text field) and 4 check boxes for Form (Letter check box, Application check box, Application 2 check box and Letter 2 check box) I need to be able to import the report but when importing, it should only add (or update if the employee already exist in the database) only one record for the employee with ID 123, Name John Doe and have Letter, Application, Application 2, and Letter 2 check box checked off based on the report.

                   

                  Any help you can provide is appreciated. Please let me know if I can help understand this in a better way. Thanks! 

                  • 6. Re: How to convert/consolidate filed data from multiple records
                    TSGal

                    newlyuser:

                     

                    Thank you for your post.

                     

                    When importing from a text file, each line is a record.  Therefore, we need to create a secondary table, IMPORT, so that we can process the information.

                     

                    Next, in order to process the records more quickly, let's sort and group them together by the ID.

                     

                    Here's a possible script (all comments are preceded by "#", and extra space is purposefully added to help readability):

                     

                     

                    # set error capture on

                    Set Error Capture [ On ]

                     

                    # start with a fresh IMPORT table

                    Go to Layout [ <IMPORT layout> ]

                    Show All Records

                    Delete All Records [ No dialog ]

                     

                    # import the data

                    Import Records [ No dialog ; <your data file> ]

                     

                    # sort the records by ID

                    Sort Records [ Restore ]

                     

                    # go to the first record and start processing

                    Go to Record/Request/Page [ First ]

                     

                    # create a variable to store information about the ID.  Initialize it to a large value.

                    Set Variable [ $var ; "ZZZZZ" ]

                     

                    Loop

                     

                       # check to see if the ID field equals the variable $var - this will fail initially 

                       If [ IMPORT::ID ≠ $var ]

                          # if so, store the contents of the ID field into $var, contents of Name into a variable $nm, and the contents of Form into $fm

                          Store Variable [ $var ; IMPORT::ID ]

                     

                          #switch to the layout where you want to update the data 

                          Go to Layout [ <your original table you want to update> ]

                          

                          # search for the relevant ID

                          Enter Find Mode []

                          Set Field [ ID ; $var ]

                          Perform Find []

                     

                          # if a record doesn't exist, then create one

                          If [ Get ( FoundCount ) = 0 ]

                             New Record Request

                            

                             # once the record is created, replace ID and Name with the variables $var and $nm, respectively

                             Set Field [ ID ; $var ]

                             Set Field [ Name ; $nm ]

                          End If

                     

                       Else

                     

                          # switch to the data layout 

                          Go to Layout [ <your original table you want to update> ]

                     

                       End If 

                     

                       # regardless if the record is found or not, we are now pointing to the correct record.

                     

                       # check the value of $fm (Form).  If not empty, then update the record

                       If [ not IsEmpty ( $fm ) ]

                     

                          # check to see if information already exists in the checkbox field.

                          If [ IsEmpty ( Form ) ]

                             # if not, then replace the field with $fm

                             Set Field [ Form ; $fm ]

                          Else

                             Set Field [ Form ; Form & "¶" & $fm ]

                          End If

                     

                       End If

                     

                       # return to the IMPORT layout

                       Go to Layout [ <IMPORT layout> ]

                     

                       # go to the next record and evaluate

                       #    if the last record, then exit the loop 

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

                      

                    Loop

                     

                        

                    --------------

                     

                    Please feel free to contact me if you need clarification for any of the above steps.

                     

                    TSGal

                    FileMaker, Inc.