How to Determine if data to be imported does not exist?

zulq

I have a csv file which contains list of employees. This list is imported on periodic bases. These data however sometimes changes. I want be able to compare the data to be imported to the existing data. If an employee exists in the database but is not in the import list it should be marked inactive/terminated. How do I compare the existing data to the list on the file to be imported?


Thanks.

Savatage

One way is to have a "Date Modified" field. If the CSV has that Employee then update the "Date Modified". Then later you can easily filter on that field to see which one's were & were not updated.

zulq

I mean I need to do this while importing for example comparing the unique employee no. on the import against unique employee no. on the existing data (nav table).

ara3n

You can also create a temporary record variable and insert all the records from the file into temporary record.

Then at the end loop through the employee record and see if it exists in Temporary record, if it doesn't then you can expire that employee

Savatage

How is it imported? using a dataport?

zulq

Sorry was away. Yes it is imported using a dataport.

ara3n

Just follow the suggestion I had written above. use temp record.

zulq

Hi Rashed,
I am trying to implement your suggestion but it's not working. Below is the code am using:

emp.SETCURRENTKEY("No.");
temp.SETCURRENTKEY("No.");
IF emp.FIND('-') THEN BEGIN REPEAT
IF temp.FIND('-') THEN BEGIN REPEAT
IF NOT temp.GET(emp."No.") THEN BEGIN
MESSAGE('no. '+'%1'+ emp."No.",' + does not exist');
emp.Status := emp.Status::Inactive;
emp.MODIFY(TRUE);
END;
UNTIL temp.NEXT = 0;
END;
UNTIL emp.NEXT = 0;
END;

Am I missing something?

David_Singleton

WOW these are some complex solutions here, how about doing it the simple way*.

Create a new field in the employee card "Active Employee"::Boolean.
In the dataport add ONE LINE OF CODE:

"Active Employee" := TRUE;

done.


* PS what ever happened to the old Navision logo "The Beauty Of Simplicity"

ara3n

Hello You have a bug in your code.

IF emp.FIND('-') THEN BEGIN REPEAT
  IF NOT temp.GET(emp."No.") THEN BEGIN
    emp.Status := emp.Status::Inactive;
     emp.MODIFY(TRUE);
   END;
UNTIL emp.NEXT = 0;

I'm assuming that on onaftergerimportrecord you are populating Temp record.

ara3n

you can also do what David has suggested, but instead use the current field Status.

On predataItem

Emp.modifyall (status,Status::Inactive);




On afterimportRecord add code to change the status to active.

validate(status,Status::Active);

zulq

I did as you guys suggested but still after the import all employees are set to inactive.
OnPreDataItem:

emp.SETRANGE(PaymentMode,PaymentMode::Wages);
emp.MODIFYALL(Status,Status::Inactive);

Then on AfterImportRecord:

VALIDATE(Status,Status::Active);

But still the status is inactive for all employees.

zulq

It's working partially but employees who are not in paymentmode::wages are also set to inactive which should not be the case. That's why I put the the setrange there before deactivating everyone. Also if an employee is in the data to be imported but not in the employee table it should be inserted.

Thanks.

zulq

Everything is working now except inserting record into table if it doesn't exist.
Any help please.

ara3n

can you export your dataport as text and paste it in here?