Re: how to script and how to make some records invalid...

[filemakerman]

Hi,all !

 

There are 2 problems in my FM 5.5 database.

The first one is I want to make all records in the year of 2002 and 2003 and 2004 invalid,no idea on how to do this immediately.

Just keep 2005 and 2006 records.

 

The 2nd one is I have a primary matching id:WorkerID.

But I found out some records are repeated with the same WorkerID,but with different type.

 

Example:

1.

 

NAME: JACK GOOER WORKERID:1087988

GENDER:MALE TYPE: IN

Modified in :1/1/2004

2.

NAME: JACK GOOER WORKERID:1087988

GENDER:MALE TYPE: I

Modified in 1/1/2005

3.

NAME: JACK GOOER WORKERID:1087988

GENDER:MALE TYPE:

Modified in:1/1/2006

 

You see from the above,in nature,this is only for 1 person,should be just 1 record,but they have slight difference,and were regarded as 3 records.

My questions:

 

How to find all records with this proble? I believe I got a few hundreds of records with the same problem.

 

If I only like to keep the year of 2006,what should I do?

 

It seems,some scripts must be written for solving these two problems.

 

Anybody likes to help ?

 

Thanks a lot.

 

Jet Lacorn

[donwolfkonecny]

1) apple-F for Find, for the date field put ...12/31/2004 and hit enter

then do what you want to the found set to flag them as old

 

2) if your workerid is indexed, apple-f for find, for worker id put ! and hit enter. this will find all duplicates. then sort by workerid to make them easier to work with. then delete alternates or reconcile or do whatever to eliminate dupes.

 

for the workerid field, in options, you can specify no duplicates, this would mean a data-enterer would get a message that duplicates are not allowed

[filemakerman]

Hi,my friend,

Thanks a lot for your answer.

 

1) apple-F for Find, for the date field put ...12/31/2004 and hit enter

then do what you want to the found set to flag them as old

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

***

But how can I flag all found records as Invalid together?(to tag INVALID Checkbox for over 500000 records)

-----------

2) if your workerid is indexed, apple-f for find, for worker id put ! and hit enter. this will find all duplicates. then sort by workerid to make them easier to work with. then delete alternates or reconcile or do whatever to eliminate dupes.

 

---------

***

after sorting,how could I reconcile the old records with the new ones?

 

Thanks a lot.

 

Jet

[Maarten Witberg]

But how can I flag all found records as Invalid together?(to tag INVALID Checkbox for over 500000 records)

Go to the field INVALID of one record, type your flag code, do not commit the record then Use menu bar->records->replace field contents (or apple-=). 's gonna take some time for 500000 records I suspect. But you can just get some coffee while filemaker does the work.

 

after sorting,how could I reconcile the old records with the new ones?

depends on what you need to do. It sounds as if you just need to make a printout and hand it to someone at HRM and ask them which data is the correct, then you can delete the rest.

 

kjoe

[Maarten Witberg]

About those duplicate records: I assumed the WorkerID field was the identifying key field for relationships. If this is not the case (which should be so since WorkerID has duplicates) then put off deleting the duplicate records untill you know which records in other tables they relate to, then link these to the one you're going to keep. If you need further help, please let us know.

 

kjoe