1 Reply Latest reply on Apr 14, 2012 10:20 AM by GuyStevens

    Marking records with matching data... use duplicates??

    FMNewbie

      Title

      Marking records with matching data... use duplicates??

      Post

      So... I have records that track data that for the sake of simplicity I'll call: JOB NUMBER, DATE, TIME START, TIME END, and VEHICLE.  In this setting it is possible two have two or more jobs logged with the date, times and vehicle... doing 2 jobs at the same time.  I'm trying to find a way to go back through all 5000 or so records, find the ones where two or more jobs are done at the same time and mark another field, say MULTIPLE JOB as "Y". 

      I've been trying to use the "!" search operator (then just backfill the MULTIPLE JOB field using REPLACE FIELD CONTENTS), but I don't think I'm using it right. 

      What am I missing?  Do I need to do this through a script or something???   Help?  Yell

        • 1. Re: Marking records with matching data... use duplicates??
          GuyStevens

          I just did a little test and here's what I think the easiest way to do this.

          First you create a new field in your table (Table_JOB). Let's call it "calc_MatchField". That will be a calculation field resulting in text.
          In this field you just put all the fields you want to use to look for duplicates. For instance you put:
          JobNumber & Date & StartTime & EndTime & Vehicle

          Then you go to the relationships dialogue and you create a second table occurrence of Table_JOB. You could name it TableJOB_Match.
          (You create a second table occurrence by using the two green plus signs on the bottom.
          Then you make a relationship between the two tables using the "calc_MatchField" field.
          TableJOB::calc_MatchField--------[=]-------TableJOB_Match::calc_MatchField

          Then you create a new script with these steps:

          Show all records

          Go to Record/Request/Page [First]

          Loop

          Set Field [TableJOB_Match::MultipleJob; "Original"]

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

          End Loop

          If you do this all of the original records that are not a duplicate will be marked "Original".

          Then you perform a find for the word "Original" in the field "MultipleJob"

          and then you go to the menu "Record" and you do "Show omited Only"

          Now you have only your duplicate records.

          Now you can use almost the same script to loop trough the current records (don't use the "Show all" script step in this script) And you could mark them as duplcate in the MultipleJob field.

          That script would look like this:

          Go to Record/Request/Page [First]

          Loop

          Set Field [TableJOB::MultipleJob; "Duplicate"]

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

          End Loop