More on OLE attachments and record notes

Microsoft’s David Musgrave takes on OLE attachments and record notes based on a support case he tackled recently. David reviews the process use by Dynamics GP to retrieve the next note index value and how the system associates the note to a record. He explores the OLE container and how the file names are encoded along with the path.

Furthermore, he provides some Dexterity and SQL code to demonstrate how you would retrieve the hexadecimal value used to compose the notes file name. Be sure to read David’s article to get the complete scoop.

Related articles

All About the Dexterity OLE Container – click here
Understanding Notes and the Note Index Field @ Developing for Dynamics GP, click here

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

Fixing Microsoft Dynamics GP Toolbar erratic behavior

A few months aback, I blogged about a case where the Microsoft Dynamics GP toolbar menu would reorganized itself erratically even with the Lock Toolbar option activated. Back then, I alluded to the Script Debugging Tool being activated via the DEX.INI. Since then, I have also found that this may (or may not) be the case when returning from Modifier or Report Writer.

If you do happen to experience this issue and do not use the Script Debugging Tool, you can run the following T-SQL script in SQL Server Management Studio to correct the problem:

USE DYNAMICS;GO

UPDATE SY07121 SET Visible = 0, ROWNMBR = 1, RowSequence = 1 WHERE CmdBarDictID = 3830   AND CmdBarFormID = 22006   AND CmdBarWindowID = 22003   AND USERID  ''

UPDATE SY07121  SET Visible = 1, ROWNMBR = 1, RowSequence = 4  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 294    AND USERID  ''

UPDATE SY07121  SET Visible = 1, ROWNMBR = 1, RowSequence = 3  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 295    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 231    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 223    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 230    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 228    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 225    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 224    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 226    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 227    AND USERID  ''

UPDATE SY07121  SET Visible = 0, ROWNMBR = 99, RowSequence = 0  WHERE CmdBarDictID = 0    AND CmdBarFormID = 1568    AND CmdBarWindowID = 192    AND USERID  ''GO

The above script is applicable ONLY to Microsoft Dynamics GP v10. Make a backup of your DYNAMICS database before executing this script.

If the above fails to correct the issue, then you will need to remove the records for the user in question:

USE DYNAMICS;GODELETE FROM SY07121 WHERE USERID = 'theUserID';

The scripts were originally provided by Sarah Purdy on the Dynamics GP Partner Technical Community and must be executed without any users in the system.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. “End of Month + Net Days” (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 — or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM PM20000 A    INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM RM20101 A    INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)      AND (A.RMDTYPAL = I.RMDTYPAL)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:

NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

"End of Month + Net Days" payment terms due date calculation

Folks across the pond use payment terms and due dates that are not traditionally close the ones we are familiar with on this side of the hemisphere. “End of Month + Net Days” (EOM+ND) is a typical case.

In EOM+ND payment terms, an invoice becomes due a number of net days after the last day of the month for the invoice date. For example, if an invoice date is July 16 and we are on a payment term of EOM plus 45 net days, the invoice will not be due until September 14 — or 45 days from July 31.

Of course, you cannot manage this type of payment term request in GP, not out-of-the-box anyways, which most of the time will require a customization to deal with the issue.

In this article, I will examine setting up two SQL Server triggers: one on the PM Transaction Open File (dbo.PM20000) and another on the RM Open File (dbo.RM20101) tables. The triggers will use the Net Days field in the Payment Terms Master (dbo.SY03300) table to calculate the net days after the end of month to assign the invoice due date.

So here are the triggers:

trigger pmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER pmEOMPlusNet ON dbo.PM20000 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM PM20000 A    INNER JOIN INSERTED I ON (A.VCHRNMBR = I.VCHRNMBR) AND (A.DOCTYPE = I.DOCTYPE)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.DOCTYPE = 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

trigger rmEOMPlusNet

-- Created by Mariano Gomez, MVP-- No warranties expressed or impliedCREATE TRIGGER rmEOMPlusNet ON dbo.RM20101 AFTER INSERTASBEGIN TRANSACTION;

BEGIN TRY  UPDATE A SET A.DUEDATE =    DATEADD(dd, B.DUEDTDS, DATEADD(dd, -1, DATEADD(mm, DATEDIFF(mm, 0, I.DOCDATE) + 1, 0)))  FROM RM20101 A    INNER JOIN INSERTED I ON (A.CUSTNMBR = I.CUSTNMBR) AND (A.DOCNUMBR = I.DOCNUMBR)      AND (A.RMDTYPAL = I.RMDTYPAL)    LEFT OUTER JOIN SY03300 B ON (I.PYMTRMID = B.PYMTRMID)  WHERE (I.RMDTYPAL= 1) AND (I.PYMTRMID LIKE 'EOMPLUSND%')END TRYBEGIN CATCH  SELECT ERROR_NUMBER() AS ErrorNumber    , ERROR_SEVERITY() AS ErrorSeverity    , ERROR_STATE() AS ErrorState    , ERROR_PROCEDURE() AS ErrorProcedure    , ERROR_LINE() AS ErrorLine    , ERROR_MESSAGE() AS ErrorMessage;

  IF @@TRANCOUNT > 0    ROLLBACK TRANSACTION;END CATCH;

IF @@TRANCOUNT > 0  COMMIT TRANSACTION;GO

Setting up the payment term in Dynamics GP

Open the Payment Terms Setup window (MSDGP > Tools > Setup > Company > Payment Terms) and configure the payment term as shown below:

NOTE: You can still setup discount and discount types for the payment term, but these will be calculated based on the document date. If you need these to apply based on EOM as well, you will need to change the above triggers to reflect the discount calculation based on EOM as well.

Finally, once setup, these payment terms can be used from SOP and POP. Just keep in mind that the due dates will not be calculated while the transactions are stored in a batch, but rather when posted.

Until next post!

MG.-
Mariano Gomez, MVP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to calculate the number of days a vendor check takes to clear?

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.

According to the help file, the field is defined as “View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.“, in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:

-- Created by Mariano Gomez, MVPWITH AVGCTE(VENDORID, AVGDAYS) AS (  SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS  FROM CM20200 A  GROUP BY A.CMLinkID)UPDATE PM00201 SET DYCHTCLR = AVGDAYSFROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

How to calculate the number of days a vendor check takes to clear?

I am always amazed at the things you can do with Microsoft Dynamics GP. Just recently, a user pointed out that the Days for Checks to Clear field on the Vendor Credit Summary window was always blank and that they needed this field to update automatically.

According to the help file, the field is defined as “View or change the average number of days that a check you issue to the vendor usually takes to clear your bank.“, in other words, you can manually update it if you need to track this data.

The user also pointed out that they performed daily bank reconciles and that the Cleared Date column in the CM Transaction (dbo.CM20200) table was reflective of the actual check cleared date in the bank and that they would like to use this field to update the Days for Checks to Clear field in the Vendor Credit Summary window. After all, the date value of the Cleared Date column only gets updated when the check is marked as cleared during a checkbook reconciliation process and does not necessarily reflect the actual date the check cleared the bank, but with daily bank reconciles this should not be an issue, because the would be one of the same.

So, if you perform daily bank reconciliations and would like to know the average days a vendor check takes to clear your bank, you could use the following T-SQL query to update this column value:

-- Created by Mariano Gomez, MVPWITH AVGCTE(VENDORID, AVGDAYS) AS (  SELECT A.CMLinkID, AVG(DATEDIFF(DD, A.TRXDATE, A.clearedate)) AS AVGDAYS  FROM CM20200 A  GROUP BY A.CMLinkID)UPDATE PM00201 SET DYCHTCLR = AVGDAYSFROM PM00201 INNER JOIN AVGCTE ON (PM00201.VENDORID = AVGCTE.VENDORID)

Now what to do with the script? You can schedule it as part of a SQL Server job that will run daily to update this value for you! This will allow you to have accurate average days for each vendor and in turn will allow you to make better purchasing decisions to preserve your hard earned cash.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

You receive error "Someone is clearing company files and you cannot get into this company" when logging into a company – The Clear Data process

This one was actually a very interesting case reported on the Dynamics GP newsgroup, so I figured I needed to get to the bottom of it. The very frustrated user reported seing the following error trying to get into a company in Dynamics GP.

In an attempt to replicate this issue, the most logical place to start was to login into Dynamics GP as my ’sa’ user and open the Clear Data window and try to do something there.

Since Clear Data is a distructive process, the Dynamics GP Development team figured they had to find a way to prevent users from accessing the system while this process was being executed by, perhaps, the system administrator. This is how the Clear Data Comp reserved user ID was born. When the window is first accessed by the system administrator, Dynamics GP checks for any users currently available in the system. If there are no users, it will display the Clear Data window and create a record for the Clear Data Comp user ID in the system databases User Activity table (DYNAMICS.dbo.ACTIVITY). The following query shows the content of the ACTIVITY table.

If a second user attempts to access the same company, the code will check for the Clear Data Comp user activity record before allowing the user to access the company. If the record is found, the system will issue the error message originally described.

Since this user ID is internal to the application, there are no locks placed or sessions logged in the tempdb..DEX_LOCK or tempdb..DEX_SESSION tables respectively. If you noted the query results, the Clear Data Comp user ID’s SQL session ID is 0.

What happens when Dynamics GP crashes in middle of running Clear Data?

If the system crashes in middle of the Clear Data process or SQL Server becomes unavailable, the ACTIVITY record for the Clear Data Comp user ID will not be properly released. Hence, when users attempt to log back into the company they will receive the same error message.

To correct this problem, the system administrator must log into SQL Server Management Studio and remove the record by running the following query:

-- created by Mariano Gomez, MVPDELETE FROM ACTIVITY WHERE USERID = 'Clear Data Comp'

Hope this helps in your troubleshooting efforts and to understand another one of those ‘old’ Dynamics maintenance utilities.

Until next post!

MG.-
Mariano Gomez, MIS, MCP, MVP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com

Fixing Microsoft Dynamics GP and Illegal Characters error messages

By now you probably read David Musgrave’s series on Microsoft Dynamics GP and Illegal Characters and have come to realize that the single quote character is perhaps one of the biggest culprits in the errors you are receiving when running reports such as your age trial balances in both receivables and payables. It’s enough for the single quote character to be in your customer ID, vendor ID, or even document numbers to cause some error while running a report or inquiring a transaction.

The following is an actual case reported by a user on the Dynamics GP newsgroup when running the Payables Historical Aged Trial Balance report:

The stored procedure pmHistoricalAgedTrialBalance returned the following
result: DMBS: -127, Microsoft Dynamics GP: 0.

After some research the user realized they had a few document numbers that contained the illegal single quote character, for example INV’23002. Note that this case is specific to the document number.

So, what to do if you are a victim of the single quotes?

1) You will need to first identify the tables possibly affected. In particular, you will need to search all tables in the database that may contain the column storing the data — in the case of the example, DOCNUMBR. For this you can use a script I published before here. You may also use the script provided by David Musgrave on Part 2 of his series, which would allow you to search through all your company databases if experiencing the issue in more than one company.

2) You will then need to identify the specific records affected by a single quote. Once the tables have been identified, you can do the following:

SELECT * FROM PM00400 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10201 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10300 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM10400 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM20000 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM30200 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM50100 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM PM80200 WHERE DOCNUMBR LIKE '%''%'SELECT * FROM GL20000 WHERE ORDOCNUM LIKE '%''%'

NOTE: The GL20000 and GL30000 tables will store this information in the ORDOCNUM column.

3) Once you identify the document numbers you will need to have a plan to correct them. Your plan should include making sure the new resulting document number is not going to create a duplicate situation with an already existing record.

4) Update the affected records.

UPDATE PM00400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10201 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10300 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM10400 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM20000 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM30200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM50100 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE PM80200 SET DOCNUMBR = 'NEW_DOC_NUM' WHERE DOCNUMBR = 'XY''Z'UPDATE GL20000 SET ORDOCNUM = 'NEW_DOC_NUM' WHERE ORDOCNUM = 'XY''Z'

Note the use of a double single quote to format the document number string properly and avoid an early string termination.

Since this situation can present itself in other modules, be sure to identify the specific module affected and the tables that may be impacted based on the data column storing the informatin. Hope this helps and complements the articles and information on Developing for Dynamics GP.

Until next post!

MG.-
Mariano Gomez, MIS
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com/