Options

Loop through 2 tables to populate a third table

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;

Best Answer

  • Options
    Village_IdiotVillage_Idiot Member Posts: 14
    Answer ✓
    Hi, thank you lubost for proposing a solution, it didnt work, but I found another way to get it to work

Answers

  • Options
    lubostlubost Member Posts: 614
    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;
  • Options
    Village_IdiotVillage_Idiot Member Posts: 14
    Answer ✓
    Hi, thank you lubost for proposing a solution, it didnt work, but I found another way to get it to work
Sign In or Register to comment.