Modify Report Help

james

Hi,

I'm currently working on a couple of modify reports that will update both the Vendor No. in the Item and the Item Vendor tables for all records that currently have a certain Vendor No. (say XX001) and will be replaced by another Vendor No. (say YY001)

I've been able to update the Item table with the new Vendor No. by using ModifyAll,at first I had used the following:

"Vendor No." := "New Vendor No." (where New Vendor No. is a C/AL Global);
MODIFY;

but this was only updating one record at a time.

However the Item Vendor update is more complicated (well for me anyway!), as I cannot use ModifyAll due to the fact that the Vendor No. is a primary key.

Does anyone know how I would be able to get round this, so that for every record in the table with say XX001 as the Vendor No., I can automatically change it to say YY001.

The slight complication is that some Item No.'s in the Item Vendor table already have both Vendor No.'s in them.

Thanks for any help.

Albertvh

Hi James

To change the Item Vendor table you could do the following

ItemVendor.SETRANGE("Vendor No.",'XX001');
if ItemVendor.FIND('-') THEN
  REPEAT
    IF ItemVendor2.GET(ItemVendor."Item No.",ItemVendor."Vendor No.") THEN BEGIN
      ItemVendor2.DELETE;
      ItemVendor2."Vendor No." := New Vendor;
      IF ItemVendor2.INSERT THEN;
    END ELSE BEGIN
      ItemVendor2 := ItemVendor;
      ItemVendor2."Vendor No." := New Vendor;
      IF ItemVendor2.INSERT THEN;
      ItemVendor.DELETE;
    END;
  UNTIL ItemVendor.NEXT = 0;

ItemVendor2 is Global subtype Item Vendor datatype record

Hope this helps

james

Thanks very much for the help Albertvh.

I've slightly amended what you suggested, but you gave me the crux of what I was aiming for

The new code now reads:

"Item Vendor".SETRANGE ("Vendor No.",'XX001');
IF "Item Vendor".FIND('-') THEN
REPEAT
IF ItemVendor2.GET("Item Vendor"."Item No.","Item Vendor"."Vendor No.") THEN BEGIN
ItemVendor2.RENAME('YY001');
IF ItemVendor2.INSERT THEN;
END ELSE BEGIN
ItemVendor2 := "Item Vendor";
ItemVendor2.RENAME('YY001');
END;

UNTIL "Item Vendor".NEXT = 0;

I've taken the .DELETE code out because it was deleting the record but was then not creating a new record with the new Vendor No. and the Item No. associated with the old Vendor No.

I've included RENAME in the code, which may not be the best way to do it though it does works, however the issue I now have is that someone has manually added this new Vendor No into some records already (and kept the old vendor no.) in the Item Vendor Catalogue.

Therefore with these entries an error message is popping up saying the combination already exists and it is not updating. Is there any simple code that I can put in that will make it skip over these instances?

Probably the simplest solution is to create a report on the Item Vendor table that has both the old and new vendor no. and manually taking out the new vendor no before running this report. However if anyone has a quick solution then that would be great.

Thanks
James