I've done this with a button script... scroll through the table, and when you find one you want to remove, then:

retval = oPriceCode.Delete()

Thanks Kevin,

You think this will work in a BOI script to do a bulk delete?

We want to delete records from a couple of customers, something like 3400 records.

Regards,

Manuel

I have no idea.  Test company: try it.

Thanks, will do...

You can accomplish this with a BOI script on either a button or externally to loop through the records and compare if they meet the criteria and delete them or you could use the following approach using a VI import job to delete records in bulk.

The first step is to identify the data source and whether or not the records are going to be prefiltered for deletion or whether you will rely on a combination of conditional data field, select fields, and/or skip fields in the VI job to identify records that should be deleted. In the following example, i am using ODBC as the data source and the default prepared SQL statement. You can typically use a task's lookup to export to excel and either filtering in the lookup or once the data is in excel and then export to CSV. You could also use either Crystal Report or Excel to query the table directly via ODBC and either filter with a SQL WHERE clause or once the data is in excel. Regardless, if the VI job is not using ODBC as the source, i recommend using CSV as you should encounter fewer problems than with the other formats like xls, xlsx, etc.

In the case of the IM_PriceCode table, the default lookup isn't going to work so i just used ODBC as the source. You can create a custom lookup but for some reason sage decided the key columns wouldn't be in the same order in the lookup as they appear in the data table. You can still work with this if you export to excel and then CSV, you would just need to adjust the column positions in the import definition accordingly.

On to the next step.

Create a VI import job with IM_PriceCode as the target table and set your source information.

Here is the table added.

Followed by the fields that make up the primary key in the same order as they appear in the data table. 

Use either the File Layouts or DFDM the target table to identify the fields that make up the primary key.

This is what the default prepared SQL statement looks like now. If you feel comfortable with SQL syntax, you could modify it to include a WHERE clause to filter the records. 

Now add the fields that make up the primary key. i suggest adding them in the same order has they appear in your data source or else you will have to adjust the column position as needed.

i am using an insignificant field to identify records to delete. Now in this example, it is the PriceCodeDesc field.

Set the operation to Assign and the Default value to something that makes sense, in this case, i am using DELETE.

Since i am not prefiltering the data source, i am relying on the conditional expression seen above and the following select field.

So if the PriceCode is either a "1" or a "2", the PriceCodeDesc will be set to "DELETE" then if the PriceCode is a "1", the record will be selected with the following logic. 

These are simplified examples and you can even add skip record logic as well.

Now the final step that makes this all work is perform logic assigned to the After Write event.

Here is what it looks like set up.

Here is the full string with the condition that the PriceCodeDesc equals "DELETE".

IF PriceCodeDesc$="DELETE" THEN coBusiness'Delete(coBusiness'GetKey$()) !

If you prefilter your source and every record read from your source should be deleted, then you would just use the following.

coBusiness'Delete(coBusiness'GetKey$()) !

You would also not include the conditional expression on the PriceCodeDesc field.

Hopefully this all makes sense.

Feel free to reply back if you have questions.

Thanks David,

WOW,!!, this is a heck of a response.

I ill give it a try and let you know the outcome.

Regards,

Manuel

As Kevin, said, make sure you play around in a test company before applying to the production company to make sure you get the desired results. Should have included this warning in my original post. 

On a final note, since the perform logic occurs on the after write event, this does allow you to use the "Test" function to see how things would go but since VI wasn't intended to delete records, the log will not indicate records are deleted, it will indicate records successfully "changed", skipped, or failed. So keep that in mind.

Thanks David, WORKED fine...I do understand the logic, i like it.

I never thought about doing things this way excellent job.

Regards

Manuel