3 Replies Latest reply on Jan 19, 2011 8:34 AM by philmodjunk

    Help! Change flat file to relational? (newbie alert)

    MarkusWinter

      Title

      Help! Change flat file to relational? (newbie alert)

      Post

      Hi all,

      another newbie question I'm afraid.

      I had a look at the databases our lab has set up, and unfortunately they are basically glorified spreadsheets - so for example it looks like this

      product A ordered by

      ABC Maria
      CDF Ron
      AGG Maria Nova

      where Maria and Ron are simply entered text. So I see two problems:

      • sometimes the same person is entered under different names (for example with or without family name) or simply misspelled

      • there should be a table "people" and each name should just be a link to the table entry



      So my questions are:

      - is there a way to clean up the database?

      - is there a way to batch replace names with table entries

      I should note that there are several such databases, each with 2,000 to 5,000 entries, so I would not want to do this by hand.

      Thanks

      Markus

      P.S. In my defense: I'm currently working my way through some tutorials (boring as heck - they seem tailored to someone who sees a computer for the first time) and nothing has come up yet to help me with my problem. 

        • 1. Re: Help! Change flat file to relational? (newbie alert)
          Sorbsbuster

          Can we assume that the Staff Codes are all correct - eg: the ABC is always entered correctly in all of your existing records?

          If that's the case then simply create another table in your file, called STAFFNAMES, with two fields: the StaffCode, and StaffName.  (That's very simplistic - I'd recommend you create NameFirst, NameSecond, NameFull = NameFirst & " " & NameSecond, etc)

          Then create a link between the two tables, using the StaffCode as the linking field.  In your existing table, double-click the existing 'Name' field.  This will open the 'Specify Field' dialogue box.  Click on the Table Occurance at the top of that window and choose the StaffName table from the relationship you just created.  Select the StaffName field from the list.

          You will now see displayed the correctly-entered name, where the StaffCodes match.

          • 2. Re: Help! Change flat file to relational? (newbie alert)
            MarkusWinter

            That would be nice, but ABC are no staff codes - they are just items like oligos, vectors etc Frown

            It really is just like one big Excel spreadsheet

            • 3. Re: Help! Change flat file to relational? (newbie alert)
              philmodjunk

              While there are tools in filemaker that can help you here. There will, unfortunately, be no substitute for at least some "hands on" intervention to clean up your data. Here's one approach:

              1. Define a Staff table with at least two fields:

                StaffID (Auto-entered Serial number)
                Name (name to match the name column in your spread sheet)
              2. On your staff layout, resize the name field to be many lines of text tall. You may want to also give it a scroll bar as the name field will serve as a list of different forms of the same staff person's name when you are finished.
              3. Hand enter or import the data you need to create a list of all staff with names correctly entered. (If you import the data, make sure that the "enable auto enter" check box is enabled so that each staff record is assigned a serial number in staff ID automatically.)
              4. Import your spreadsheet data into a separate table where you've defined a relationship like this:

                ImportTable::Name = Staff::Name
              5. Add a field for StaffID (Number, NOT auto-entered) to ImportTable
              6. Add the StaffID and Name fields from Staff to your Import Layout.
              7. Enter find mode, type a single asterisk, *, into Staff::Name, then select the omit button in the status area and perform your find. This finds all records in ImportTable that don't match a name in the staff table.
              8. Look at the imported Name of the first record found. Copy the imported name name to the clipboard.
              9. Switch to your Staff layout. Determine to which staff record it belongs. Find the staff record for this person, click after the last name in the name field, press return to start a new line and paste the copied name into this field so that you've added a new line of text in the name field. This will not only match your current record in the Import Table to this staff record but any other records that have this same name.
              10. Return to your Import Table layout. You should now see a name and staffID number from the staff table for the record you just worked with.
              11. Repeat this process untill all records match to a staff record. You can periodically repeat the above find to omit records that now match to a staff record from your found set. When no records are found, you have finished linking your records.
              12. Now Select Show All Records, put the cursor in the empty ImportTable::StaffID field and use Replace Field Contents with the calculation option to copy Staff::StaffID into all your records.
              13. Now you can link the importTable and Staff table by StaffID instead of name.