Cowmaster's Corner: April 2019

Monday, April 29, 2019

NAV - How to create a Codeunit to export to Text File for X12 EDI

https://ediacademy.com/blog/ansi-x12-manual/

https://www.youtube.com/watch?v=46L_e832dGk

XMLPorts cannot meet the requirements of the EDI structure

Instead, build a codeunit that writes a text file in the exact format you need

Sender/Receiver qualifier ID's and DUNS

https://medium.com/orderful/edi-transaction-setup-isa-id-20d0ba0b7716
Qualifier codes

http://ecomgx17.ecomtoday.com/edi/EDI_4010/elI05.htm

Examples

http://www.excellware.com/docs/dynamo/tech/EDI.htm

Resources to help identify the file structure

Download EDI Notepad to view EDI files and confirm if they meet the basic structure requirements

https://liaison.opentext.com/products/integrate/edi-notepad/

CAL to Create Text file and write to it

http://www.navision-girl.com/2007/08/create-and-write-to-text-file-from.html

File1.CREATE( 'C:\Output.txt');
File1.CREATEOUTSTREAM(OutStreamObj);

OutStreamObj.WRITETEXT ('First line of text that you want to write to text file');
OutStreamObj.WRITETEXT ('This is not the second line of text to be outputed but to concatenate with the first line');

OutStreamObj.WRITETEXT(); // This command is to move to next line
OutStreamObj.WRITETEXT('Second line will start here') ;

File1.Close; // To end the writing and write out to the file.

Friday, April 26, 2019

NAV CAL - Read File using BigText Instream. Separate single line into multiple lines on delimiter

FileMgt.GetServerDirectoryFilesList(Filelist,EDITemp."Input File Location");
//Importfile
IF Filelist.FIND('-') THEN BEGIN
REPEAT
LC := 0;
Path := Filelist.Name;
InputFile.WRITEMODE(FALSE);
InputFile.TEXTMODE(TRUE);
InputFile.OPEN(Path);
intLen := InputFile.LEN;
WHILE InputFile.POS < intLen DO BEGIN
InputFile.CREATEINSTREAM(FileInStream);
WHILE NOT FileInStream.EOS DO BEGIN
Buffer.READ(FileInStream);
END;
Lineend := 1;
Buffer2 := Buffer;
WHILE Buffer2.TEXTPOS(EDITemp."Segment Terminator") > 0 DO BEGIN
Buffer.GETSUBTEXT(Buffer2,Lineend,Buffer.LENGTH);
Lineend := Buffer2.TEXTPOS(EDITemp."Segment Terminator") + 1;
LC := LC + 1000;
VEL.INIT;
VEL.Line := LC;
VEL.Filename := Filelist.Name;
VEL.DateRead := CURRENTDATETIME;
Buffer2.GETSUBTEXT(VEL.Detail,1,Lineend);
VEL.INSERT;
Buffer := Buffer2;
END;
END;
InputFile.CLOSE;
COMMIT;
UNTIL Filelist.NEXT <= 0
END;

Name DataType Subtype Length
FileMgt Codeunit File Management
EDIFile Record Vendor EDI Files
EDITemp Record Vendor EDI Template
Filelist Record Name/Value Buffer

Name DataType Subtype Length
ServerFile Integer
Path Text 250
InputFile File
VEL Record Vendor EDI Lines
intLen Integer
LC Integer
FileInStream InStream
Buffer BigText
Lineend Integer
Buffer2 BigText

NAV CAL - Get a list of filenames from a windows directory

https://juhl.blog/2017/07/05/get-filelist-using-net/

Create variables

FileMgt = C419
FileList = T823

FileMgt.GetServerDirectoryFilesList(Filelist,'C:\MyFolder');
This populates the 823 with all filenames in that directory
Cycle through 823 to deal with each record/file

823 structure

ID Name Value

1 C:\AS2\IMPORT\106041-20190121.xml 106041-20190121

Thursday, April 25, 2019

NAV CAL - How to create a Location Browse Field to capture a file location

Enable Ellipsis from AssistEdit

Page Field>Properties>Assistedit = Yes

Create Variable for FileMgt = Codeunit 418
Add code to launch file browse screen and capture location to a field "Input File Location"

FileMgt.SelectFolderDialog('',"Input File Location");

Wednesday, April 24, 2019

Dynamics NAV - CAL and FTP Management

Dynamics NAV - CAL and SFTP Management

SFTP is NOT FTP. It is a completely different protocol.

Get WinSCP assembly files here

https://winscp.net/eng/docs/library
Download and install
Copy C:\Program Files (x86)\WinSCP\WinSCP.net to C:\Program Files\Microsoft Dynamics NAV\110\Service\Add-ins\WinSCPNet
Restart Service

Get sample FOB here

https://dynamicsuser.net/nav/b/ara3n/posts/moving-files-in-nav-with-ftp
Extract, import to nav

Merge T472
Design C50009

View>Locals>Variables
For each DotNet variable, click ellipsis and re-map to current WinSCP

Get Test server here

https://labs.rebex.net/tiny-sftp-server
Install

Configure options in NAV

Ensure paths exist
Enter Parameter
Set SFTP Host Key

Login to server using WinSCP client>Session>Generate UDL Code
copy the -hostkey string, it should look like this

ssh-rsa 2048 mjx56ZXynI67txcOoUu2+anhpc9yPvSjp3ERCozWjE4=

Thursday, April 18, 2019

NAV - Fixed Assets, Straight Line Depreciation, Monthly Depreciation, 30 days

Confirm Setup

Depr. Books>Navigate>FA Journal Setup
Enter User to allow FA entries

Create a Fixed Asset

Assign it to a book, straight line, 5 depreciation years (60 months)
Post acquisition at 7-Feb-2018 for $4245.39
Set Depreciation Start Date 1-Mar-2018 (We want to start depreciating as at the next month)
Run Depreciation for 31-Mar-2018

You should get an fa g/l entry for 70.76 for 30 days
As long as you run depreciation between the first and last day of any month, it should calculate based on 30 days

If you try to run it in the middle of the month, it will calculate based on exact number of days between dates, and end up calculating accurately, but incorrectly due to the 28,31,30 days that may exist between the dates.
Add Vat Bus Posting Group info
Insert Bal Line

Add Vat Bus Group Info
Enter Gen. Posting Type

Disposals must be negative

Wednesday, April 17, 2019

SQL Database is in "Pending Recovery" state

https://www.stellarinfo.com/blog/fix-sql-database-recovery-pending-state-issue/

--Option1

----------------------------------------------------

ALTER DATABASE [DBName] SET EMERGENCY;

GO

ALTER DATABASE [DBName] set single_user

GO

DBCC CHECKDB ([DBName], REPAIR_ALLOW_DATA_LOSS) WITH ALL_ERRORMSGS;

GO

ALTER DATABASE [DBName] set multi_user

GO

------------------------------------------------------------------

--Option2

ALTER DATABASE [DBName] SET EMERGENCY;

ALTER DATABASE [DBName] set multi_user

EXEC sp_detach_db ‘[DBName]’

EXEC sp_attach_single_file_db @DBName = ‘[DBName]’, @physname = N'[mdf path]’