Excel changing link paths when opened from UNC vs Mapped Drive

Behaviour of links in excel is described in this good document.

Description of link management and storage in Excel

Looking at an excel cell when document opened via UNC path vs Looking at excel cell when document opened via mapped drive path:

=+VLOOKUP(C10,'\\domain.net\folds\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)
=+VLOOKUP(C10,'N:\USERS\design\bill\[Costings.xls]Sheet1'!GpData,2,FALSE)

The actual UNC path should be as below, the “files” folder has been left out by Excel, thus the links are broken when the Excel file is opened via UNC vs Mapped Drive.

\\domain.net\files\folds\USERS\design\bill\

For preservation of content, here is the relevant parts, but check the link for latest version:

How link paths are handled when a file opens
When Excel opens a file that contains links (linked workbook), it combines the portions of the links stored in the file with the necessary portions of the current path of the linked workbook.
How link paths are stored
When Excel stores the path to a linked file, it uses the following rules to determine what to store.
Note Moving up a path indicates that you are referring to folders moving away from the root drive or share. Moving down a path indicates that you are moving closer to the root drive or share.
  • If the linked file and the source data file are not on the same drive, the drive letter is stored with a path to the file and file name.
  • If the linked file and the source data file are in the same folder, only the file name is stored.
  • If the source data file is located in a folder that is nested in the same root folder as the linked file, a property is stored to indicate the root folder. All portions of the path that are shared are not stored.
    For example, if the linked file C:\Mydir\Linked.xls is dependent on C:\Mydir\Files\Source.xls, the only portion of the path that is stored is \Files\Source.xls.
  • If the source data file is one folder down from the linked file, a property is stored to indicate this.
    For example, the linked file is C:\Mydir\Files\Myfile\Linked.xls and the source data file is C:\Mydir\Files\Source.xls. Excel stores only \MyDir\Files\ .. \Source.xls.
    Note This allows a link to be maintained when the linked file is copied to an additional sub folder of the folder that the source file is located in.
    For example, the linked file is C:\Mydir\Files\Myfiles1\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, the linked file, Linked.xls, is copied from the folder C:\Mydir\Files\Myfiles1 to a folder named C:\Mydir\Files\Myfiles2, and the link to C:\Mydir\Files\Source.xls is maintained.
  • If the source data file is located in the XLStart, Alternate Startup File Location, or the Library folder, a property is written to indicate one of these folders, and only the file name is stored.
    Note Excel recognizes two default XLStart folders from which to automatically open files on startup. The two folders are as follows:
    • The XLStart folder that is in the Office installation folder, such as C:\Program Files\Microsoft Office\Office folder\XLStart
    • The XLStart folder that is in the user's profile, such as C:\Documents and Settings\username\Application Data\Microsoft\Excel\XLStart
    The XLStart folder that is in the user's profile is the XLStart folder that will be stored as a property for the link. If you use the XLStart folder that is in the Office installation folder, that XLStart folder is treated like any other folder on the hard disk.
    The Office folder name changes between versions of Office. For example, the Office folder name is Office, Office10, Office11 or Office12, depending on the version of Office that you are running. This folder name change causes links to be broken if you move to a computer that is running a different version of Excel than the version in which the link was established.
It is also important to note that what appears in the formula bar is not necessarily what is stored. For example, if the source data file is closed, you see a full path to the file, although only the file name may be stored.
Relative vs. absolute links
Links to external workbooks are created in a relative manner whenever possible. This means that the full path to the source data file is not recorded, but rather the portion of the path as it relates to the linked workbook. With this method, you can move the workbooks without breaking the links between them. The links remain intact, however, only if the workbooks remain in the same location relative to each other. For example, if the linked file is C:\Mydir\Linked.xls and the source data file is C:\Mydir\Files\Source.xls, you can move the files to the D drive as long as the source file is still located in a subfolder called "files".
Relative links may cause problems if you move the linked file to different computers and the source is in a central location.
Mapped drives vs. UNC
When a source data workbook is linked, the link is established based on the way that the workbook was opened. If the workbook was opened over a mapped drive, the link is created by using a mapped drive. The link remains that way regardless of how the source data workbook is opened in the future. If the source data file is opened by a UNC path, the link does not revert to a mapped drive, even if a matching drive is available. If you have both UNC and mapped drive links in the same file, and the source files are open at the same time as the destination file, only those links that match the way the file was opened will react as hyperlink. Specifically, if you open the file through a mapped drive and change the values in the source file, only those links created to the mapped drive will update immediately.
The link displayed in Excel may appear differently depending on how the workbook was opened. The link may appear to match either the root UNC share or the root drive letter that was used to open the file.
Scenarios that may cause links to not work as expected
There are several circumstances in which links between files can be inadvertently made to point to erroneous locations. The following are two of the most common scenarios.
Scenario 1:
  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You create links to a workbook that is stored at the mapped location after you open the file through that mapped drive.
  3. You open the file by a UNC path.
  4. As a consequence the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1. In other words, the Share name is eliminated from the path.
Scenario 2:
  1. You map a drive under the root of a share. For example, you map drive Z to \\Server\Share\Folder1.
  2. You open the file by a UNC path or a mapped drive mapped to a different folder on the share, such as \\Server\Share\Folder2.
  3. As a consequence, the link will be broken.
If you close the file without saving it, the links will not be changed. However, if you save the file before you close it, you will save the links with the current broken path. The folders between the root of the share and the mapped folder will be left out of the path. In the example above, the link would change to \\Server\Folder1.

Dynamics GP next sales order number is corrupted as has become the same as the next quote number range

This problem happened today, first time for a few years, but it used to happen regularly (every few months). The issue is that the auto populated sales order number in sales transaction entry gets overwritten with the number from the quotation number range.

My guess for the circumstances of this issue, is that the SOP Entry window is open with a quote showing, but not actually started yet. Somehow the order type gets changed from quote to order in the forms internal variables (integration or misbehaving code or connection lost or something?).

The Sop Entry window is then closed, or the order type drop down is changed to order from quote, causing the GP window to attempt to return the unused quote number to the pool to be reused via the next sop number field in sop setup.

However as we are saying that the window variable that holds order type is incorrect and thinks this is a quotation, the window actually returns this quote number to the next order number field, when it should have returned it to the next quote number field. Hence the next sales order number becomes a quote number! eek!Surprised smile

Ok, to fix this is easy, right? -open SOP setup by navigating to Microsoft Dynamics GP>>Tools>>Setup>>Sales>>Sales Order Processing then click [Numbers] button.

Next SOP number in Dynamics GP

Correct the next document number to what it should be (see SQL later to find this), note that you need to close both windows to ensure it saves, and be quick if users are generating numbers you will get a optimistic concurrency violation on save. The problem you then encounter is that if you have distributed sales force, spread over the globe, they will have picked up numbers from the incorrect sop number range, and have them sitting like a time bomb on new sales orders in the sop entry window. Even when you change the number in the sop set up window, if any of those users choose to subsequently close the Sop Entry window, having not used the erroneous sop number, then that number will again get returned to the sop next number field in sop setup, undoing your good work! This is exactly what happens, the number keeps jumping back to the quote range of numbers and you fight a losing battle against the users.

One answer is to ask all users to exit sop entry, make the change then let them back in, with a distributed work force, that may not be actually easy to contact or worse, not in front of the machines when you need to make the change, this is not practical. In this case, I have used the script below to keep resetting the number so immediately the users corrupts the next sop number, it gets corrected again.

Leave the script below running in SSMS until you are confident all users are now on the correct range of numbers.

DECLARE @NextSopNumber CHAR(21)

SET @NextSopNumber = '01447267'

WHILE 1 = 1
BEGIN
IF EXISTS (
SELECT SOPNUMBE
FROM SOP40300
--Here SOPTYPE = 2 is an Order - could be changed to target another type
WHERE SOPTYPE = 2
AND SOPNUMBE NOT LIKE
-- Make a like stub from the next number to see that the number has not jumped
CONCAT (
SUBSTRING(@NextSopNumber, 1, LEN(@NextSopNumber) - 3)
,'%'
)
)
BEGIN
UPDATE SOP40300
SET SOPNUMBE = @NextSopNumber
WHERE DOCTYABR = 'ORD'
END

PRINT 'WAIT'
-- 5 second delay then check again
WAITFOR DELAY '00:00:05'
END

In the above script, set the next sop number in the above script from the number that looks reasonable as a last number from running, looking at the SOPTYPE for the correct document type in question:

SELECT TOP(40)* FROM SOP10100 order by DEX_ROW_ID DESC

So long as the next SOPNumber is within 1000 of the actual last number used, then the GP client will scroll up through, finding the actual next sop number that is available for allocation. The exact number is not critical, and it wont matter until 1000 orders have been entered. After 1000 you will get an error message saying a new sop number could not be found. 
 
Do let me know if this was helpful, in the comments, keeps me motivated to write more!

Dynamics GP - Manual Payment does not generate next cheque/check number or document number

Manual Payment empty document number, not defaulted

When making a manual payment normally, after selecting the payee, the cheque book attached to that payee is queried for its next cheque number, that is then auto populated into the “Document No” field shown.

Payables Manual Payment Entry

During month end we found all of a sudden this was not happening, instead the document number was being left blank. The previous payment only ten minutes earlier had been fine, correctly generating the number.

Cash receipts entry required field for Cheque/Card Number


Shortly after Sales ledger started complaining that cheque number field in cash receipts had suddenly become mandatory.

 Cash Receipts Entry

See in the above screen how the Cheque/Card Number is bold, indicating it is now mandatory, before this was not the case.

Remove Creditor Hold Password set to default

A third event also happened at the same time, which was that the password on payables for removing creditor holds, that previously was blank, suddenly had defaulted back to its default value of ACCESS.

Password defaulted

After some digging I figured out the cause of this was that the Payment Document Management module had been switched on somehow and had not been configured so was causing the blanks.

.

Payment Document Management 2150 PMNTDOC.DIC
2150FRM.DIC
2150RPT.DIC

Navigating to Dynamics GP>> Tools>> Setup>> Company>> Payment  Document Setup
Unchecking both check boxes turned off the module and behaviour resumed as per normal.

Company Payment Document Management

 

It is a mystery how this occurred but maybe someone else one day might find this post useful, if so do comment!

Correct reconcile Dynamics GP SOP batch totals and transaction counts without SQL

Sometimes batch totals and transaction counts get corrupted in GP, there can be many reasons behind this.

To correct them the check links maintenance routine can be executed for “Sales Work”. This will correct the batch totals but will take a very long time to run and is not the sort of thing to be doing during working hours.

Alternatively corrections can be made via SQL which is quick and can address multiple batches,

Reconciling SOP Batches - The Dynamics GP Blogstersome thought needs to be put into batch locking etc if this is to be used regularly as the script in its current form does not check if it is “allowed” to update the batch totals.

Similar post: Reconciling SOP Batches from WITHIN Dynamics GP

Today I found out another way that I thought worth recording for future reference, printing an edit list for the batch. Although this can only be used one batch at a time, it is quick and “built in” to GP.

Instructions

Go to the Sales Batch Entry Window…

Transactions>>Sales>>Sales Batches

2017-06-22_09-32-37

Select the SOP batch of interest and click the print icon.

2017-06-22_09-32-53

In the Form to Print combo box, select Edit List and then click the print button.

Select a destination (to screen) in the print destination window that follows. After printing, leaving the batch and then going back into the batch, the totals will be found to be corrected.

Thanks for the hint from Jorge Mejia on GP forums: https://community.dynamics.com/gp/f/32/p/241273/665571#665571