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,
