NAV - Reverse Purchase Invoice Item Charges

• Departments/Financial Management/Payables/Purchase Credit Memos
• Create new Credit Memo
• Enter Vendor
• Get Posted Document Lines to reverse
• Select Lines to reverse
• Click Line>Item Charge Assignment
• Get Receipt Lines
• Select the lines to apply the credit to
• Post Credit
• Now you can redo a new purchase invoice for the correct amount, and redo your item charges
• Be sure to run your adjust costs afterwards

NAV - Jet Reports - How to build a report

• Install Jet
• Launch Excel
• Jet>Data Source Settings>Define Jet Data source
• Connecting to the Cube will enable different tools than connecting to a regular ODBC
• Use these tools to build parts or all of your report
• Row 1 and Column A are restricted and designated for Jet system functions, do not enter values here
• A2 - Keyword Option will display value as an option in report
• B1 - Keyword Title will display value as title in report
• B2 - Keyword Value will display value as an editable field in report
• From B2 onward, you can enter values and labels that can be referenced in formulas
• Formulas (JfX)
• Jet reference
• https://support.jetglobal.com/hc/en-us/articles/218953168-NL-Function-Reference
• Other References
• https://community.dynamics.com/nav/b/technicaltipsandtricksfordynamicsnav/posts/creating-report-in-jet-using-nl-nf-np-amp-gl-amp-excel-formulas
• NF
• NL
• NP
• GL
• Report Wizard
• Select Table
• Select Filter, group, sort options
• Generate simple report off of a single table, similar to a standard pivot table report, but using Jet function syntax
• Direct Query off of Database takes very long
• Generally useless
• Table Builder
• Allows you to build views to join tables and add function logic
• Same as using Excel Query builder
• Slightly less useless
• Pivot Table (Cube Only)
• Will generate pivot table off of cube
• Same functionality as connecting directly to cube from Excel

• Scheduler
• Schedule report to email

Clicklearn - How to change the default logo

• Create a recording
• Select it
• Tools>Preferences>Template>Click pencil icon next to "Recording Template"
• New Window will open
• Click Preferences
• Cick Logo Ellipsis>Select new logo
• Save as>Template Name
• Close window
• Select Template name to apply template to current recording
• Click Produce

NAV - Web Client subform lines do not display

Put the subform in a group by itself.
Indent the entire group left to make it at the same level as the top group.

NAV CAL - Delete old files by created date using File Management

Name DataType Subtype Length
FileMgt Codeunit File Management
FileSystemObject Automation 'Microsoft Scripting Runtime'.FileSystemObject
TheFile Automation 'Microsoft Scripting Runtime'.File

Name DataType Subtype Length
FileList Record Name/Value Buffer
FileDateCreated Text 50
DeleteOlderThan Text 50


DeleteOldFiles()
CREATE(FileSystemObject,FALSE,TRUE);
IF VET.FIND('-') THEN BEGIN
  REPEAT
    FileMgt.GetServerDirectoryFilesList(FileList,VET."Archive File Location");
      IF FileList.FIND('-') THEN BEGIN
        REPEAT
          TheFile := FileSystemObject.GetFile(FileList.Name);
          FileDateCreated := FORMAT(TheFile.DateCreated);
          DeleteOlderThan := FORMAT(EndLogDate);
          IF FileDateCreated <= DeleteOlderThan THEN BEGIN
            FileMgt.DeleteClientFile(FileList.Name);
            //MESSAGE('File date:'  + FileDateCreated + 'Delete older than' + DeleteOlderThan + ' file deleted.');
          END;
        UNTIL FileList.NEXT <= 0;
      END;
  UNTIL VET.NEXT <=0;
END;

NAV CAL XMLPORTS - Exporting to XML generates a blank file

In this specific case, my XMLPort was mapped to a field that used to be a flowfield, but got changed to regular text, and the calcfield on that old flowfield was causing a silent error you could not see unless you ran the xmlport manually.

SQL Machine and Port Repeated Connection Testing

Repeating Ping SQL Port in Powershell with log


$Logfile = "C:\Users\temp\Desktop\testing\$(gc env:computername).log"

while($true)
{
$server="mydb"; $port=1433; echo((new-object Net.Sockets.TcpClient).Connect($server,$port)) "$server is listening on TCP port $port";
Add-content $Logfile -value "$server is listening on TCP port $port"
Start-Sleep -Seconds 5
}





Repeated Ping ip in batch file with log

@ECHO OFF
set IPADDRESS=x.x.x.x
set INTERVAL=5
:PINGINTERVAL
ping %IPADDRESS% -n 1 >> filename.txt
timeout %INTERVAL%
GOTO PINGINTERVAL

SQL - How to test connection to SQL easily

Create a file, change the extension to udl.
Run it, fill in connection info and test.

NAV - Disable Fields or Subform based on field

On Page

OnAfterGetCurrRecord()
//Lock all fields when posted
IF Posted THEN BEGIN
  EditAI := FALSE;
END ELSE BEGIN
  EditAI := TRUE;
END;

On each field or part you want to disable, in line properties
Editable = EditAI

Batch File - How to check if a folder is empty, then upload to FTP, and then move files if error or not using WinSCP

echo on

call :ReportFolderState "C:\FTPS\EXPORT"
Pause
@exit /b

:ReportFolderState
@call :CheckFolder "%~f1"
@set RESULT=%ERRORLEVEL%
@if %RESULT% equ 999 @echo Folder doesn't exist
@if %RESULT% equ 0   (@echo Folder is Empty!)
@if %RESULT% equ 1   goto Notempty
@exit /b

:CheckFolder
@if not exist "%~f1" @exit /b 999
@for %%I in (""%~f1"\*.*") do @exit /b 1
@exit /b 0

:Notempty
@echo Not Empty!
setlocal enableextensions

    for %%a in ("%~f1\*.xml") do (
        set "fileName=%%~na"
set "NamePath=%%~fa"
echo "%%~fa"
        setlocal enabledelayedexpansion
goto Sendfile
)
exit /b

:SendFile
echo %Namepath%
@echo sending File!
"C:\Program Files (x86)\WinSCP\WinSCP.com" ^
  /command ^
    "open ftpes://myftpsite.com" ^
    "lcd ""%~f1""" ^
    "cd /" ^
    "put %Namepath%" ^
    "exit"

set WINSCP_RESULT=%ERRORLEVEL%
if %WINSCP_RESULT% equ 0 (
  move %Namepath% C:\FTPS\ARCHIVE
  echo Uploaded Successfully
) else (
  move %Namepath% C:\FTPS\ERROR
  echo Error Uploading
)

exit /b %WINSCP_RESULT%

Smartconnect - How to get Quantity Available from SQL before passing to Sales Line for Lot quantity without requiring override

'----------------------------------- CONFIGURATION -------------------------------------
Dim server As String = "TEST"
Dim database As String = "TEST"
Dim user As String = "test"
Dim password As String = "test"

Dim myItem As String = _ITEMNMBR
Dim mySite As String = _SITEID
Dim myQty as double = 0
'Dim myQtyString as string = ""

'---------------------------------------------------------------------------------------

    'Declare the connection string based on the configuration variables
    Dim conString As New String("Data Source=" & server & ";Initial Catalog=" & database & ";User=" & user & ";Password=" & password & ";")
 
    'Define the query command to be run
    Dim execute As String = "select qtyonhnd-atyalloc as qtyavlbl from iv00102 where itemnmbr = '" & myItem & "' and locncode = '" & mySite & "'"
   'Microsoft.VisualBasic.MsgBox(execute)

    'Define the SQL connection and open it
    Dim myConn As System.Data.SqlClient.SqlConnection = New System.Data.SqlClient.SqlConnection(conString)
    myConn.Open()

    'Declare the SQL command as the query string
    Dim myCmd As System.Data.SqlClient.SqlCommand = New System.Data.SqlClient.SqlCommand(execute, myConn)

    'Define the SQL Reader and execute it
    Dim sqlReader As System.Data.SqlClient.SqlDataReader = myCmd.ExecuteReader()

 sqlReader.Read()
myQty = Decimal.Parse(sqlReader("qtyavlbl"))
'myQtyString = sqlReader("qtyavlbl").ToString

IF _QUANTITY < myQty THEN
myQty = _QUANTITY
END IF
'Microsoft.VisualBasic.MsgBox(myQty)
'Microsoft.VisualBasic.MsgBox(myQtyString)
Return  myQty

Dynamics GP - Serial numbers are not available for selection, and the numbers cannot be used when attempting to adjust in

The numbers have already been used in the iv00200 table without proper receipt links

Find all records that are missing proper receipt links
select * from iv00200 where itemnmbr = 'myitemno' and RCTSEQNM <0

Delete the records from iv00200



Adjust the numbers back in using an inventory adjustment

NAV CAL - How to insert dimensions using Shortcut dimension field

 GJL.VALIDATE(GJL."Shortcut Dimension 1 Code",MyDimensionValue);

This will work as long as it is the LAST field you validate on the journal entry.
If you attempt to validate any other fields after this line, it may remove the dimension that has already been validated.

NAV - How to setup Dimensions and Dimension Values

• Departments/Administration/Application Setup/Financial Management/Dimensions/Dimensions
• Customize Ribbon>Add Dimension Menu
• Click Dimension Values

NAV - FTPS for NAV

http://navcraft.fr/using-ftp-over-ssl-ftps-in-nav/

NAV CAL - How to update or insert a record on a PO

UpdatePOLineToMatchAILine(AILine : Record "Advance Invoice Lines")
PH.SETRANGE("No.",AILine."PO Number");
ReleasePurchDoc.PerformManualReopen(PH);
IF PL.FIND('-') THEN BEGIN
  PL.SETRANGE("Document No.",AILine."PO Number");
  PL.SETRANGE("Line No.",AILine."PO Line");
  IF PL.FIND('-') THEN BEGIN
  //Modify existing PO Line
    //PL.VALIDATE("Unit of Measure Code",AILine."Invoice UofM");
    PL.VALIDATE(Quantity,AILine."Invoice Quantity");
    PL.VALIDATE("Direct Unit Cost",AILine."Invoice Unit Price");
  END ELSE BEGIN
  //Insert new PO Line
    PL.INIT;
    PL.VALIDATE("Document Type",PH."Document Type");
    PL.VALIDATE("Document No.",PH."No.");
    PL.VALIDATE("Buy-from Vendor No.",PH."Buy-from Vendor No.");
    PL.VALIDATE(Type,PL.Type::Item);
    PL.VALIDATE("No.",AILine."Item Number");
    PL.VALIDATE("Location Code",PH."Location Code");
    PL.VALIDATE("Unit of Measure Code",AILine."Invoice UofM");
    PL.VALIDATE(Quantity,AILine."Invoice Quantity");
    PL.VALIDATE("Direct Unit Cost",AILine."Invoice Unit Price");
    PL.INSERT;
  END;
END;
ReleasePurchDoc.PerformManualRelease(PH);