deleteing obsolete records

[oilman]

Hi all

Our sales people send us a monthly report and we download it into FM6. Each record has a serial number. We would like to script the deletion of obsolete record if possible. We perform this step manually now. We compare the serial number sent in by the sales people with the db and delete the record in the db that are no longer listed on the sales report. The problem is that the report may contain new information that we would need to keep.

Example:

in the the db the serial numbers are as:

601

602

603

604

605

in the report from the sales people, the serial numbers are as:

602

603

604

605

606

We would like to delete record 601 and keep 606.

Is there a way of doing this with a script.

Thanks in advance

Lionel

[SassyTexan]

Is this 1 database or 2? I am sure that there is a solution.

[fishma]

Set up a relationship between the DB and the downloaded report (Serial::Serial).

Loop through the DB and determine if the relationship is Valid. If not, Delete the record.

ShowAll

GotoRecord Request First

Loop

If(Serial = relatedSerial)

Else

DeleteRecordRequest(NoDialog)

EndIf

GoTo Record Request Next (Exit after Last)

EndLoop

ExitScript

There are more elegant solutions, but this should be suffice for your needs.

Enjoy

[oilman]

RECORDS ARE IMPORT FROM THE SALE PEOPLE DB INTO THE MAIN DB.

[Robert Kidd]

I think your script wouldn't check the record after a record deletion.

You need to move the "go to next record" between the If and Else

[fishma]

My script determines the following...

If the relationship is valid (i.e. the record still exists in the sales report), do nothing.

If the relationship is no longer valid (i.e. the record no longer exists in the sales report), delete the record.

I hope this clarifys things for you.

[Robert Kidd]

BUT.

Your script finds a record to delete and deletes it. You will now be viewing the next record in the DB. You current script then moves to the next record therefore the record immediately after the record which is deleted will remain unchecked.

[fishma]

Nice Catch...

Revised...

ShowAll

GotoRecord Request First

Loop

If(Serial = relatedSerial)

OmitRecord

Else

DeleteRecordRequest(NoDialog)

EndIf

ExitLoopIf(FoundCount = 0)

EndLoop

ShowAllRecords

ExitScript

Thanks Kidd

[oilman]

Hi Clark and Robert

Thanks for the information will do

[Paolo]

i think there is a MUCH easier way to do that:

import record with "Update Matching Records" + "Add remaining records"

obsolete records will not be in the found set after the import so

Show Omitted

Delete All Records

---

Paolo

[oilman]

thanks Paolo

[bruceR]

Paolo said:

i think there is a MUCH easier way to do that:

import record with "Update Matching Records" + "Add remaining records"

obsolete records will not be in the found set after the import so

Show Omitted

Delete All Records

Ack, I wouldn't do that! (Delete the records; I WOULD use import/update matching).

You seem to be assuming that there is only one sales person, only one set of records, all historical records are invalid, etc. Doen't seem likely to me.