Export data from multiple companies into CSV

Hello,

I’m not a Navision expert, but a PHP programmer, working with a client to synchronize data between our website and Navision. The process require auto export of some data into CSV and upload it via FTP to the server, where the website will automatically import the data using Cronjob.

We were doing fine using this script:

Documentation()
Meg01.00 CM (15-11-18): New Modification for FZE company.(WAG-000125)
Meg02.00 CM (09-11-20): New Modification for FZE company.(WAG-000153)
Meg03.00 CM (08-12-20): New 2 columns "Projected Qty fze"and "PO ETA FZE".(WAG-000155)
MT 1.00     (07-22-21): New 3 Colums "Availability Qatar ;Projected Available Balance Qatar;Purchase Order ETA Qatar"

OnInitXMLport()

OnPreXMLport()

OnPostXMLport()

Root - Export::OnBeforePassVariable()

Root - Import::OnAfterAssignVariable()

Integer - Import::OnAfterInsertRecord()

Integer - Import::OnBeforeModifyRecord()

Integer - Import::OnAfterModifyRecord()

Integer - Export::OnPreXMLItem()

Integer - Export::OnAfterGetRecord()
ItemNo := 'Item No.';
ItemRef := 'Item Reference';
Desc := 'Description';
//ManufacCode := 'Manufacturor Code';
//SellCurr := 'Selling Currency';
SellPrice := 'Price';
//Inv := 'Inventory';
Avai := 'Availability';
ETADate := 'Purchase Order ETA';
ProjectedAvaiBal := 'Projected Available Balance';
AvaiFZE := 'Availability FZE';//meg02.00
ETADateFZE := 'Purchase Order ETA FZE';//meg03.00
ProjectedAvaiBalFZE := 'Projected Available Balance FZE';//meg03.00

AvaiQatar := 'Availability Qatar';//MT 1.00
ETADateQatar := 'Purchase Order ETA Qatar';//MT 1.00
ProjectedAvaiBalQatar := 'Projected Available Balance Qatar';//MT 1.00

Integer - Import::OnAfterInitRecord()

Integer - Import::OnBeforeInsertRecord()

ItemNo - Import::OnAfterAssignVariable()

ItemNo - Export::OnBeforePassVariable()

ItemRef - Import::OnAfterAssignVariable()

ItemRef - Export::OnBeforePassVariable()

Desc - Import::OnAfterAssignVariable()

Desc - Export::OnBeforePassVariable()

SellPrice - Import::OnAfterAssignVariable()

SellPrice - Export::OnBeforePassVariable()

Avai - Import::OnAfterAssignVariable()

Avai - Export::OnBeforePassVariable()

ProjectedAvaiBal - Import::OnAfterAssignVariable()

ProjectedAvaiBal - Export::OnBeforePassVariable()

ETADate - Import::OnAfterAssignVariable()

ETADate - Export::OnBeforePassVariable()

AvaiFZE - Import::OnAfterAssignVariable()

AvaiFZE - Export::OnBeforePassVariable()

ProjectedAvaiBalFZE - Import::OnAfterAssignVariable()

ProjectedAvaiBalFZE - Export::OnBeforePassVariable()

ETADateFZE - Import::OnAfterAssignVariable()

ETADateFZE - Export::OnBeforePassVariable()

AvaiQatar - Import::OnAfterAssignVariable()

AvaiQatar - Export::OnBeforePassVariable()

ProjectedAvaiBalQatar - Import::OnAfterAssignVariable()

ProjectedAvaiBalQatar - Export::OnBeforePassVariable()

ETADateQatar - Import::OnAfterAssignVariable()

ETADateQatar - Export::OnBeforePassVariable()

Item - Import::OnAfterInitRecord()

Item - Import::OnBeforeInsertRecord()

Item - Import::OnAfterInsertRecord()

Item - Import::OnBeforeModifyRecord()

Item - Import::OnAfterModifyRecord()

Item - Export::OnPreXMLItem()
//Item.SETRANGE("No.",'WO0022');

//Item.SETRANGE("No.",'IS0044'); //ITG

Item - Export::OnAfterGetRecord()
InventorySetup.GET();
GLSetup.GET;//Meg02.00

ManiDesc := '';
//IF NOT GLSetup."FZE Company" THEN//meg02.00
  ManiDesc := Item."Manufacturer Code" +' '+Item.Description;
Item.CALCFIELDS(Inventory);

IF NOT GLSetup."FZE Company"  THEN BEGIN//Meg02.00
  SalesPrice.RESET;
  SalesPrice.SETRANGE("Item No.",Item."No.");
  IF SalesPrice.FIND('+') THEN BEGIN
    REPEAT
     // SellingCurr := SalesPrice."Currency Code";
      IF COMPANYNAME = 'I.T.G.' THEN BEGIN
        PriceSell := FORMAT(ROUND(SalesPrice."Unit Price"/1.11,0.0001));
      END ELSE 
      IF COMPANYNAME = 'QATAR WoodandGas for Trading' THEN BEGIN //MT 1.00
        PriceSell := FORMAT(''); //MT 1.00
      END ELSE BEGIN //MT 1.00
        IF NOT GLSetup."FZE Company" THEN//Meg02.00
          PriceSell := FORMAT(ROUND(SalesPrice."Unit Price",0.0001))
        ELSE//Meg02.00
          PriceSell := FORMAT(ROUND(SalesPrice."Unit Price",0.0001));//Meg02.00
      END;
    UNTIL SalesPrice.NEXT = 0;
  END;
END;//Meg02.00

QtyAvaiSales := 0;
QtyAvai := 0;
TotalQtyAvai := 0;




SalesLine.RESET;
SalesLine.SETCURRENTKEY("Document Type",Type,"No.","Shipment Date");
PurchLine3.RESET;
//PurchLine3.SETRANGE("Expected Receipt Date",010318D,TODAY+InventorySetup."Days For Availability");
PurchLine3.SETRANGE("Document Type",PurchLine3."Document Type"::Order);
PurchLine3.SETRANGE(Type,PurchLine3.Type::Item);
PurchLine3.SETRANGE("No.",Item."No.");
IF PurchLine3.FIND('-') THEN BEGIN
  CurrentDate := TODAY;
  IF PurchLine3."Expected Receipt Date" >= CurrentDate THEN
    SalesLine.SETRANGE("Shipment Date",010318D,PurchLine3."Expected Receipt Date")
  ELSE
    SalesLine.SETRANGE("Shipment Date",010318D,TODAY+60);
END ELSE
  SalesLine.SETRANGE("Shipment Date",010318D,TODAY+60);

SalesLine.SETRANGE(Dropped,FALSE);
SalesLine.SETRANGE("Document Type",SalesLine."Document Type"::Order);
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETRANGE("No.",Item."No.");
//SalesLine.SETRANGE("Qty. to Ship",'<> %1',0);
IF SalesLine.FIND('-') THEN BEGIN
  REPEAT
    IF (SalesLine.Quantity - SalesLine."Quantity Shipped" > 0) THEN BEGIN
      QtyAvaiSales += SalesLine."Qty. to Ship";
    END;
  UNTIL SalesLine.NEXT = 0;
END;

PurchLine.RESET;
PurchLine.SETRANGE("Expected Receipt Date",010318D,TODAY);
PurchLine.SETRANGE("Document Type",PurchLine."Document Type"::Order);
PurchLine.SETRANGE(Type,PurchLine.Type::Item);
PurchLine.SETRANGE("No.",Item."No.");
IF PurchLine.FIND('-') THEN BEGIN
  REPEAT
    IF (PurchLine.Quantity - PurchLine."Quantity Received" > 0) THEN
    QtyAvai += PurchLine."Qty. to Receive";
  UNTIL PurchLine.NEXT = 0;
END;
TotalQtyAvai := (Item.Inventory - QtyAvaiSales) + QtyAvai;

IF NOT GLSetup."FZE Company" THEN BEGIN//Meg02.00
  IF NOT (COMPANYNAME = 'QATAR WoodandGas for Trading') THEN BEGIN  //MT 1.00
  AVailabilityFZE := FORMAT('');//Meg02.00
  AVailabilityQatar := FORMAT('');//MT 1.00
  AVailability := DELCHR(FORMAT(TotalQtyAvai),'=',',');
   END
END ELSE BEGIN//Meg02.00
  IF NOT (COMPANYNAME = 'QATAR WoodandGas for Trading') THEN BEGIN  //MT 1.00
  AVailability := FORMAT('');//Meg02.00
  AVailabilityQatar := FORMAT('');//MT 1.00
  AVailabilityFZE := DELCHR(FORMAT(TotalQtyAvai),'=',',');//Meg02.00
  END
END;//Meg02.00

IF COMPANYNAME = 'QATAR WoodandGas for Trading' THEN BEGIN //MT 1.00
   AVailability := FORMAT('');
   AVailabilityQatar := DELCHR(FORMAT(TotalQtyAvai),'=',',');//MT 1.00

END;
// END;

DecProjectedAvaiBal:= 0;
POETADate := 0;
LocProjectedAvaiBal := '';
LocETADate := '';
//Meg03.00+
DecProjectedAvaiBalFZE:= 0;
POETADateFZE := 0;
LocProjectedAvaiBalFZE := '';
LocETADateFZE := '';
//Meg03.00-

//MT 1.00 +
DecProjectedAvaiBalQatar:= 0;
POETADateQatar := 0;
LocProjectedAvaiBalQatar := '';
LocETADateQatar := '';
//MT 1.00 -

IF NOT GLSetup."FZE Company" THEN BEGIN//Meg02.00
//IF (TotalQtyAvai < 0) THEN BEGIN
  PurchLine2.RESET;
  PurchLine2.SETCURRENTKEY("Expected Receipt Date");
  PurchLine2.SETRANGE(Type,PurchLine2.Type::Item);
  PurchLine2.SETRANGE("No.",Item."No.");
  PurchLine2.SETRANGE("Document Type",PurchLine2."Document Type"::Order);
  PurchLine2.SETFILTER("Expected Receipt Date",'> %1',TODAY);

  IF PurchLine2.FIND('-') THEN BEGIN
    PurchLine2.SETRANGE("Expected Receipt Date",PurchLine2."Expected Receipt Date");
    REPEAT
      DecProjectedAvaiBal +=  PurchLine2."Qty. to Receive";
      POETADate := PurchLine2."Expected Receipt Date"-TODAY;
      LocProjectedAvaiBal := DELCHR(FORMAT(DecProjectedAvaiBal),'=',',');
      LocETADate := FORMAT(POETADate);
    UNTIL PurchLine2.NEXT = 0;
  END ELSE BEGIN
    LocProjectedAvaiBal := '0';
    LocETADate := '0';
  END;
  IF DecProjectedAvaiBal <> 0 THEN BEGIN
    DecProjectedAvaiBal := DecProjectedAvaiBal;
    LocProjectedAvaiBal := DELCHR(FORMAT(DecProjectedAvaiBal),'=',',');
  END;
  LocProjectedAvaiBalFZE := '';
  LocETADateFZE := '';
  LocProjectedAvaiBalQatar := '';
  LocETADateQatar := '';

END ELSE BEGIN//Meg02.00
  //Meg03.00+
  IF NOT (COMPANYNAME = 'QATAR WoodandGas for Trading') THEN BEGIN 
  PurchLine2.RESET;
  PurchLine2.SETCURRENTKEY("Expected Receipt Date");
  PurchLine2.SETRANGE(Type,PurchLine2.Type::Item);
  PurchLine2.SETRANGE("No.",Item."No.");
  PurchLine2.SETRANGE("Document Type",PurchLine2."Document Type"::Order);
  PurchLine2.SETFILTER("Expected Receipt Date",'> %1',TODAY);

  IF PurchLine2.FIND('-') THEN BEGIN
    PurchLine2.SETRANGE("Expected Receipt Date",PurchLine2."Expected Receipt Date");
    REPEAT
      DecProjectedAvaiBalFZE +=  PurchLine2."Qty. to Receive";
      POETADateFZE := PurchLine2."Expected Receipt Date"-TODAY;
      LocProjectedAvaiBalFZE := DELCHR(FORMAT(DecProjectedAvaiBal),'=',',');
      LocETADateFZE := FORMAT(POETADateFZE);
    UNTIL PurchLine2.NEXT = 0;
  END ELSE BEGIN
    LocProjectedAvaiBalFZE := '0';
    LocETADateFZE := '0';
  END;
  IF DecProjectedAvaiBalFZE <> 0 THEN BEGIN
    DecProjectedAvaiBalFZE := DecProjectedAvaiBalFZE;
    LocProjectedAvaiBalFZE := DELCHR(FORMAT(DecProjectedAvaiBalFZE),'=',',');
  END;
  LocProjectedAvaiBal := '';
  LocETADate := '';
  LocProjectedAvaiBalQatar := '';
  LocETADateQatar := '';
END;
END;
//Meg03.00-

//MT 1.00 +
IF COMPANYNAME = 'QATAR WoodandGas for Trading' THEN BEGIN 

PurchLine2.RESET;
  PurchLine2.SETCURRENTKEY("Expected Receipt Date");
  PurchLine2.SETRANGE(Type,PurchLine2.Type::Item);
  PurchLine2.SETRANGE("No.",Item."No.");
  PurchLine2.SETRANGE("Document Type",PurchLine2."Document Type"::Order);
  PurchLine2.SETFILTER("Expected Receipt Date",'> %1',TODAY);

  IF PurchLine2.FIND('-') THEN BEGIN
    PurchLine2.SETRANGE("Expected Receipt Date",PurchLine2."Expected Receipt Date");
    REPEAT
      DecProjectedAvaiBalQatar +=  PurchLine2."Qty. to Receive";
      POETADateQatar := PurchLine2."Expected Receipt Date"-TODAY;
      LocProjectedAvaiBalQatar := DELCHR(FORMAT(DecProjectedAvaiBal),'=',',');
      LocETADateQatar := FORMAT(POETADateQatar);
    UNTIL PurchLine2.NEXT = 0;
  END ELSE BEGIN
    LocProjectedAvaiBalQatar := '0';
    LocETADateQatar := '0';
  END;
  IF DecProjectedAvaiBalQatar <> 0 THEN BEGIN
    DecProjectedAvaiBalQatar := DecProjectedAvaiBalQatar;
    LocProjectedAvaiBalQatar := DELCHR(FORMAT(DecProjectedAvaiBalQatar),'=',',');
  END;
  LocProjectedAvaiBal := '';
  LocETADate := '';
    LocProjectedAvaiBalFZE := ''; //MT 1.00
  LocETADateFZE := ''; //MT 1.00
END;

//MT 1.00 -

F1 - Export::OnBeforePassField()

F1 - Import::OnAfterAssignField()

F2 - Export::OnBeforePassField()

F2 - Import::OnAfterAssignField()

ManiDesc - Export::OnBeforePassVariable()

ManiDesc - Import::OnAfterAssignVariable()

PriceSell - Export::OnBeforePassVariable()

PriceSell - Import::OnAfterAssignVariable()

AVailability - Export::OnBeforePassVariable()

AVailability - Import::OnAfterAssignVariable()

LocProjectedAvaiBal - Export::OnBeforePassVariable()

LocProjectedAvaiBal - Import::OnAfterAssignVariable()

LocETADate - Export::OnBeforePassVariable()

LocETADate - Import::OnAfterAssignVariable()

AVailabilityFZE - Export::OnBeforePassVariable()

AVailabilityFZE - Import::OnAfterAssignVariable()

LocProjectedAvaiBalFZE - Export::OnBeforePassVariable()

LocProjectedAvaiBalFZE - Import::OnAfterAssignVariable()

LocETADateFZE - Export::OnBeforePassVariable()

LocETADateFZE - Import::OnAfterAssignVariable()

AVailabilityQatar - Export::OnBeforePassVariable()

AVailabilityQatar - Import::OnAfterAssignVariable()

LocProjectedAvaiBalQatar - Export::OnBeforePassVariable()

LocProjectedAvaiBalQatar - Import::OnAfterAssignVariable()

LocETADateQatar - Export::OnBeforePassVariable()

LocETADateQatar - Import::OnAfterAssignVariable()

The problem is, we are exporting data from multiple Companies (WAG, FZE, ITG and Qatar), each on separate CSV file, is it possible to modify the code to Export data from all our departments into 1 CSV file only ?

I was reading the forums here and found out it’s something called CrossCompanies, but didn’t understand how to add it to my client code as I’m a PHP developer.

Any recommendations are welcome.

Best regards,

If all your departments are located in the same database and just different companies, you can achieve this goal by looping using CHANGECOMPANY function (and loop virtual table 2000000006 Company for companies).
However, it seems that you already have a partner who has created this XMLport for you, so I’d suggest you to contact that Partner for modifications in the XMLport.

1 Like