5 Replies Latest reply on Jul 5, 2017 7:58 AM by lrodri007

    Similar Fields with bad data, trying to combine the two

    lrodri007

      The premise:

      I have a database dump that I threw into Filemaker (running new 16 version).  It has 3 tables. 

      1. Participant List Records,  18000 records, with about 100 fields (many reiterate, see below)

      2. Program List Records, it has about 45 records

      3. Join Table PxP (participant program relationship), it has about 50,000 records

       

      My question relates to the many similar fields that were created as different people administered the database itself in Participant Table.

      For example there are many fields that are duplicated; i.e. Gender01, Gender02, Gender03.  Each field has similar data, Male, Female although they are upper case in one field and lower case in another.  As I scroll through the data, you can see data in some columns, then goes blank while another column begins to show data.  Visually you can see time working here.  Gender01 shows data for several records then stops as records begin to show in Gender02.  And every once and a while you see both fields filled in.

       

      I am trying to export this data into concise excel file for management to work with (they work with Pivot Tables in excel).  Instead of exporting data as is I'm trying to see if I can consolidate the common columns into one.

       

      I want to go through all the different common fields (Gender, Ethnicity, Race, etc.) arbitrarily (actually choose the column with majority of data) choose a master column that I want to keep then....

      1. Run process to compare values of common fields, Gender1:Gender2:Gender3 etc

      2. If master column has data keep going

      3. If Master column is blank look through other fields to see if they have data to copy over to first column.  In this scenario, If I get hit on Gender2 stop, otherwise if Gender 2 is also blank check 3, 4, 5 for data until all possibilities are exhausted.  If blank at the end oh well....

      4. That's about it, once done, I will do quick check then delete all extra columns. 

       

      In the end I want to go from :

      LName:FName:Gender1:Gender2:Ethinity1:Ethnicity2:zipcode

      to

      LName:Fname:Gender:Ethnicity:Zipcode

       

      Thanks for any help..... hopefully there are solutions out there other than walk away

        • 1. Re: Similar Fields with bad data, trying to combine the two
          philmodjunk

          Both a looping script and the calculation option of Replace Field Contents could do this. In Replace Field Contents, you'd set up a case function to look at each alternate field in succession, returning the value of that field if it is not empty.

          • 2. Re: Similar Fields with bad data, trying to combine the two
            bigtom

            philmodjunk has the answer for you. I prefer to use the looping script as I can run it through the debugger for the first few records to confirm it works as expected before it runs on the entire table.

            • 3. Re: Similar Fields with bad data, trying to combine the two
              lrodri007

              Thanks for the reply.  This morning came back to sort my fields by primary, secondary, tertiary, etc.  To elaborate I added Leading counts to each of the duplicated fields.  So now I can see my three fields of Gender (G1, G2, G3) and know that G1 has 15000 values, G2 has 3000 values, and G3 has 300 values.

               

              Then I use the logic: I want to copy from secondary field to primary field.  If Primary is empty, and there is a a value in Secondary, copy that over.  In the end I wan to progressively fill in empty values in Primary from all others if there are any.

               

              Of course this is my thought and it doesn't help I am not great at coding.  So off I went to the forums to piecemeal things I thought went together. Found some other forum subjects that were similar in some respects and of course you helped in many!!

               

               

              Here is what I have so far in steps:

              1. Enter Browse

              2. Go to my layout with records I am working with

              3. Show all records

              4. Go to beginning

              5. Begin a LOOP

              6. Set Variable to secondary field (of the two I am reviewing the one with fewer values)

              7. Run IF [IsEmpty] on my Primary Field

              8. If it is, then Set Field to contents of Variable above

              9. Go to next record

              10. End if

              11. End Loop

               

              Okay I definitely know things are out of whack just wondering if anyone wanted to help before I got back to thinking about it.  One of those things where it took me a couple of hours to find more info in forums and build the logic behind it.  Now I have to figure out the correct syntax to have to of through each record finding empties and copying values from smaller set.

               

              And... will I get errors?  I am thinking what if both fields are blank?  At this point I want to keep going in the script and ignore that as data entry being at fault and not much I can do now.

              • 4. Re: Similar Fields with bad data, trying to combine the two
                philmodjunk

                Why not use replace field contents?

                 

                Show All records

                Replace Field Contents

                 

                Might be all that you need.

                 

                You can set up a calculation option with a case function that looks like this:

                 

                Case ( Not IsEmpty ( MainField ) ; MainField ;
                           Not IsEmpty ( Field2 ) ; Field2 ;

                          Not IsEmpty ( Field3 ) ; Field 3 ;

                          and so forth for however many fields that you have

                         )

                 

                This will leave MainField unchanged if it has data. If not, Field2 will be copied over, if Field 2 is empty, then Field 3 will be copied over and so forth....

                 

                Just put your field names in place of mine in this function.

                • 5. Re: Similar Fields with bad data, trying to combine the two
                  lrodri007

                  philmodjunk.....Thanks for that last explanation.  Just got in and made copy of the database and ran the script per your instructions.  Eureka!!   Tried it on field with just two columns (City_01 & City_02).  Before leaving work Monday and posting my example above this was these were the fields I was trying to work with and confirmed that City_02 only had two values missing from City_01 so anything I ran would be easy to check at end.

                   

                  Sure enough this morning used your case calculation and got the +2 count of values  on City_01 at end of script run. 

                   

                  Thanks you for help!!