Dynamics GP will not remove hold or why timestamp format in GP matters

If Dynamics GP will not let a SOP module, transaction process hold be removed in the user interface, it may be due to formatting of time stamps. Check no integrating applications have created that hold incorrectly. 

The issue is that GP is real fussy about formats of timestamps, one place time stamps can cause problems is in use with the SOP Transaction Hold table, the following query illustrates what you need to know about this. 

SELECT [PRCHLDID], [HOLDDATE], [TIME1]
FROM SOP10104
WHERE SOPNUMBE='YourDocumentNumber' AND SOPTYPE=2

This query gives us some data to look at, that is if some holds exist for that document supplied to the SQL query...

The first thing to notice is that GP often splits/spreads the Time and the Date components of the point in time over two fields, in this case the point in time is represented by HOLDDATE and TIME1. These two fields are of the sql data type "datetime", something that can catch out people when new to GP databases as it may not be totally obvious what is going on. 

Lets look at the first row, it is for the hold type "CREDIT" and represents the point in time, 3rd of May 2020 at eleven minutes past three and 31 seconds

  • It is interesting and important to note that for the HOLDDATE field, it has a date but see how the date has zeros for all the time elements of that HOLDDATE datetime value. To fill up the unused time component, the time has been defaulted to midnight. A default time of zero (midnight) is used, as the unused half of the datetime sql data type may not be set to null (we need all those bytes). 
  • Now also note how the TIME1 field value has a time component, but the date has been defaulted to a "default" of the 1st of January 1900. This date by convention is used to fill the date component of the time, as the unused half of a datetime data type may not null. 
  • Further, it is vital to note that the milliseconds of the time has been zeroed too

Now if you disobey these format rules, the GP user interface will do weird things. More often than not, it just doesn't do anything, that can be really perplexing as a problem solve because the records to the untrained eye look fine and similar to other records right next to them. It may be as simple as including milliseconds (not zeroing them) that can prevent a user from removing a hold in the GP user interface!

These time stamps are used in this way for many tables, where the same rules apply. It is very important to provide a correctly formatted time and date field if integrating or manually scripting against the GP database. This is a really common rookie mistake for developers working with GP, its easy to get lazy or not notice the formatting to be wrong. It can also change behaviour and totals, as time span calculations may not result in the same outcome if time components are left in date fields etc. Imagine the impact when totalling values over time spans, urgh.  

So to save some head scratching, and to make a GP compatible timestamp for the current time field in .NET use the following code snippet:

DateTime now = new DateTime(1900, 1, 1).Add(DateTime.Now.TimeOfDay);
DateTime ForFieldUse= now - new TimeSpan(0, 0, 0, 0, now.TimeOfDay.Milliseconds);

Don't be tempted to simplify this and try to access the .now in two places on the same statement as you may find the times have shifted enough between calls to cause the milliseconds not to zero correctly as two different values would be returned and be subtracted, not resulting in zero! Date times are immutable so you can't simply zero the milliseconds component either.

To get the date only value for use in GP date fields, you may simply use the in build .NET method ".Today" as shown in this snippet;Snippet

DateTime.Today;

 

 Hopefully this will help get back up and running after Googling the problem you've just encountered! - If so do comment, it keeps me motivated to blog more.

Installing Remote Server Administration Tools for Windows 10

Note to self, run following where the add features option in windows doesn't work. 

DISM.exe /Online /add-capability /CapabilityName:Rsat.ActiveDirectory.DS-LDS.Tools~~~~0.0.1.0 /CapabilityName:Rsat.BitLocker.Recovery.Tools~~~~0.0.1.0 /CapabilityName:Rsat.CertificateServices.Tools~~~~0.0.1.0 /CapabilityName:Rsat.DHCP.Tools~~~~0.0.1.0 /CapabilityName:Rsat.Dns.Tools~~~~0.0.1.0 /CapabilityName:Rsat.FailoverCluster.Management.Tools~~~~0.0.1.0 /CapabilityName:Rsat.FileServices.Tools~~~~0.0.1.0 /CapabilityName:Rsat.GroupPolicy.Management.Tools~~~~0.0.1.0 /CapabilityName:Rsat.IPAM.Client.Tools~~~~0.0.1.0 /CapabilityName:Rsat.LLDP.Tools~~~~0.0.1.0 /CapabilityName:Rsat.NetworkController.Tools~~~~0.0.1.0 /CapabilityName:Rsat.NetworkLoadBalancing.Tools~~~~0.0.1.0 /CapabilityName:Rsat.RemoteAccess.Management.Tools~~~~0.0.1.0 /CapabilityName:Rsat.RemoteDesktop.Services.Tools~~~~0.0.1.0 /CapabilityName:Rsat.ServerManager.Tools~~~~0.0.1.0 /CapabilityName:Rsat.Shielded.VM.Tools~~~~0.0.1.0 /CapabilityName:Rsat.StorageReplica.Tools~~~~0.0.1.0 /CapabilityName:Rsat.VolumeActivation.Tools~~~~0.0.1.0 /CapabilityName:Rsat.WSUS.Tools~~~~0.0.1.0 /CapabilityName:Rsat.StorageMigrationService.Management.Tools~~~~0.0.1.0 /CapabilityName:Rsat.SystemInsights.Management.Tools~~~~0.0.1.0



Find orphan note records in a Dynamics GP company database

About the Dynamics GP Notes table

Notes are held within the SY03900 company table in Dynamics GP. Every note in that notes table has a NOTEINDX, which is used by other tables in GP to reference that note. This makes the notes system extensible, when new modules/addins are created for GP,  those modules can simply piggy back off the existing notes table for their notes too by inserting notes into the table and referring to them by index. However this does have the disadvantage that looking at a random note in the notes table, you have no idea as to what records may be referencing that note. 

Hence, if you were to delete records from tables that reference note records, it orphans any note record for that deleted record. The note record can still exist, but the record that refers to it has been removed. An example of where this could happen is, if someone were to delete a sales order line using SQL DELETE, without also deleting the note record in SY03900 that is referenced by that sales order line record. 

You see, the note table does not have a "source" field to identify the "owner" table, so there is nothing in the note record to indicate from what table the note record originated. Thus you must check all the NOTEINDX fields in all the tables over the entire company database in order to find its owner.

Lucky for us, using dynamic SQL we can do this. We can query SQL server for the table definitions of all the tables in the database containing the field named NOTEINDX. We can then copy all the values that exist from each of those tables found into one big list of NOTEINDX values. Finally we can compare the notes table with that extracted big list of all valid values and find where we have a value in the notes table that does not exist in any of the tables in the GP company database. These will (most likely) be orphan records. Note you must exclude the SY03900 table itself when preparing this consolidated list!

Dynamic SQL to find all references to note records

The following script does this. It looks for any table that have a field named NOTEINDX and inserts all the note index numbers from those tables and fields into a temp table, from where you may join it back to the notes table to find the notes that no longer have reference in the database (orphaned). 

If you are then going to use the results from this to delete notes, beware as if you have a third party product that uses notes but does not name its reference to the note as NOTEINDX, so I'm saying use this with care, especially if you start removing notes based on it, check what they are first and gain confidence they are genuine orphans.

 

IF EXISTS (
SELECT *
FROM tempdb..sysobjects
WHERE NAME = '##NotesConsolidated'
)
DROP TABLE dbo.##NotesConsolidated

SET NOCOUNT ON

CREATE TABLE ##NotesConsolidated (
NOTEINDX numeric(19,5)
,TableName VARCHAR(1000)
)
GO
DECLARE @SqlStatement VARCHAR(500)
DECLARE @DatabaseName AS VARCHAR(5)
SET @DatabaseName = cast(db_name() AS VARCHAR(5))

DECLARE This_cursor CURSOR
FOR
SELECT 'INSERT INTO ##NotesConsolidated (NOTEINDX, TableName) select NOTEINDX, ''[' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'' from [' + SCHEMA_NAME(schema_id) + '].[' + t.NAME + ']'
FROM sys.tables AS t
INNER JOIN sys.columns c ON t.OBJECT_ID = c.OBJECT_ID
WHERE c.NAME = 'NOTEINDX' AND NOT (t.name='SY03900' AND SCHEMA_NAME(schema_id)='dbo');
OPEN This_cursor

FETCH NEXT
FROM This_cursor
INTO @SqlStatement

WHILE (@@fetch_status <> - 1)
BEGIN
EXEC (@SqlStatement)

FETCH NEXT
FROM This_cursor
INTO @SqlStatement
END
DEALLOCATE This_cursor

SELECT * FROM SY03900
where NOTEINDX not in (
SELECT NOTEINDX FROM ##NotesConsolidated )

 

Results 

Examples of the four hundred and seven (yours will differ) tables containing references to notes in the notes table:

Solution to Acrobat reader slow to attach email, Get a link to your file, How do you want to share your file

Our Dynamics GP uses reporting services for generating sales documents, these documents often need to be emailed to customers and suppliers. It was recently brought to my attention that pressing the share icon on Acrobat DC to send a PDF by email launches a new share window. This window seems to often stall or take 30 seconds or longer to open sometimes with a spinning icon. I assume this is an attempt by Acrobat to draw people in the cloud document management solutions. For users transaction processing day in day out, this mounts up over a day and is annoying and inefficient.

Although I've not checked, I think this is because it will be contacting the Adobe servers in the background and our corporate firewall blocks a lot of traffic. Admin users don't seem to suffer this problem, which may be evidence of this being the case. 

Solution to slow to send email in acrobat:

To enable the envelope icon on the tools menu, to directly jump to attaching an email in outlook, avoiding this slow overlay share window,  then a registry change is required. The change is documented in the following acrobat resource, How to use the email icon to send a PDF directly as email attachment 

Basically the following registry change is required for DC users. Having applied this change outlook opens in sub-seconds  with the PDF attached. 

Windows Registry Editor Version 5.00

[HKEY_LOCAL_MACHINE\SOFTWARE\Policies\Adobe\Acrobat Reader\DC\FeatureLockDown]
"bSendMailShareRedirection"=dword:00000000