Using Dynamics GP VBA to Truncate Decimal Placement

Hi:

Below is a previously entered case.  I'm finding that I need to accomplish this "ROUND" statement for the SOP10200.UNITPRCE field using VBA.

I've used VBA with Dynamics GP.  But, not for this Dynamics GP issue.  If someone could provide me some guidance, that would be great.

I'm trying to drop the last four decimals in Sales Transaction Entry Unit Price and not allow rounding to the nearest second decimal place per the T-SQL scripting below.

Thanks!

TBSupport




Hello:

In any case, I revised my trigger and got better results--sort of.  When I tab past the line containing my item, nothing happens.  But, when I click back on that line, the "correct change" happens perfectly.  There is a problem with this, though.

I obviously don't like that I have to click back on my original line for this change to happen.

Anyway, below are my revised triggers--one for the insert and one for the update.  They're both working.  But, again, I don't like having to click back on the grid.

So, these only work if I in essence "refresh" the grid line (i.e. click "Print", hit "Save", or click back on the line item).

If you can let me know what I'm doing wrong, that would be awesome!

ALTER TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
AFTER INSERT
AS
UPDATE SOP10200 SET SOP10200.UNITPRCE =
ROUND(SOP10200.UNITPRCE, 2, 1)

ALTER TRIGGER dbo.[ROUNDUNITPRICE2]
ON dbo.[SOP10200]
FOR UPDATE
AS
IF UPDATE(UNITPRCE)
BEGIN
UPDATE SOP10200 SET SOP10200.UNITPRCE =
ROUND(SOP10200.UNITPRCE, 2, 1)
END

Thanks!

TBSupport
LVL 1
TBSupportAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZberteocCommented:
Try this:

CAST(SOP10200.UNITPRCE as decimal(12,2))
0
TBSupportAuthor Commented:
How do I put that in VBA?

TBSupport
0
ZberteocCommented:
That is SQL code, so you need to modify the triggers:

ALTER TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
AFTER INSERT
AS 
UPDATE SOP10200 SET SOP10200.UNITPRCE = 
CAST(SOP10200.UNITPRCE as decimal(12,2))

ALTER TRIGGER dbo.[ROUNDUNITPRICE2]
ON dbo.[SOP10200]
FOR UPDATE
AS 
IF UPDATE(UNITPRCE)
BEGIN
UPDATE SOP10200 SET SOP10200.UNITPRCE = 
CAST(SOP10200.UNITPRCE as decimal(12,2))
END

Open in new window

0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

TBSupportAuthor Commented:
Hi Zberteoc:

That didn't work.  In fact, the triggers that I used actually did conduct the proper price change.  It's just I had to click on the line item or print the document to see the change.  That's why I was asking about perhaps placing my triggers inside of Dynamics GP VBA, since GP grids can cause problems like this.

I'm afraid that, with your "CAST" suggestion, no changes were made at all.

Thanks, though!  :)

If someone knows of a way to do this with Visual Studio Tools, I'm open to that, as well.

TBSupport
0
ZberteocCommented:
I think I misunderstood the process you're using. Do you have a VB app that adds/updates prices? What line items and click are you refereeing to?

If you just want o force the update for all the prices in the table so they will get rid of decimals  then all you need to do is to execute a statement like:

UPDATE dbo.[SOP10200] SET UNITPRCE = ROUND(UNITPRCE, 2, 1)

This will update all your prices and following after the trigger will take care of it.
0
TBSupportAuthor Commented:
No, that's not it.  There's no VB app.  Microsoft Dynamics GP has a "built-in" means of accessing VBA.

It's not that I want to do a "mass update" of what's already in the table.  I want what I have in my original triggers to work for the Sales Transaction Entry window.

TBSupport
0
ZberteocCommented:
I just realized that the triggers are wrong. They will update the entire table every time a row is inserted or updated, you need a WHERE clause there to filter the rows. Also you don't need 2 triggers to do the same thing, you can have one trigger for both insert and delete but without the UPDATE(unitprice) condition:

ALTER TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
FOR INSERT, UPDATE
AS 
UPDATE SOP10200 SET SOP10200.UNITPRCE = 
ROUND(inserted.UNITPRCE, 2, 1) WHERE pk_col = inserted.pk_col

Open in new window

Where the pk_col has to be the primary key for the SOP10200 table, this will ensure that only the inserted/updated row will be affected by the trigger. The "inserted" is the background auxiliary table that sql uses in the case of insert and update actions as an intermediate storage. Remove the second trigger. Definetely you don't need any VBA code to deal with this.

The UPDATE(unitprice) condition from the second trigger prevented the action to take place unless the unitprice column was updated. Removing it will solve your problem.
0
ZberteocCommented:
To more exact what happened with your triggers was this:

1. On INSERT all table would be updated, that is why in that case you would see the effect.

2. On UPDATE nothing would happened unless the UNITPRICE column value changed, and that was because of the UPDATE(UNITPRICE) condition. If the UNITPRICE column value changed, again all the table would be affected by applying the rounding.

In any case you don't want and don't need to affect the entire table. Probably the table is not big otherwise your inserts and UNITPRICE value change would take a long time to complete.
0
TBSupportAuthor Commented:
Thanks, Zberteoc!

SOP10200 has four primary keys!  So, I'm unsure of the syntax.  Help.

TBSupport
0
ZberteocCommented:
Primary key can only be one per table, but it can be a composite key, which means it is formed by multiple columns. The role of the PK is to uniquely identify a row so is a unique combination of columns, if multiple columns are involved.

If you have a PK that is formed by, let's say:

col1, col2, col3, col4

In that case your tigger should be:
ALTER TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
FOR INSERT, UPDATE
AS 
UPDATE SOP10200 SET 
	SOP10200.UNITPRCE = ROUND(inserted.UNITPRCE, 2, 1) 
WHERE 
		col1 = inserted.col1
	AND col2 = inserted.col2
	AND col3 = inserted.col3
	AND col4 = inserted.col4

Open in new window

The order doesn't matter but it for convenience should be the same as in the actual PK definition.
0
TBSupportAuthor Commented:
Thanks!  I got an error saying that "the multi-part identifier inserted.... could not be bound".  Here is the syntax that I used:

CREATE TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
FOR INSERT, UPDATE
AS
UPDATE SOP10200 SET
      SOP10200.UNITPRCE = ROUND(inserted.UNITPRCE, 2, 1)
WHERE
            SOPTYPE = inserted.SOPTYPE
      AND SOPNUMBE = inserted.SOPNUMBE
      AND LNITMSEQ = inserted.LNITMSEQ
      AND CMPNTSEQ = inserted.CMPNTSEQ

TBSupport
0
TBSupportAuthor Commented:
Am I getting that error because the "inserted" table does not exist?  How do I create it?

TBSupport
0
ZberteocCommented:
Use this:

CREATE TRIGGER dbo.[ROUNDUNITPRICE]
ON dbo.[SOP10200]
FOR INSERT, UPDATE
AS 
UPDATE s SET 
	UNITPRCE = ROUND(i.UNITPRCE, 2, 1)
FROM  
	dbo.[SOP10200] s
	INNER JOIN inserted i 
		ON
			s.SOPTYPE  = i.SOPTYPE
		AND s.SOPNUMBE = i.SOPNUMBE
		AND s.LNITMSEQ = i.LNITMSEQ
		AND s.CMPNTSEQ = i.CMPNTSEQ 

Open in new window

0
ZberteocCommented:
What type is the UNITPRICE column? Is it money? If yes you could change it to decimal(12,2) and you don't need the trigger. However in that case you can't control the rounding.
0
TBSupportAuthor Commented:
Hi Zberteoc:

Thanks, but I'm afraid that that did not work either.  GP gave me an ugly error message, so I dropped the trigger.

I don't want to change the decimal type of the field in the table directly, as that can wreak havoc with things like upgrades and system maintenance.

Any way to do this in VBA?

TB Support
0
ZberteocCommented:
You can't do it in VBA. How will you call VBA from your application?

Just post the error you said it was caused by the trigger. This can be done at the trigger level. Maybe you didn't give the right PK information. That trigger s a basic thing so we should be able to fix it.

So I need to see the PK columns for the table and the error from trigger.
0
TBSupportAuthor Commented:
The error was the GP error, "A save operation on table SOP_LINE_WORK (45)".

The PK columns are the four that I mentioned before and that were in your revised trigger.

Thanks!

TBSupport
0
ZberteocCommented:
That error refers to a different table.

You should check if the UNITPRICE values that you inserted/updated with the newe trigger are correct. Also make sure you removed the dbo.[ROUNDUNITPRICE2] trigger or at least disabled it.
0
TBSupportAuthor Commented:
No, that error message refers indeed to the SOP10200 table.
0
ZberteocCommented:
Sorry to say but it doesn't add up.

Your 2 triggers didn't create any problems but one trigger does. The changed trigger only merged the 2 before and does the same thing, rounds the UNITPRICE but only on the updated row(s) and not the entire table. If there was an error caused by that trigger and the SOP10200 table the message should say so.

Sorry but it doesn't make sense. It seems like you don't want the help.

You never answered if you removed the [ROUNDUNITPRICE2], trigger. If it still there it could interfere with the new one by locking the table. You should have only one trigger on SOP10200 table that deals with the UNITPRICE rounding and that should be the one I posted last.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.