Loop through 2 tables to populate a third table

Village_Idiot

I have 3 tables,

1. Common table (Central Vendor) with vendors 50009 – All the vendors that is in the database - all companies, are in here.
2. Common table (Central Vendor Link) with Company Codes and Vendor Numbers 50008
3. Vendor Table 23

I want to loop through the Common Vendor Table 50009 and populate the Vendor Table 23 in a company based on the filtered values on vendor Number in 50008 Central Vendor Link

Central Vendor Link TABLE
Company Code Vendor No.
COMPANY1 V0001
COMPANY2 V0001
COMPANY2 V0006
COMPANY1 V0011
COMPANY1 V0016
COMPANY2 V0021

So while I am in Company = COMPANY1, I want to loop through the Central Link Table and get all the numbers where Companyname = COMPANY1
and then populate Table 23 in COMPANY1 from Central Vendor for only numbers that match the filtered values in Central Vendor Links
My processing report is written as follows, but the results are not correct. I get all the Vendors from the Central Vendor
Some help will be appreciated

Vendor OnPreDataItem()
Vendor.INIT;

CentralVendorLink.SETRANGE("Hospital Code",Company.Name);
IF CentralVendorLink.FINDSET THEN

CentralVendor.SETRANGE("No.",CentralVendorLink."Central Vendor No.");
IF CentralVendor.FINDSET THEN BEGIN
Vendor.SETRANGE(Vendor."No.",CentralVendor."No.");

IF NOT Vendor.FINDSET THEN
REPEAT
WITH Vendor DO BEGIN
INIT;
TRANSFERFIELDS(CentralVendor);
INSERT;
END
UNTIL CentralVendor.NEXT = 0;
END;

Village_Idiot

Hi, thank you lubost for proposing a solution, it didnt work, but I found another way to get it to work

lubost

I think that your object is report, so:
- use the first dataitem CentralVendorLink with code in OnPreDataItem trigger
CentralVendorLink.SETRANGE("Hospital Code",Company.Name);
- use the second dataitem CentralVendor intended under CentralVendorLink dataitem and set the Link property to "No."="Central Vendor No."
- in OnAfterGetRecord trigger of second dataitem use your code:
IF NOT Vendor.GET(CentralVendor."No.") THEN BEGIN
Vendor.INIT;
Vendor.TRANSFERFIELDS(CentralVendor);
Vendor.INSERT;
END;

Village_Idiot

Hi, thank you lubost for proposing a solution, it didnt work, but I found another way to get it to work