Trigger on IVR10015

I'm trying to find a way to capture the actual created date on new items when 
the item copy function is used (when you use the copy function it stores the 
created date of the source item as the created date of the new item).  We 
have manufacturing but we don't have the QA module, so I was trying to add a 
trigger to IVR10015 to put the current date in the QA Start Date field and or 
the QA End Date field upon insert to IVR10015.

When I do that and add a new item I get the message below.  How can I tell 
what the constraint is that is being violated?  Any other ideas on how to 
capture the actual created date?  Thanks.

[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted 
with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The conflict 
occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.

-- 
Jim@TurboChef
0
jim.harris (398)
10/17/2006 12:19:02 PM
greatplains 29623 articles. 4 followers. Follow

18 Replies
1587 Views

Similar Articles

[PageSpeed] 10

------=_NextPart_0001_21B87889
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hi Jim,

Thank you for posting tot he Microsoft Great Plains Newsgroup.

I understand you created a trigger to populate the QASTDATE start field in 
the IVR10015 table.  This trigger was created to update that table to show 
you when an Item Number was created.  If you use the copy feature for 
copying item numbers, the Created Date in the IV00101 table shows the date 
from the original item number.  This is not what you need.

I am not sure what you have setup for your trigger and but it probably does 
not matter as this field may not be able to be populated.  What you can 
look at is first updating the QAQCNEEDED_I field first.  When the 
QAQCNEEDED_I field has a value of 1, the date field is available.  If it is 
set to 0, the date field is not available.  This may be causing the error 
message you are receiving.

I looked further into the IV00101 table and I was wondering if you would be 
able to use the MODIFDT (Modified Date) for you issue.  This field is 
updated right away with the user date when the copy feature is used.  The 
draw back of this field is that it is updated if you change anything on the 
Item Number.

Have a great day!

Best Regards, 

David Graening
Microsoft Online Partner Support 


--------------------
Thread-Topic: Trigger on IVR10015
thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
X-WBNR-Posting-Host: 207.59.211.138
From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
Subject: Trigger on IVR10015
Date: Tue, 17 Oct 2006 05:19:02 -0700
Lines: 17
Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.greatplains
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.greatplains

I'm trying to find a way to capture the actual created date on new items 
when 
the item copy function is used (when you use the copy function it stores 
the 
created date of the source item as the created date of the new item).  We 
have manufacturing but we don't have the QA module, so I was trying to add 
a 
trigger to IVR10015 to put the current date in the QA Start Date field and 
or 
the QA End Date field upon insert to IVR10015.

When I do that and add a new item I get the message below.  How can I tell 
what the constraint is that is being violated?  Any other ideas on how to 
capture the actual created date?  Thanks.

[Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted 
with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The conflict 
occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.

-- 
Jim@TurboChef

------=_NextPart_0001_21B87889
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hi Jim,
\par 
\par Thank you for posting tot he Microsoft Great Plains Newsgroup.
\par 
\par I understand you created a trigger to populate the QASTDATE start field in the IVR10015 table.  This trigger was created to update that table to show you when an Item Number was created.  If you use the copy feature for copying item numbers, the Created Date in the IV00101 table shows the date from the original item number.  This is not what you need.
\par 
\par I am not sure what you have setup for your trigger and but it probably does not matter as this field may not be able to be populated.  What you can look at is first updating the QAQCNEEDED_I field first.  When the QAQCNEEDED_I field has a value of 1, the date field is available.  If it is set to 0, the date field is not available.  This may be causing the error message you are receiving.
\par 
\par I looked further into the IV00101 table and I was wondering if you would be able to use the MODIFDT (Modified Date) for you issue.  This field is updated right away with the user date when the copy feature is used.  The draw back of this field is that it is updated if you change anything on the Item Number.
\par 
\par Have a great day!
\par 
\par Best Regards, 
\par 
\par David Graening
\par Microsoft Online Partner Support 
\par 
\par 
\par \pard\li720 --------------------
\par Thread-Topic: Trigger on IVR10015
\par thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
\par X-WBNR-Posting-Host: 207.59.211.138
\par From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
\par Subject: Trigger on IVR10015
\par Date: Tue, 17 Oct 2006 05:19:02 -0700
\par Lines: 17
\par Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
\par MIME-Version: 1.0
\par Content-Type: text/plain;
\par \tab charset="Utf-8"
\par Content-Transfer-Encoding: 7bit
\par X-Newsreader: Microsoft CDO for Windows 2000
\par Content-Class: urn:content-classes:message
\par Importance: normal
\par Priority: normal
\par X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
\par Newsgroups: microsoft.public.greatplains
\par Path: TK2MSFTNGXA01.phx.gbl
\par Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
\par NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
\par X-Tomcat-NG: microsoft.public.greatplains
\par 
\par I'm trying to find a way to capture the actual created date on new items when 
\par the item copy function is used (when you use the copy function it stores the 
\par created date of the source item as the created date of the new item).  We 
\par have manufacturing but we don't have the QA module, so I was trying to add a 
\par trigger to IVR10015 to put the current date in the QA Start Date field and or 
\par the QA End Date field upon insert to IVR10015.
\par 
\par When I do that and add a new item I get the message below.  How can I tell 
\par what the constraint is that is being violated?  Any other ideas on how to 
\par capture the actual created date?  Thanks.
\par 
\par [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted 
\par with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The conflict 
\par occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
\par 
\par -- 
\par Jim@TurboChef
\par \pard 
\par 
\par }
------=_NextPart_0001_21B87889--

0
dgraenin (57)
10/18/2006 6:19:10 PM
Thanks - we're using the modified date now, but as you noted that picks up 
all changes so there is no consistent way to know which items are new on any 
given day.  I'll try setting the QAQCNEEDED_I field as part of the trigger.
-- 
Jim@TurboChef


"David Graening {MSFT}" wrote:

> Hi Jim,
> 
> Thank you for posting tot he Microsoft Great Plains Newsgroup.
> 
> I understand you created a trigger to populate the QASTDATE start field in 
> the IVR10015 table.  This trigger was created to update that table to show 
> you when an Item Number was created.  If you use the copy feature for 
> copying item numbers, the Created Date in the IV00101 table shows the date 
> from the original item number.  This is not what you need.
> 
> I am not sure what you have setup for your trigger and but it probably does 
> not matter as this field may not be able to be populated.  What you can 
> look at is first updating the QAQCNEEDED_I field first.  When the 
> QAQCNEEDED_I field has a value of 1, the date field is available.  If it is 
> set to 0, the date field is not available.  This may be causing the error 
> message you are receiving.
> 
> I looked further into the IV00101 table and I was wondering if you would be 
> able to use the MODIFDT (Modified Date) for you issue.  This field is 
> updated right away with the user date when the copy feature is used.  The 
> draw back of this field is that it is updated if you change anything on the 
> Item Number.
> 
> Have a great day!
> 
> Best Regards, 
> 
> David Graening
> Microsoft Online Partner Support 
> 
> 
> --------------------
> Thread-Topic: Trigger on IVR10015
> thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> X-WBNR-Posting-Host: 207.59.211.138
> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> Subject: Trigger on IVR10015
> Date: Tue, 17 Oct 2006 05:19:02 -0700
> Lines: 17
> Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> 	charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> Newsgroups: microsoft.public.greatplains
> Path: TK2MSFTNGXA01.phx.gbl
> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> X-Tomcat-NG: microsoft.public.greatplains
> 
> I'm trying to find a way to capture the actual created date on new items 
> when 
> the item copy function is used (when you use the copy function it stores 
> the 
> created date of the source item as the created date of the new item).  We 
> have manufacturing but we don't have the QA module, so I was trying to add 
> a 
> trigger to IVR10015 to put the current date in the QA Start Date field and 
> or 
> the QA End Date field upon insert to IVR10015.
> 
> When I do that and add a new item I get the message below.  How can I tell 
> what the constraint is that is being violated?  Any other ideas on how to 
> capture the actual created date?  Thanks.
> 
> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted 
> with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The conflict 
> occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> 
> -- 
> Jim@TurboChef
> 
0
jim.harris (398)
10/18/2006 7:36:02 PM
------=_NextPart_0001_2234C585
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Jim,

Besides using the IVR10015 table, have you looked to see if you can 
populate another field in the IV00101 table?  I am not sure if this would 
be easier to do this, but it is just another thought.

Best Regards, 

David Graening
Microsoft Online Partner Support 


--------------------
Thread-Topic: Trigger on IVR10015
thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
X-WBNR-Posting-Host: 207.59.211.138
From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com> 
<BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
Subject: RE: Trigger on IVR10015
Date: Wed, 18 Oct 2006 12:36:02 -0700
Lines: 86
Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
MIME-Version: 1.0
Content-Type: text/plain;
	charset="Utf-8"
Content-Transfer-Encoding: 7bit
X-Newsreader: Microsoft CDO for Windows 2000
Content-Class: urn:content-classes:message
Importance: normal
Priority: normal
X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
Newsgroups: microsoft.public.greatplains
Path: TK2MSFTNGXA01.phx.gbl
Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
X-Tomcat-NG: microsoft.public.greatplains

Thanks - we're using the modified date now, but as you noted that picks up 
all changes so there is no consistent way to know which items are new on 
any 
given day.  I'll try setting the QAQCNEEDED_I field as part of the trigger.
-- 
Jim@TurboChef


"David Graening {MSFT}" wrote:

> Hi Jim,
> 
> Thank you for posting tot he Microsoft Great Plains Newsgroup.
> 
> I understand you created a trigger to populate the QASTDATE start field 
in 
> the IVR10015 table.  This trigger was created to update that table to 
show 
> you when an Item Number was created.  If you use the copy feature for 
> copying item numbers, the Created Date in the IV00101 table shows the 
date 
> from the original item number.  This is not what you need.
> 
> I am not sure what you have setup for your trigger and but it probably 
does 
> not matter as this field may not be able to be populated.  What you can 
> look at is first updating the QAQCNEEDED_I field first.  When the 
> QAQCNEEDED_I field has a value of 1, the date field is available.  If it 
is 
> set to 0, the date field is not available.  This may be causing the error 
> message you are receiving.
> 
> I looked further into the IV00101 table and I was wondering if you would 
be 
> able to use the MODIFDT (Modified Date) for you issue.  This field is 
> updated right away with the user date when the copy feature is used.  The 
> draw back of this field is that it is updated if you change anything on 
the 
> Item Number.
> 
> Have a great day!
> 
> Best Regards, 
> 
> David Graening
> Microsoft Online Partner Support 
> 
> 
> --------------------
> Thread-Topic: Trigger on IVR10015
> thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> X-WBNR-Posting-Host: 207.59.211.138
> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> Subject: Trigger on IVR10015
> Date: Tue, 17 Oct 2006 05:19:02 -0700
> Lines: 17
> Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> 	charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> Newsgroups: microsoft.public.greatplains
> Path: TK2MSFTNGXA01.phx.gbl
> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> X-Tomcat-NG: microsoft.public.greatplains
> 
> I'm trying to find a way to capture the actual created date on new items 
> when 
> the item copy function is used (when you use the copy function it stores 
> the 
> created date of the source item as the created date of the new item).  We 
> have manufacturing but we don't have the QA module, so I was trying to 
add 
> a 
> trigger to IVR10015 to put the current date in the QA Start Date field 
and 
> or 
> the QA End Date field upon insert to IVR10015.
> 
> When I do that and add a new item I get the message below.  How can I 
tell 
> what the constraint is that is being violated?  Any other ideas on how to 
> capture the actual created date?  Thanks.
> 
> [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement 
conflicted 
> with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The 
conflict 
> occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> 
> -- 
> Jim@TurboChef
> 

------=_NextPart_0001_2234C585
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Jim,
\par 
\par Besides using the IVR10015 table, have you looked to see if you can populate another field in the IV00101 table?  I am not sure if this would be easier to do this, but it is just another thought.
\par 
\par Best Regards, 
\par 
\par David Graening
\par Microsoft Online Partner Support 
\par 
\par 
\par \pard\li720 --------------------
\par Thread-Topic: Trigger on IVR10015
\par thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
\par X-WBNR-Posting-Host: 207.59.211.138
\par From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
\par References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
\par Subject: RE: Trigger on IVR10015
\par Date: Wed, 18 Oct 2006 12:36:02 -0700
\par Lines: 86
\par Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
\par MIME-Version: 1.0
\par Content-Type: text/plain;
\par \tab charset="Utf-8"
\par Content-Transfer-Encoding: 7bit
\par X-Newsreader: Microsoft CDO for Windows 2000
\par Content-Class: urn:content-classes:message
\par Importance: normal
\par Priority: normal
\par X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
\par Newsgroups: microsoft.public.greatplains
\par Path: TK2MSFTNGXA01.phx.gbl
\par Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
\par NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
\par X-Tomcat-NG: microsoft.public.greatplains
\par 
\par Thanks - we're using the modified date now, but as you noted that picks up 
\par all changes so there is no consistent way to know which items are new on any 
\par given day.  I'll try setting the QAQCNEEDED_I field as part of the trigger.
\par -- 
\par Jim@TurboChef
\par 
\par 
\par "David Graening \{MSFT\}" wrote:
\par 
\par > Hi Jim,
\par > 
\par > Thank you for posting tot he Microsoft Great Plains Newsgroup.
\par > 
\par > I understand you created a trigger to populate the QASTDATE start field in 
\par > the IVR10015 table.  This trigger was created to update that table to show 
\par > you when an Item Number was created.  If you use the copy feature for 
\par > copying item numbers, the Created Date in the IV00101 table shows the date 
\par > from the original item number.  This is not what you need.
\par > 
\par > I am not sure what you have setup for your trigger and but it probably does 
\par > not matter as this field may not be able to be populated.  What you can 
\par > look at is first updating the QAQCNEEDED_I field first.  When the 
\par > QAQCNEEDED_I field has a value of 1, the date field is available.  If it is 
\par > set to 0, the date field is not available.  This may be causing the error 
\par > message you are receiving.
\par > 
\par > I looked further into the IV00101 table and I was wondering if you would be 
\par > able to use the MODIFDT (Modified Date) for you issue.  This field is 
\par > updated right away with the user date when the copy feature is used.  The 
\par > draw back of this field is that it is updated if you change anything on the 
\par > Item Number.
\par > 
\par > Have a great day!
\par > 
\par > Best Regards, 
\par > 
\par > David Graening
\par > Microsoft Online Partner Support 
\par > 
\par > 
\par > --------------------
\par > Thread-Topic: Trigger on IVR10015
\par > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
\par > X-WBNR-Posting-Host: 207.59.211.138
\par > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
\par > Subject: Trigger on IVR10015
\par > Date: Tue, 17 Oct 2006 05:19:02 -0700
\par > Lines: 17
\par > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
\par > MIME-Version: 1.0
\par > Content-Type: text/plain;
\par > \tab charset="Utf-8"
\par > Content-Transfer-Encoding: 7bit
\par > X-Newsreader: Microsoft CDO for Windows 2000
\par > Content-Class: urn:content-classes:message
\par > Importance: normal
\par > Priority: normal
\par > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
\par > Newsgroups: microsoft.public.greatplains
\par > Path: TK2MSFTNGXA01.phx.gbl
\par > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
\par > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
\par > X-Tomcat-NG: microsoft.public.greatplains
\par > 
\par > I'm trying to find a way to capture the actual created date on new items 
\par > when 
\par > the item copy function is used (when you use the copy function it stores 
\par > the 
\par > created date of the source item as the created date of the new item).  We 
\par > have manufacturing but we don't have the QA module, so I was trying to add 
\par > a 
\par > trigger to IVR10015 to put the current date in the QA Start Date field and 
\par > or 
\par > the QA End Date field upon insert to IVR10015.
\par > 
\par > When I do that and add a new item I get the message below.  How can I tell 
\par > what the constraint is that is being violated?  Any other ideas on how to 
\par > capture the actual created date?  Thanks.
\par > 
\par > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement conflicted 
\par > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The conflict 
\par > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
\par > 
\par > -- 
\par > Jim@TurboChef
\par > 
\par \pard 
\par 
\par }
------=_NextPart_0001_2234C585--

0
dgraenin (57)
10/18/2006 7:54:16 PM
Yes, but I didn't see any other date fields there except for created date and 
modified date.  There are several fields we never need, but I'm not sure how 
to store a date in them.  For example minshelflfe1 and minshelflife2 are 
never relevant for our products, but those are integer fields.
-- 
Jim@TurboChef


"David Graening {MSFT}" wrote:

> Jim,
> 
> Besides using the IVR10015 table, have you looked to see if you can 
> populate another field in the IV00101 table?  I am not sure if this would 
> be easier to do this, but it is just another thought.
> 
> Best Regards, 
> 
> David Graening
> Microsoft Online Partner Support 
> 
> 
> --------------------
> Thread-Topic: Trigger on IVR10015
> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
> X-WBNR-Posting-Host: 207.59.211.138
> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com> 
> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
> Subject: RE: Trigger on IVR10015
> Date: Wed, 18 Oct 2006 12:36:02 -0700
> Lines: 86
> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
> MIME-Version: 1.0
> Content-Type: text/plain;
> 	charset="Utf-8"
> Content-Transfer-Encoding: 7bit
> X-Newsreader: Microsoft CDO for Windows 2000
> Content-Class: urn:content-classes:message
> Importance: normal
> Priority: normal
> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> Newsgroups: microsoft.public.greatplains
> Path: TK2MSFTNGXA01.phx.gbl
> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> X-Tomcat-NG: microsoft.public.greatplains
> 
> Thanks - we're using the modified date now, but as you noted that picks up 
> all changes so there is no consistent way to know which items are new on 
> any 
> given day.  I'll try setting the QAQCNEEDED_I field as part of the trigger.
> -- 
> Jim@TurboChef
> 
> 
> "David Graening {MSFT}" wrote:
> 
> > Hi Jim,
> > 
> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
> > 
> > I understand you created a trigger to populate the QASTDATE start field 
> in 
> > the IVR10015 table.  This trigger was created to update that table to 
> show 
> > you when an Item Number was created.  If you use the copy feature for 
> > copying item numbers, the Created Date in the IV00101 table shows the 
> date 
> > from the original item number.  This is not what you need.
> > 
> > I am not sure what you have setup for your trigger and but it probably 
> does 
> > not matter as this field may not be able to be populated.  What you can 
> > look at is first updating the QAQCNEEDED_I field first.  When the 
> > QAQCNEEDED_I field has a value of 1, the date field is available.  If it 
> is 
> > set to 0, the date field is not available.  This may be causing the error 
> > message you are receiving.
> > 
> > I looked further into the IV00101 table and I was wondering if you would 
> be 
> > able to use the MODIFDT (Modified Date) for you issue.  This field is 
> > updated right away with the user date when the copy feature is used.  The 
> > draw back of this field is that it is updated if you change anything on 
> the 
> > Item Number.
> > 
> > Have a great day!
> > 
> > Best Regards, 
> > 
> > David Graening
> > Microsoft Online Partner Support 
> > 
> > 
> > --------------------
> > Thread-Topic: Trigger on IVR10015
> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> > X-WBNR-Posting-Host: 207.59.211.138
> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> > Subject: Trigger on IVR10015
> > Date: Tue, 17 Oct 2006 05:19:02 -0700
> > Lines: 17
> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> > MIME-Version: 1.0
> > Content-Type: text/plain;
> > 	charset="Utf-8"
> > Content-Transfer-Encoding: 7bit
> > X-Newsreader: Microsoft CDO for Windows 2000
> > Content-Class: urn:content-classes:message
> > Importance: normal
> > Priority: normal
> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> > Newsgroups: microsoft.public.greatplains
> > Path: TK2MSFTNGXA01.phx.gbl
> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> > X-Tomcat-NG: microsoft.public.greatplains
> > 
> > I'm trying to find a way to capture the actual created date on new items 
> > when 
> > the item copy function is used (when you use the copy function it stores 
> > the 
> > created date of the source item as the created date of the new item).  We 
> > have manufacturing but we don't have the QA module, so I was trying to 
> add 
> > a 
> > trigger to IVR10015 to put the current date in the QA Start Date field 
> and 
> > or 
> > the QA End Date field upon insert to IVR10015.
> > 
> > When I do that and add a new item I get the message below.  How can I 
> tell 
> > what the constraint is that is being violated?  Any other ideas on how to 
> > capture the actual created date?  Thanks.
> > 
> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement 
> conflicted 
> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The 
> conflict 
> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> > 
> > -- 
> > Jim@TurboChef
> > 
> 
0
jim.harris (398)
10/18/2006 8:49:01 PM
Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We 
don't use manufacturing so I assume that copying an existing item to a new 
item will still create a record in IV00101.)

Ta,
M.

"Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
> Yes, but I didn't see any other date fields there except for created date 
> and
> modified date.  There are several fields we never need, but I'm not sure 
> how
> to store a date in them.  For example minshelflfe1 and minshelflife2 are
> never relevant for our products, but those are integer fields.
> -- 
> Jim@TurboChef
>
>
> "David Graening {MSFT}" wrote:
>
>> Jim,
>>
>> Besides using the IVR10015 table, have you looked to see if you can
>> populate another field in the IV00101 table?  I am not sure if this would
>> be easier to do this, but it is just another thought.
>>
>> Best Regards,
>>
>> David Graening
>> Microsoft Online Partner Support
>>
>>
>> --------------------
>> Thread-Topic: Trigger on IVR10015
>> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
>> X-WBNR-Posting-Host: 207.59.211.138
>> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
>> Subject: RE: Trigger on IVR10015
>> Date: Wed, 18 Oct 2006 12:36:02 -0700
>> Lines: 86
>> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
>> MIME-Version: 1.0
>> Content-Type: text/plain;
>> charset="Utf-8"
>> Content-Transfer-Encoding: 7bit
>> X-Newsreader: Microsoft CDO for Windows 2000
>> Content-Class: urn:content-classes:message
>> Importance: normal
>> Priority: normal
>> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> Newsgroups: microsoft.public.greatplains
>> Path: TK2MSFTNGXA01.phx.gbl
>> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
>> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> X-Tomcat-NG: microsoft.public.greatplains
>>
>> Thanks - we're using the modified date now, but as you noted that picks 
>> up
>> all changes so there is no consistent way to know which items are new on
>> any
>> given day.  I'll try setting the QAQCNEEDED_I field as part of the 
>> trigger.
>> -- 
>> Jim@TurboChef
>>
>>
>> "David Graening {MSFT}" wrote:
>>
>> > Hi Jim,
>> >
>> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
>> >
>> > I understand you created a trigger to populate the QASTDATE start field
>> in
>> > the IVR10015 table.  This trigger was created to update that table to
>> show
>> > you when an Item Number was created.  If you use the copy feature for
>> > copying item numbers, the Created Date in the IV00101 table shows the
>> date
>> > from the original item number.  This is not what you need.
>> >
>> > I am not sure what you have setup for your trigger and but it probably
>> does
>> > not matter as this field may not be able to be populated.  What you can
>> > look at is first updating the QAQCNEEDED_I field first.  When the
>> > QAQCNEEDED_I field has a value of 1, the date field is available.  If 
>> > it
>> is
>> > set to 0, the date field is not available.  This may be causing the 
>> > error
>> > message you are receiving.
>> >
>> > I looked further into the IV00101 table and I was wondering if you 
>> > would
>> be
>> > able to use the MODIFDT (Modified Date) for you issue.  This field is
>> > updated right away with the user date when the copy feature is used. 
>> > The
>> > draw back of this field is that it is updated if you change anything on
>> the
>> > Item Number.
>> >
>> > Have a great day!
>> >
>> > Best Regards,
>> >
>> > David Graening
>> > Microsoft Online Partner Support
>> >
>> >
>> > --------------------
>> > Thread-Topic: Trigger on IVR10015
>> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
>> > X-WBNR-Posting-Host: 207.59.211.138
>> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> > Subject: Trigger on IVR10015
>> > Date: Tue, 17 Oct 2006 05:19:02 -0700
>> > Lines: 17
>> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> > MIME-Version: 1.0
>> > Content-Type: text/plain;
>> > charset="Utf-8"
>> > Content-Transfer-Encoding: 7bit
>> > X-Newsreader: Microsoft CDO for Windows 2000
>> > Content-Class: urn:content-classes:message
>> > Importance: normal
>> > Priority: normal
>> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> > Newsgroups: microsoft.public.greatplains
>> > Path: TK2MSFTNGXA01.phx.gbl
>> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
>> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> > X-Tomcat-NG: microsoft.public.greatplains
>> >
>> > I'm trying to find a way to capture the actual created date on new 
>> > items
>> > when
>> > the item copy function is used (when you use the copy function it 
>> > stores
>> > the
>> > created date of the source item as the created date of the new item). 
>> > We
>> > have manufacturing but we don't have the QA module, so I was trying to
>> add
>> > a
>> > trigger to IVR10015 to put the current date in the QA Start Date field
>> and
>> > or
>> > the QA End Date field upon insert to IVR10015.
>> >
>> > When I do that and add a new item I get the message below.  How can I
>> tell
>> > what the constraint is that is being violated?  Any other ideas on how 
>> > to
>> > capture the actual created date?  Thanks.
>> >
>> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
>> conflicted
>> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
>> conflict
>> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
>> >
>> > -- 
>> > Jim@TurboChef
>> >
>> 


0
10/18/2006 9:26:59 PM
Yes, it would.  But I haven't been able to make that work either via a trigger.
-- 
Jim@TurboChef


"M. E. Houston" wrote:

> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We 
> don't use manufacturing so I assume that copying an existing item to a new 
> item will still create a record in IV00101.)
> 
> Ta,
> M.
> 
> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
> > Yes, but I didn't see any other date fields there except for created date 
> > and
> > modified date.  There are several fields we never need, but I'm not sure 
> > how
> > to store a date in them.  For example minshelflfe1 and minshelflife2 are
> > never relevant for our products, but those are integer fields.
> > -- 
> > Jim@TurboChef
> >
> >
> > "David Graening {MSFT}" wrote:
> >
> >> Jim,
> >>
> >> Besides using the IVR10015 table, have you looked to see if you can
> >> populate another field in the IV00101 table?  I am not sure if this would
> >> be easier to do this, but it is just another thought.
> >>
> >> Best Regards,
> >>
> >> David Graening
> >> Microsoft Online Partner Support
> >>
> >>
> >> --------------------
> >> Thread-Topic: Trigger on IVR10015
> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
> >> X-WBNR-Posting-Host: 207.59.211.138
> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
> >> Subject: RE: Trigger on IVR10015
> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
> >> Lines: 86
> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
> >> MIME-Version: 1.0
> >> Content-Type: text/plain;
> >> charset="Utf-8"
> >> Content-Transfer-Encoding: 7bit
> >> X-Newsreader: Microsoft CDO for Windows 2000
> >> Content-Class: urn:content-classes:message
> >> Importance: normal
> >> Priority: normal
> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> Newsgroups: microsoft.public.greatplains
> >> Path: TK2MSFTNGXA01.phx.gbl
> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> X-Tomcat-NG: microsoft.public.greatplains
> >>
> >> Thanks - we're using the modified date now, but as you noted that picks 
> >> up
> >> all changes so there is no consistent way to know which items are new on
> >> any
> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the 
> >> trigger.
> >> -- 
> >> Jim@TurboChef
> >>
> >>
> >> "David Graening {MSFT}" wrote:
> >>
> >> > Hi Jim,
> >> >
> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
> >> >
> >> > I understand you created a trigger to populate the QASTDATE start field
> >> in
> >> > the IVR10015 table.  This trigger was created to update that table to
> >> show
> >> > you when an Item Number was created.  If you use the copy feature for
> >> > copying item numbers, the Created Date in the IV00101 table shows the
> >> date
> >> > from the original item number.  This is not what you need.
> >> >
> >> > I am not sure what you have setup for your trigger and but it probably
> >> does
> >> > not matter as this field may not be able to be populated.  What you can
> >> > look at is first updating the QAQCNEEDED_I field first.  When the
> >> > QAQCNEEDED_I field has a value of 1, the date field is available.  If 
> >> > it
> >> is
> >> > set to 0, the date field is not available.  This may be causing the 
> >> > error
> >> > message you are receiving.
> >> >
> >> > I looked further into the IV00101 table and I was wondering if you 
> >> > would
> >> be
> >> > able to use the MODIFDT (Modified Date) for you issue.  This field is
> >> > updated right away with the user date when the copy feature is used. 
> >> > The
> >> > draw back of this field is that it is updated if you change anything on
> >> the
> >> > Item Number.
> >> >
> >> > Have a great day!
> >> >
> >> > Best Regards,
> >> >
> >> > David Graening
> >> > Microsoft Online Partner Support
> >> >
> >> >
> >> > --------------------
> >> > Thread-Topic: Trigger on IVR10015
> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> >> > X-WBNR-Posting-Host: 207.59.211.138
> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> > Subject: Trigger on IVR10015
> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
> >> > Lines: 17
> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> > MIME-Version: 1.0
> >> > Content-Type: text/plain;
> >> > charset="Utf-8"
> >> > Content-Transfer-Encoding: 7bit
> >> > X-Newsreader: Microsoft CDO for Windows 2000
> >> > Content-Class: urn:content-classes:message
> >> > Importance: normal
> >> > Priority: normal
> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> > Newsgroups: microsoft.public.greatplains
> >> > Path: TK2MSFTNGXA01.phx.gbl
> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> > X-Tomcat-NG: microsoft.public.greatplains
> >> >
> >> > I'm trying to find a way to capture the actual created date on new 
> >> > items
> >> > when
> >> > the item copy function is used (when you use the copy function it 
> >> > stores
> >> > the
> >> > created date of the source item as the created date of the new item). 
> >> > We
> >> > have manufacturing but we don't have the QA module, so I was trying to
> >> add
> >> > a
> >> > trigger to IVR10015 to put the current date in the QA Start Date field
> >> and
> >> > or
> >> > the QA End Date field upon insert to IVR10015.
> >> >
> >> > When I do that and add a new item I get the message below.  How can I
> >> tell
> >> > what the constraint is that is being violated?  Any other ideas on how 
> >> > to
> >> > capture the actual created date?  Thanks.
> >> >
> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
> >> conflicted
> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
> >> conflict
> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> >> >
> >> > -- 
> >> > Jim@TurboChef
> >> >
> >> 
> 
> 
> 
0
jim.harris (398)
10/18/2006 9:33:02 PM
Jim,

If I have understood you correctly, the create date is being copied from the 
source item when you use the copy function in GP
You want to correct for that.  You could use a trigger but it is probably 
just as easy to use a stored procedure that runs once a day and achieves 
that.

Try this:
first create a DUOS object  - let's call it 'InventoryItem'

then just use a stored proc as below  schedule it to run  as often as you 
like - normally, once a day should be sufficient
I haven't tested this code.  it is brain dump so test it please.

HS
-----------------------------------------------------
 create procedure usp_HS_FixItemCreateDate as
 /* Created by HSalim 10/19/2006
 Fixes create dates for items added using GP's copy functionality
 Schedule this proc to run once a day at say 11pm
 */

declare @DexID varchar(20)
Select @DexID = PropertyValue from SY90000 where ObjectType = 
'InventoryItem' and Objectid = 'LastItem' and PropertyName = 'DexID'

UPDATE IV00101 set CREATDDT = cast(cast(getdate() as int) as smalldatetime)
where DEX_ROW_ID > @DexID

Select  @dexid =  max(dex_row_id) from IV00101

update sy90000 set PropertyValue = @DexID
where ObjectType = 'InventoryItem' and Objectid = 'LastItem' and 
PropertyName = 'DexID'

go

grant all on usp_HS_FixItemCreateDate to DynGRP

-- 
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge.  Add your favorite questions and answers
Help add questions to this site!  We want Your input.
--------------------------------------------
"Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
> Yes, it would.  But I haven't been able to make that work either via a 
> trigger.
> -- 
> Jim@TurboChef
>
>
> "M. E. Houston" wrote:
>
>> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We
>> don't use manufacturing so I assume that copying an existing item to a 
>> new
>> item will still create a record in IV00101.)
>>
>> Ta,
>> M.
>>
>> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
>> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
>> > Yes, but I didn't see any other date fields there except for created 
>> > date
>> > and
>> > modified date.  There are several fields we never need, but I'm not 
>> > sure
>> > how
>> > to store a date in them.  For example minshelflfe1 and minshelflife2 
>> > are
>> > never relevant for our products, but those are integer fields.
>> > -- 
>> > Jim@TurboChef
>> >
>> >
>> > "David Graening {MSFT}" wrote:
>> >
>> >> Jim,
>> >>
>> >> Besides using the IVR10015 table, have you looked to see if you can
>> >> populate another field in the IV00101 table?  I am not sure if this 
>> >> would
>> >> be easier to do this, but it is just another thought.
>> >>
>> >> Best Regards,
>> >>
>> >> David Graening
>> >> Microsoft Online Partner Support
>> >>
>> >>
>> >> --------------------
>> >> Thread-Topic: Trigger on IVR10015
>> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
>> >> X-WBNR-Posting-Host: 207.59.211.138
>> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
>> >> Subject: RE: Trigger on IVR10015
>> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
>> >> Lines: 86
>> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
>> >> MIME-Version: 1.0
>> >> Content-Type: text/plain;
>> >> charset="Utf-8"
>> >> Content-Transfer-Encoding: 7bit
>> >> X-Newsreader: Microsoft CDO for Windows 2000
>> >> Content-Class: urn:content-classes:message
>> >> Importance: normal
>> >> Priority: normal
>> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> Newsgroups: microsoft.public.greatplains
>> >> Path: TK2MSFTNGXA01.phx.gbl
>> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
>> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> X-Tomcat-NG: microsoft.public.greatplains
>> >>
>> >> Thanks - we're using the modified date now, but as you noted that 
>> >> picks
>> >> up
>> >> all changes so there is no consistent way to know which items are new 
>> >> on
>> >> any
>> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
>> >> trigger.
>> >> -- 
>> >> Jim@TurboChef
>> >>
>> >>
>> >> "David Graening {MSFT}" wrote:
>> >>
>> >> > Hi Jim,
>> >> >
>> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
>> >> >
>> >> > I understand you created a trigger to populate the QASTDATE start 
>> >> > field
>> >> in
>> >> > the IVR10015 table.  This trigger was created to update that table 
>> >> > to
>> >> show
>> >> > you when an Item Number was created.  If you use the copy feature 
>> >> > for
>> >> > copying item numbers, the Created Date in the IV00101 table shows 
>> >> > the
>> >> date
>> >> > from the original item number.  This is not what you need.
>> >> >
>> >> > I am not sure what you have setup for your trigger and but it 
>> >> > probably
>> >> does
>> >> > not matter as this field may not be able to be populated.  What you 
>> >> > can
>> >> > look at is first updating the QAQCNEEDED_I field first.  When the
>> >> > QAQCNEEDED_I field has a value of 1, the date field is available. 
>> >> > If
>> >> > it
>> >> is
>> >> > set to 0, the date field is not available.  This may be causing the
>> >> > error
>> >> > message you are receiving.
>> >> >
>> >> > I looked further into the IV00101 table and I was wondering if you
>> >> > would
>> >> be
>> >> > able to use the MODIFDT (Modified Date) for you issue.  This field 
>> >> > is
>> >> > updated right away with the user date when the copy feature is used.
>> >> > The
>> >> > draw back of this field is that it is updated if you change anything 
>> >> > on
>> >> the
>> >> > Item Number.
>> >> >
>> >> > Have a great day!
>> >> >
>> >> > Best Regards,
>> >> >
>> >> > David Graening
>> >> > Microsoft Online Partner Support
>> >> >
>> >> >
>> >> > --------------------
>> >> > Thread-Topic: Trigger on IVR10015
>> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
>> >> > X-WBNR-Posting-Host: 207.59.211.138
>> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> > Subject: Trigger on IVR10015
>> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
>> >> > Lines: 17
>> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> > MIME-Version: 1.0
>> >> > Content-Type: text/plain;
>> >> > charset="Utf-8"
>> >> > Content-Transfer-Encoding: 7bit
>> >> > X-Newsreader: Microsoft CDO for Windows 2000
>> >> > Content-Class: urn:content-classes:message
>> >> > Importance: normal
>> >> > Priority: normal
>> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> > Newsgroups: microsoft.public.greatplains
>> >> > Path: TK2MSFTNGXA01.phx.gbl
>> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
>> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> > X-Tomcat-NG: microsoft.public.greatplains
>> >> >
>> >> > I'm trying to find a way to capture the actual created date on new
>> >> > items
>> >> > when
>> >> > the item copy function is used (when you use the copy function it
>> >> > stores
>> >> > the
>> >> > created date of the source item as the created date of the new 
>> >> > item).
>> >> > We
>> >> > have manufacturing but we don't have the QA module, so I was trying 
>> >> > to
>> >> add
>> >> > a
>> >> > trigger to IVR10015 to put the current date in the QA Start Date 
>> >> > field
>> >> and
>> >> > or
>> >> > the QA End Date field upon insert to IVR10015.
>> >> >
>> >> > When I do that and add a new item I get the message below.  How can 
>> >> > I
>> >> tell
>> >> > what the constraint is that is being violated?  Any other ideas on 
>> >> > how
>> >> > to
>> >> > capture the actual created date?  Thanks.
>> >> >
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
>> >> conflicted
>> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
>> >> conflict
>> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
>> >> >
>> >> > -- 
>> >> > Jim@TurboChef
>> >> >
>> >>
>>
>>
>> 


0
HSalim (1270)
10/19/2006 6:52:37 PM
This is very quick and dirty just to get a trigger to work.

CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
AFTER INSERT
AS
UPDATE dbo.IV00101
SET dbo.IV00101.CREATDDT = CAST((CONVERT(CHAR(10), GETDATE(), 102) + ' 
00:00:00') AS DATETIME)
FROM inserted
WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR

Where the problem lies with the constraint is that the date must have 0 for 
hours, minutes, and seconds.

I hope this helps to get you on track.

Ta,
M.

"Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
> Yes, it would.  But I haven't been able to make that work either via a 
> trigger.
> -- 
> Jim@TurboChef
>
>
> "M. E. Houston" wrote:
>
>> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We
>> don't use manufacturing so I assume that copying an existing item to a 
>> new
>> item will still create a record in IV00101.)
>>
>> Ta,
>> M.
>>
>> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
>> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
>> > Yes, but I didn't see any other date fields there except for created 
>> > date
>> > and
>> > modified date.  There are several fields we never need, but I'm not 
>> > sure
>> > how
>> > to store a date in them.  For example minshelflfe1 and minshelflife2 
>> > are
>> > never relevant for our products, but those are integer fields.
>> > -- 
>> > Jim@TurboChef
>> >
>> >
>> > "David Graening {MSFT}" wrote:
>> >
>> >> Jim,
>> >>
>> >> Besides using the IVR10015 table, have you looked to see if you can
>> >> populate another field in the IV00101 table?  I am not sure if this 
>> >> would
>> >> be easier to do this, but it is just another thought.
>> >>
>> >> Best Regards,
>> >>
>> >> David Graening
>> >> Microsoft Online Partner Support
>> >>
>> >>
>> >> --------------------
>> >> Thread-Topic: Trigger on IVR10015
>> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
>> >> X-WBNR-Posting-Host: 207.59.211.138
>> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
>> >> Subject: RE: Trigger on IVR10015
>> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
>> >> Lines: 86
>> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
>> >> MIME-Version: 1.0
>> >> Content-Type: text/plain;
>> >> charset="Utf-8"
>> >> Content-Transfer-Encoding: 7bit
>> >> X-Newsreader: Microsoft CDO for Windows 2000
>> >> Content-Class: urn:content-classes:message
>> >> Importance: normal
>> >> Priority: normal
>> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> Newsgroups: microsoft.public.greatplains
>> >> Path: TK2MSFTNGXA01.phx.gbl
>> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
>> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> X-Tomcat-NG: microsoft.public.greatplains
>> >>
>> >> Thanks - we're using the modified date now, but as you noted that 
>> >> picks
>> >> up
>> >> all changes so there is no consistent way to know which items are new 
>> >> on
>> >> any
>> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
>> >> trigger.
>> >> -- 
>> >> Jim@TurboChef
>> >>
>> >>
>> >> "David Graening {MSFT}" wrote:
>> >>
>> >> > Hi Jim,
>> >> >
>> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
>> >> >
>> >> > I understand you created a trigger to populate the QASTDATE start 
>> >> > field
>> >> in
>> >> > the IVR10015 table.  This trigger was created to update that table 
>> >> > to
>> >> show
>> >> > you when an Item Number was created.  If you use the copy feature 
>> >> > for
>> >> > copying item numbers, the Created Date in the IV00101 table shows 
>> >> > the
>> >> date
>> >> > from the original item number.  This is not what you need.
>> >> >
>> >> > I am not sure what you have setup for your trigger and but it 
>> >> > probably
>> >> does
>> >> > not matter as this field may not be able to be populated.  What you 
>> >> > can
>> >> > look at is first updating the QAQCNEEDED_I field first.  When the
>> >> > QAQCNEEDED_I field has a value of 1, the date field is available. 
>> >> > If
>> >> > it
>> >> is
>> >> > set to 0, the date field is not available.  This may be causing the
>> >> > error
>> >> > message you are receiving.
>> >> >
>> >> > I looked further into the IV00101 table and I was wondering if you
>> >> > would
>> >> be
>> >> > able to use the MODIFDT (Modified Date) for you issue.  This field 
>> >> > is
>> >> > updated right away with the user date when the copy feature is used.
>> >> > The
>> >> > draw back of this field is that it is updated if you change anything 
>> >> > on
>> >> the
>> >> > Item Number.
>> >> >
>> >> > Have a great day!
>> >> >
>> >> > Best Regards,
>> >> >
>> >> > David Graening
>> >> > Microsoft Online Partner Support
>> >> >
>> >> >
>> >> > --------------------
>> >> > Thread-Topic: Trigger on IVR10015
>> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
>> >> > X-WBNR-Posting-Host: 207.59.211.138
>> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> > Subject: Trigger on IVR10015
>> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
>> >> > Lines: 17
>> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> > MIME-Version: 1.0
>> >> > Content-Type: text/plain;
>> >> > charset="Utf-8"
>> >> > Content-Transfer-Encoding: 7bit
>> >> > X-Newsreader: Microsoft CDO for Windows 2000
>> >> > Content-Class: urn:content-classes:message
>> >> > Importance: normal
>> >> > Priority: normal
>> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> > Newsgroups: microsoft.public.greatplains
>> >> > Path: TK2MSFTNGXA01.phx.gbl
>> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
>> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> > X-Tomcat-NG: microsoft.public.greatplains
>> >> >
>> >> > I'm trying to find a way to capture the actual created date on new
>> >> > items
>> >> > when
>> >> > the item copy function is used (when you use the copy function it
>> >> > stores
>> >> > the
>> >> > created date of the source item as the created date of the new 
>> >> > item).
>> >> > We
>> >> > have manufacturing but we don't have the QA module, so I was trying 
>> >> > to
>> >> add
>> >> > a
>> >> > trigger to IVR10015 to put the current date in the QA Start Date 
>> >> > field
>> >> and
>> >> > or
>> >> > the QA End Date field upon insert to IVR10015.
>> >> >
>> >> > When I do that and add a new item I get the message below.  How can 
>> >> > I
>> >> tell
>> >> > what the constraint is that is being violated?  Any other ideas on 
>> >> > how
>> >> > to
>> >> > capture the actual created date?  Thanks.
>> >> >
>> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
>> >> conflicted
>> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
>> >> conflict
>> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
>> >> >
>> >> > -- 
>> >> > Jim@TurboChef
>> >> >
>> >>
>>
>>
>> 


0
10/19/2006 8:06:03 PM
HS,
How do you create a DUOS object?
Stacey

"M. E. Houston" wrote:

> This is very quick and dirty just to get a trigger to work.
> 
> CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
> AFTER INSERT
> AS
> UPDATE dbo.IV00101
> SET dbo.IV00101.CREATDDT = CAST((CONVERT(CHAR(10), GETDATE(), 102) + ' 
> 00:00:00') AS DATETIME)
> FROM inserted
> WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR
> 
> Where the problem lies with the constraint is that the date must have 0 for 
> hours, minutes, and seconds.
> 
> I hope this helps to get you on track.
> 
> Ta,
> M.
> 
> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
> news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
> > Yes, it would.  But I haven't been able to make that work either via a 
> > trigger.
> > -- 
> > Jim@TurboChef
> >
> >
> > "M. E. Houston" wrote:
> >
> >> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We
> >> don't use manufacturing so I assume that copying an existing item to a 
> >> new
> >> item will still create a record in IV00101.)
> >>
> >> Ta,
> >> M.
> >>
> >> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
> >> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
> >> > Yes, but I didn't see any other date fields there except for created 
> >> > date
> >> > and
> >> > modified date.  There are several fields we never need, but I'm not 
> >> > sure
> >> > how
> >> > to store a date in them.  For example minshelflfe1 and minshelflife2 
> >> > are
> >> > never relevant for our products, but those are integer fields.
> >> > -- 
> >> > Jim@TurboChef
> >> >
> >> >
> >> > "David Graening {MSFT}" wrote:
> >> >
> >> >> Jim,
> >> >>
> >> >> Besides using the IVR10015 table, have you looked to see if you can
> >> >> populate another field in the IV00101 table?  I am not sure if this 
> >> >> would
> >> >> be easier to do this, but it is just another thought.
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> David Graening
> >> >> Microsoft Online Partner Support
> >> >>
> >> >>
> >> >> --------------------
> >> >> Thread-Topic: Trigger on IVR10015
> >> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
> >> >> X-WBNR-Posting-Host: 207.59.211.138
> >> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
> >> >> Subject: RE: Trigger on IVR10015
> >> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
> >> >> Lines: 86
> >> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
> >> >> MIME-Version: 1.0
> >> >> Content-Type: text/plain;
> >> >> charset="Utf-8"
> >> >> Content-Transfer-Encoding: 7bit
> >> >> X-Newsreader: Microsoft CDO for Windows 2000
> >> >> Content-Class: urn:content-classes:message
> >> >> Importance: normal
> >> >> Priority: normal
> >> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> Newsgroups: microsoft.public.greatplains
> >> >> Path: TK2MSFTNGXA01.phx.gbl
> >> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
> >> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> X-Tomcat-NG: microsoft.public.greatplains
> >> >>
> >> >> Thanks - we're using the modified date now, but as you noted that 
> >> >> picks
> >> >> up
> >> >> all changes so there is no consistent way to know which items are new 
> >> >> on
> >> >> any
> >> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
> >> >> trigger.
> >> >> -- 
> >> >> Jim@TurboChef
> >> >>
> >> >>
> >> >> "David Graening {MSFT}" wrote:
> >> >>
> >> >> > Hi Jim,
> >> >> >
> >> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
> >> >> >
> >> >> > I understand you created a trigger to populate the QASTDATE start 
> >> >> > field
> >> >> in
> >> >> > the IVR10015 table.  This trigger was created to update that table 
> >> >> > to
> >> >> show
> >> >> > you when an Item Number was created.  If you use the copy feature 
> >> >> > for
> >> >> > copying item numbers, the Created Date in the IV00101 table shows 
> >> >> > the
> >> >> date
> >> >> > from the original item number.  This is not what you need.
> >> >> >
> >> >> > I am not sure what you have setup for your trigger and but it 
> >> >> > probably
> >> >> does
> >> >> > not matter as this field may not be able to be populated.  What you 
> >> >> > can
> >> >> > look at is first updating the QAQCNEEDED_I field first.  When the
> >> >> > QAQCNEEDED_I field has a value of 1, the date field is available. 
> >> >> > If
> >> >> > it
> >> >> is
> >> >> > set to 0, the date field is not available.  This may be causing the
> >> >> > error
> >> >> > message you are receiving.
> >> >> >
> >> >> > I looked further into the IV00101 table and I was wondering if you
> >> >> > would
> >> >> be
> >> >> > able to use the MODIFDT (Modified Date) for you issue.  This field 
> >> >> > is
> >> >> > updated right away with the user date when the copy feature is used.
> >> >> > The
> >> >> > draw back of this field is that it is updated if you change anything 
> >> >> > on
> >> >> the
> >> >> > Item Number.
> >> >> >
> >> >> > Have a great day!
> >> >> >
> >> >> > Best Regards,
> >> >> >
> >> >> > David Graening
> >> >> > Microsoft Online Partner Support
> >> >> >
> >> >> >
> >> >> > --------------------
> >> >> > Thread-Topic: Trigger on IVR10015
> >> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> >> >> > X-WBNR-Posting-Host: 207.59.211.138
> >> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> > Subject: Trigger on IVR10015
> >> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
> >> >> > Lines: 17
> >> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> > MIME-Version: 1.0
> >> >> > Content-Type: text/plain;
> >> >> > charset="Utf-8"
> >> >> > Content-Transfer-Encoding: 7bit
> >> >> > X-Newsreader: Microsoft CDO for Windows 2000
> >> >> > Content-Class: urn:content-classes:message
> >> >> > Importance: normal
> >> >> > Priority: normal
> >> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> > Newsgroups: microsoft.public.greatplains
> >> >> > Path: TK2MSFTNGXA01.phx.gbl
> >> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> >> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> > X-Tomcat-NG: microsoft.public.greatplains
> >> >> >
> >> >> > I'm trying to find a way to capture the actual created date on new
> >> >> > items
> >> >> > when
> >> >> > the item copy function is used (when you use the copy function it
> >> >> > stores
> >> >> > the
> >> >> > created date of the source item as the created date of the new 
> >> >> > item).
> >> >> > We
> >> >> > have manufacturing but we don't have the QA module, so I was trying 
> >> >> > to
> >> >> add
> >> >> > a
> >> >> > trigger to IVR10015 to put the current date in the QA Start Date 
> >> >> > field
> >> >> and
> >> >> > or
> >> >> > the QA End Date field upon insert to IVR10015.
> >> >> >
> >> >> > When I do that and add a new item I get the message below.  How can 
> >> >> > I
> >> >> tell
> >> >> > what the constraint is that is being violated?  Any other ideas on 
> >> >> > how
> >> >> > to
> >> >> > capture the actual created date?  Thanks.
> >> >> >
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
> >> >> conflicted
> >> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
> >> >> conflict
> >> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> >> >> >
> >> >> > -- 
> >> >> > Jim@TurboChef
> >> >> >
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
Stacey (160)
10/20/2006 4:59:01 AM
The VBA Developers Guide explains DUOS objects.  The objects are stored in 
SY90000.

TA,
M.

"Stacey" <Stacey@discussions.microsoft.com> wrote in message 
news:4CADAE31-BEAB-4657-9EE7-38C81CE4D0C7@microsoft.com...
> HS,
> How do you create a DUOS object?
> Stacey 


0
10/20/2006 1:45:15 PM
We dont have VBA. I was hoping HSalim will give me a way to do this without VBA

"M. E. Houston" wrote:

> The VBA Developers Guide explains DUOS objects.  The objects are stored in 
> SY90000.
> 
> TA,
> M.
> 
> "Stacey" <Stacey@discussions.microsoft.com> wrote in message 
> news:4CADAE31-BEAB-4657-9EE7-38C81CE4D0C7@microsoft.com...
> > HS,
> > How do you create a DUOS object?
> > Stacey 
> 
> 
> 
0
Stacey (160)
10/20/2006 7:26:02 PM
The VBA Developers Guide explains what DUOS objects are and how to use them 
(specifically with VBA but there is no reason why a DUOS object couldn't be 
used in some other way as HSalim demonstrated.)  The VBA Developers Guids 
should be in the documentation folder under the Great Plains install.

Whether or not you can utilize a DUOS object depends on what you are trying 
to accomplish and you won't be able to figure out whether it will work for 
you until you understand the structure of SY90000 (the DUOS table).  I 
recommend you find and read the DUOS section of the VBA Developers Guide to 
see if what you're trying to do will fit into that structure.

Ta,
M.


"Stacey" <Stacey@discussions.microsoft.com> wrote in message 
news:D7FB1869-479A-4F9A-B7C7-FF9869DBF81D@microsoft.com...
> We dont have VBA. I was hoping HSalim will give me a way to do this 
> without VBA
>
> "M. E. Houston" wrote:
>
>> The VBA Developers Guide explains DUOS objects.  The objects are stored 
>> in
>> SY90000.
>>
>> TA,
>> M.
>>
>> "Stacey" <Stacey@discussions.microsoft.com> wrote in message
>> news:4CADAE31-BEAB-4657-9EE7-38C81CE4D0C7@microsoft.com...
>> > HS,
>> > How do you create a DUOS object?
>> > Stacey
>>
>>
>> 


0
10/20/2006 9:34:46 PM
Thanks for the help so far.  This seems to get closer, but I still hit an 
error "Syntax error converting datetime from character string."  I've tried 
some different variations on using CAST or CONVERT but haven't found the 
right format yet to make it work.  Any further suggestions?
-- 
Jim@TurboChef


"M. E. Houston" wrote:

> This is very quick and dirty just to get a trigger to work.
> 
> CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
> AFTER INSERT
> AS
> UPDATE dbo.IV00101
> SET dbo.IV00101.CREATDDT = CAST((CONVERT(CHAR(10), GETDATE(), 102) + ' 
> 00:00:00') AS DATETIME)
> FROM inserted
> WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR
> 
> Where the problem lies with the constraint is that the date must have 0 for 
> hours, minutes, and seconds.
> 
> I hope this helps to get you on track.
> 
> Ta,
> M.
> 
> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
> news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
> > Yes, it would.  But I haven't been able to make that work either via a 
> > trigger.
> > -- 
> > Jim@TurboChef
> >
> >
> > "M. E. Houston" wrote:
> >
> >> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We
> >> don't use manufacturing so I assume that copying an existing item to a 
> >> new
> >> item will still create a record in IV00101.)
> >>
> >> Ta,
> >> M.
> >>
> >> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
> >> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
> >> > Yes, but I didn't see any other date fields there except for created 
> >> > date
> >> > and
> >> > modified date.  There are several fields we never need, but I'm not 
> >> > sure
> >> > how
> >> > to store a date in them.  For example minshelflfe1 and minshelflife2 
> >> > are
> >> > never relevant for our products, but those are integer fields.
> >> > -- 
> >> > Jim@TurboChef
> >> >
> >> >
> >> > "David Graening {MSFT}" wrote:
> >> >
> >> >> Jim,
> >> >>
> >> >> Besides using the IVR10015 table, have you looked to see if you can
> >> >> populate another field in the IV00101 table?  I am not sure if this 
> >> >> would
> >> >> be easier to do this, but it is just another thought.
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> David Graening
> >> >> Microsoft Online Partner Support
> >> >>
> >> >>
> >> >> --------------------
> >> >> Thread-Topic: Trigger on IVR10015
> >> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
> >> >> X-WBNR-Posting-Host: 207.59.211.138
> >> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
> >> >> Subject: RE: Trigger on IVR10015
> >> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
> >> >> Lines: 86
> >> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
> >> >> MIME-Version: 1.0
> >> >> Content-Type: text/plain;
> >> >> charset="Utf-8"
> >> >> Content-Transfer-Encoding: 7bit
> >> >> X-Newsreader: Microsoft CDO for Windows 2000
> >> >> Content-Class: urn:content-classes:message
> >> >> Importance: normal
> >> >> Priority: normal
> >> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> Newsgroups: microsoft.public.greatplains
> >> >> Path: TK2MSFTNGXA01.phx.gbl
> >> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
> >> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> X-Tomcat-NG: microsoft.public.greatplains
> >> >>
> >> >> Thanks - we're using the modified date now, but as you noted that 
> >> >> picks
> >> >> up
> >> >> all changes so there is no consistent way to know which items are new 
> >> >> on
> >> >> any
> >> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
> >> >> trigger.
> >> >> -- 
> >> >> Jim@TurboChef
> >> >>
> >> >>
> >> >> "David Graening {MSFT}" wrote:
> >> >>
> >> >> > Hi Jim,
> >> >> >
> >> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
> >> >> >
> >> >> > I understand you created a trigger to populate the QASTDATE start 
> >> >> > field
> >> >> in
> >> >> > the IVR10015 table.  This trigger was created to update that table 
> >> >> > to
> >> >> show
> >> >> > you when an Item Number was created.  If you use the copy feature 
> >> >> > for
> >> >> > copying item numbers, the Created Date in the IV00101 table shows 
> >> >> > the
> >> >> date
> >> >> > from the original item number.  This is not what you need.
> >> >> >
> >> >> > I am not sure what you have setup for your trigger and but it 
> >> >> > probably
> >> >> does
> >> >> > not matter as this field may not be able to be populated.  What you 
> >> >> > can
> >> >> > look at is first updating the QAQCNEEDED_I field first.  When the
> >> >> > QAQCNEEDED_I field has a value of 1, the date field is available. 
> >> >> > If
> >> >> > it
> >> >> is
> >> >> > set to 0, the date field is not available.  This may be causing the
> >> >> > error
> >> >> > message you are receiving.
> >> >> >
> >> >> > I looked further into the IV00101 table and I was wondering if you
> >> >> > would
> >> >> be
> >> >> > able to use the MODIFDT (Modified Date) for you issue.  This field 
> >> >> > is
> >> >> > updated right away with the user date when the copy feature is used.
> >> >> > The
> >> >> > draw back of this field is that it is updated if you change anything 
> >> >> > on
> >> >> the
> >> >> > Item Number.
> >> >> >
> >> >> > Have a great day!
> >> >> >
> >> >> > Best Regards,
> >> >> >
> >> >> > David Graening
> >> >> > Microsoft Online Partner Support
> >> >> >
> >> >> >
> >> >> > --------------------
> >> >> > Thread-Topic: Trigger on IVR10015
> >> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> >> >> > X-WBNR-Posting-Host: 207.59.211.138
> >> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> > Subject: Trigger on IVR10015
> >> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
> >> >> > Lines: 17
> >> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> > MIME-Version: 1.0
> >> >> > Content-Type: text/plain;
> >> >> > charset="Utf-8"
> >> >> > Content-Transfer-Encoding: 7bit
> >> >> > X-Newsreader: Microsoft CDO for Windows 2000
> >> >> > Content-Class: urn:content-classes:message
> >> >> > Importance: normal
> >> >> > Priority: normal
> >> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> > Newsgroups: microsoft.public.greatplains
> >> >> > Path: TK2MSFTNGXA01.phx.gbl
> >> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> >> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> > X-Tomcat-NG: microsoft.public.greatplains
> >> >> >
> >> >> > I'm trying to find a way to capture the actual created date on new
> >> >> > items
> >> >> > when
> >> >> > the item copy function is used (when you use the copy function it
> >> >> > stores
> >> >> > the
> >> >> > created date of the source item as the created date of the new 
> >> >> > item).
> >> >> > We
> >> >> > have manufacturing but we don't have the QA module, so I was trying 
> >> >> > to
> >> >> add
> >> >> > a
> >> >> > trigger to IVR10015 to put the current date in the QA Start Date 
> >> >> > field
> >> >> and
> >> >> > or
> >> >> > the QA End Date field upon insert to IVR10015.
> >> >> >
> >> >> > When I do that and add a new item I get the message below.  How can 
> >> >> > I
> >> >> tell
> >> >> > what the constraint is that is being violated?  Any other ideas on 
> >> >> > how
> >> >> > to
> >> >> > capture the actual created date?  Thanks.
> >> >> >
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
> >> >> conflicted
> >> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
> >> >> conflict
> >> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> >> >> >
> >> >> > -- 
> >> >> > Jim@TurboChef
> >> >> >
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
jim.harris (398)
10/23/2006 2:15:01 PM
Thank you.  This got me on the right track.  The following version of this 
trigger is working now for both copied items and items keyed from scratch.

CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
AFTER INSERT
AS
UPDATE dbo.IV00101
SET dbo.IV00101.CREATDDT = DATEADD([DAY], 0, DATEDIFF([DAY], 0, GETDATE()))
FROM inserted
WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR
-- 
Jim@TurboChef


"M. E. Houston" wrote:

> This is very quick and dirty just to get a trigger to work.
> 
> CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
> AFTER INSERT
> AS
> UPDATE dbo.IV00101
> SET dbo.IV00101.CREATDDT = CAST((CONVERT(CHAR(10), GETDATE(), 102) + ' 
> 00:00:00') AS DATETIME)
> FROM inserted
> WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR
> 
> Where the problem lies with the constraint is that the date must have 0 for 
> hours, minutes, and seconds.
> 
> I hope this helps to get you on track.
> 
> Ta,
> M.
> 
> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
> news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
> > Yes, it would.  But I haven't been able to make that work either via a 
> > trigger.
> > -- 
> > Jim@TurboChef
> >
> >
> > "M. E. Houston" wrote:
> >
> >> Wouldn't changing the CREATDDT in IV00101 accomplish what you want?  (We
> >> don't use manufacturing so I assume that copying an existing item to a 
> >> new
> >> item will still create a record in IV00101.)
> >>
> >> Ta,
> >> M.
> >>
> >> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
> >> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
> >> > Yes, but I didn't see any other date fields there except for created 
> >> > date
> >> > and
> >> > modified date.  There are several fields we never need, but I'm not 
> >> > sure
> >> > how
> >> > to store a date in them.  For example minshelflfe1 and minshelflife2 
> >> > are
> >> > never relevant for our products, but those are integer fields.
> >> > -- 
> >> > Jim@TurboChef
> >> >
> >> >
> >> > "David Graening {MSFT}" wrote:
> >> >
> >> >> Jim,
> >> >>
> >> >> Besides using the IVR10015 table, have you looked to see if you can
> >> >> populate another field in the IV00101 table?  I am not sure if this 
> >> >> would
> >> >> be easier to do this, but it is just another thought.
> >> >>
> >> >> Best Regards,
> >> >>
> >> >> David Graening
> >> >> Microsoft Online Partner Support
> >> >>
> >> >>
> >> >> --------------------
> >> >> Thread-Topic: Trigger on IVR10015
> >> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
> >> >> X-WBNR-Posting-Host: 207.59.211.138
> >> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
> >> >> Subject: RE: Trigger on IVR10015
> >> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
> >> >> Lines: 86
> >> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
> >> >> MIME-Version: 1.0
> >> >> Content-Type: text/plain;
> >> >> charset="Utf-8"
> >> >> Content-Transfer-Encoding: 7bit
> >> >> X-Newsreader: Microsoft CDO for Windows 2000
> >> >> Content-Class: urn:content-classes:message
> >> >> Importance: normal
> >> >> Priority: normal
> >> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> Newsgroups: microsoft.public.greatplains
> >> >> Path: TK2MSFTNGXA01.phx.gbl
> >> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
> >> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> X-Tomcat-NG: microsoft.public.greatplains
> >> >>
> >> >> Thanks - we're using the modified date now, but as you noted that 
> >> >> picks
> >> >> up
> >> >> all changes so there is no consistent way to know which items are new 
> >> >> on
> >> >> any
> >> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
> >> >> trigger.
> >> >> -- 
> >> >> Jim@TurboChef
> >> >>
> >> >>
> >> >> "David Graening {MSFT}" wrote:
> >> >>
> >> >> > Hi Jim,
> >> >> >
> >> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
> >> >> >
> >> >> > I understand you created a trigger to populate the QASTDATE start 
> >> >> > field
> >> >> in
> >> >> > the IVR10015 table.  This trigger was created to update that table 
> >> >> > to
> >> >> show
> >> >> > you when an Item Number was created.  If you use the copy feature 
> >> >> > for
> >> >> > copying item numbers, the Created Date in the IV00101 table shows 
> >> >> > the
> >> >> date
> >> >> > from the original item number.  This is not what you need.
> >> >> >
> >> >> > I am not sure what you have setup for your trigger and but it 
> >> >> > probably
> >> >> does
> >> >> > not matter as this field may not be able to be populated.  What you 
> >> >> > can
> >> >> > look at is first updating the QAQCNEEDED_I field first.  When the
> >> >> > QAQCNEEDED_I field has a value of 1, the date field is available. 
> >> >> > If
> >> >> > it
> >> >> is
> >> >> > set to 0, the date field is not available.  This may be causing the
> >> >> > error
> >> >> > message you are receiving.
> >> >> >
> >> >> > I looked further into the IV00101 table and I was wondering if you
> >> >> > would
> >> >> be
> >> >> > able to use the MODIFDT (Modified Date) for you issue.  This field 
> >> >> > is
> >> >> > updated right away with the user date when the copy feature is used.
> >> >> > The
> >> >> > draw back of this field is that it is updated if you change anything 
> >> >> > on
> >> >> the
> >> >> > Item Number.
> >> >> >
> >> >> > Have a great day!
> >> >> >
> >> >> > Best Regards,
> >> >> >
> >> >> > David Graening
> >> >> > Microsoft Online Partner Support
> >> >> >
> >> >> >
> >> >> > --------------------
> >> >> > Thread-Topic: Trigger on IVR10015
> >> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
> >> >> > X-WBNR-Posting-Host: 207.59.211.138
> >> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
> >> >> > Subject: Trigger on IVR10015
> >> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
> >> >> > Lines: 17
> >> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
> >> >> > MIME-Version: 1.0
> >> >> > Content-Type: text/plain;
> >> >> > charset="Utf-8"
> >> >> > Content-Transfer-Encoding: 7bit
> >> >> > X-Newsreader: Microsoft CDO for Windows 2000
> >> >> > Content-Class: urn:content-classes:message
> >> >> > Importance: normal
> >> >> > Priority: normal
> >> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >> >> > Newsgroups: microsoft.public.greatplains
> >> >> > Path: TK2MSFTNGXA01.phx.gbl
> >> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
> >> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >> >> > X-Tomcat-NG: microsoft.public.greatplains
> >> >> >
> >> >> > I'm trying to find a way to capture the actual created date on new
> >> >> > items
> >> >> > when
> >> >> > the item copy function is used (when you use the copy function it
> >> >> > stores
> >> >> > the
> >> >> > created date of the source item as the created date of the new 
> >> >> > item).
> >> >> > We
> >> >> > have manufacturing but we don't have the QA module, so I was trying 
> >> >> > to
> >> >> add
> >> >> > a
> >> >> > trigger to IVR10015 to put the current date in the QA Start Date 
> >> >> > field
> >> >> and
> >> >> > or
> >> >> > the QA End Date field upon insert to IVR10015.
> >> >> >
> >> >> > When I do that and add a new item I get the message below.  How can 
> >> >> > I
> >> >> tell
> >> >> > what the constraint is that is being violated?  Any other ideas on 
> >> >> > how
> >> >> > to
> >> >> > capture the actual created date?  Thanks.
> >> >> >
> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
> >> >> conflicted
> >> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. The
> >> >> conflict
> >> >> > occurred in database 'TESTC', table 'IVR10015', column 'QASTDATE'.
> >> >> >
> >> >> > -- 
> >> >> > Jim@TurboChef
> >> >> >
> >> >>
> >>
> >>
> >> 
> 
> 
> 
0
jim.harris (398)
10/23/2006 2:40:02 PM
Have you tried the following -

cast(cast(getdate() as int) as smalldatetime) ?

The only thing I can see that might be a problem is not having a space 
between the date and time when concatenating the date + time string.  In my 
date conversion section, there should be a space between the first single 
quote and the 00:00:00 part which may not be evident.  However, the double 
cast statement above should work.

Ta,
M.

"Jim@TurboChef" <jim.harris@turbochef.com> wrote in message 
news:A54EA00D-E4F7-4B00-95B7-1A364DC7D2F7@microsoft.com...
> Thanks for the help so far.  This seems to get closer, but I still hit an
> error "Syntax error converting datetime from character string."  I've 
> tried
> some different variations on using CAST or CONVERT but haven't found the
> right format yet to make it work.  Any further suggestions?
> -- 
> Jim@TurboChef
>
>
> "M. E. Houston" wrote:
>
>> This is very quick and dirty just to get a trigger to work.
>>
>> CREATE TRIGGER [trigChangeCreated] ON [dbo].[IV00101]
>> AFTER INSERT
>> AS
>> UPDATE dbo.IV00101
>> SET dbo.IV00101.CREATDDT = CAST((CONVERT(CHAR(10), GETDATE(), 102) + '
>> 00:00:00') AS DATETIME)
>> FROM inserted
>> WHERE dbo.IV00101.ITEMNMBR = inserted.ITEMNMBR
>>
>> Where the problem lies with the constraint is that the date must have 0 
>> for
>> hours, minutes, and seconds.
>>
>> I hope this helps to get you on track.
>>
>> Ta,
>> M.
>>
>> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
>> news:F8088E38-21A9-43E9-B895-17922635D840@microsoft.com...
>> > Yes, it would.  But I haven't been able to make that work either via a
>> > trigger.
>> > -- 
>> > Jim@TurboChef
>> >
>> >
>> > "M. E. Houston" wrote:
>> >
>> >> Wouldn't changing the CREATDDT in IV00101 accomplish what you want? 
>> >> (We
>> >> don't use manufacturing so I assume that copying an existing item to a
>> >> new
>> >> item will still create a record in IV00101.)
>> >>
>> >> Ta,
>> >> M.
>> >>
>> >> "Jim@TurboChef" <jim.harris@turbochef.com> wrote in message
>> >> news:EBFD3C31-DA7B-4789-BB5A-40684350F222@microsoft.com...
>> >> > Yes, but I didn't see any other date fields there except for created
>> >> > date
>> >> > and
>> >> > modified date.  There are several fields we never need, but I'm not
>> >> > sure
>> >> > how
>> >> > to store a date in them.  For example minshelflfe1 and minshelflife2
>> >> > are
>> >> > never relevant for our products, but those are integer fields.
>> >> > -- 
>> >> > Jim@TurboChef
>> >> >
>> >> >
>> >> > "David Graening {MSFT}" wrote:
>> >> >
>> >> >> Jim,
>> >> >>
>> >> >> Besides using the IVR10015 table, have you looked to see if you can
>> >> >> populate another field in the IV00101 table?  I am not sure if this
>> >> >> would
>> >> >> be easier to do this, but it is just another thought.
>> >> >>
>> >> >> Best Regards,
>> >> >>
>> >> >> David Graening
>> >> >> Microsoft Online Partner Support
>> >> >>
>> >> >>
>> >> >> --------------------
>> >> >> Thread-Topic: Trigger on IVR10015
>> >> >> thread-index: Acby7KU8rkNqxwmTT0+32/am0GuGuQ==
>> >> >> X-WBNR-Posting-Host: 207.59.211.138
>> >> >> From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> >> References:  <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> >> <BFr2rHu8GHA.4348@TK2MSFTNGXA01.phx.gbl>
>> >> >> Subject: RE: Trigger on IVR10015
>> >> >> Date: Wed, 18 Oct 2006 12:36:02 -0700
>> >> >> Lines: 86
>> >> >> Message-ID: <309F9E62-EF06-4F03-8F95-E989B3ACC844@microsoft.com>
>> >> >> MIME-Version: 1.0
>> >> >> Content-Type: text/plain;
>> >> >> charset="Utf-8"
>> >> >> Content-Transfer-Encoding: 7bit
>> >> >> X-Newsreader: Microsoft CDO for Windows 2000
>> >> >> Content-Class: urn:content-classes:message
>> >> >> Importance: normal
>> >> >> Priority: normal
>> >> >> X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> >> Newsgroups: microsoft.public.greatplains
>> >> >> Path: TK2MSFTNGXA01.phx.gbl
>> >> >> Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36804
>> >> >> NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> >> X-Tomcat-NG: microsoft.public.greatplains
>> >> >>
>> >> >> Thanks - we're using the modified date now, but as you noted that
>> >> >> picks
>> >> >> up
>> >> >> all changes so there is no consistent way to know which items are 
>> >> >> new
>> >> >> on
>> >> >> any
>> >> >> given day.  I'll try setting the QAQCNEEDED_I field as part of the
>> >> >> trigger.
>> >> >> -- 
>> >> >> Jim@TurboChef
>> >> >>
>> >> >>
>> >> >> "David Graening {MSFT}" wrote:
>> >> >>
>> >> >> > Hi Jim,
>> >> >> >
>> >> >> > Thank you for posting tot he Microsoft Great Plains Newsgroup.
>> >> >> >
>> >> >> > I understand you created a trigger to populate the QASTDATE start
>> >> >> > field
>> >> >> in
>> >> >> > the IVR10015 table.  This trigger was created to update that 
>> >> >> > table
>> >> >> > to
>> >> >> show
>> >> >> > you when an Item Number was created.  If you use the copy feature
>> >> >> > for
>> >> >> > copying item numbers, the Created Date in the IV00101 table shows
>> >> >> > the
>> >> >> date
>> >> >> > from the original item number.  This is not what you need.
>> >> >> >
>> >> >> > I am not sure what you have setup for your trigger and but it
>> >> >> > probably
>> >> >> does
>> >> >> > not matter as this field may not be able to be populated.  What 
>> >> >> > you
>> >> >> > can
>> >> >> > look at is first updating the QAQCNEEDED_I field first.  When the
>> >> >> > QAQCNEEDED_I field has a value of 1, the date field is available.
>> >> >> > If
>> >> >> > it
>> >> >> is
>> >> >> > set to 0, the date field is not available.  This may be causing 
>> >> >> > the
>> >> >> > error
>> >> >> > message you are receiving.
>> >> >> >
>> >> >> > I looked further into the IV00101 table and I was wondering if 
>> >> >> > you
>> >> >> > would
>> >> >> be
>> >> >> > able to use the MODIFDT (Modified Date) for you issue.  This 
>> >> >> > field
>> >> >> > is
>> >> >> > updated right away with the user date when the copy feature is 
>> >> >> > used.
>> >> >> > The
>> >> >> > draw back of this field is that it is updated if you change 
>> >> >> > anything
>> >> >> > on
>> >> >> the
>> >> >> > Item Number.
>> >> >> >
>> >> >> > Have a great day!
>> >> >> >
>> >> >> > Best Regards,
>> >> >> >
>> >> >> > David Graening
>> >> >> > Microsoft Online Partner Support
>> >> >> >
>> >> >> >
>> >> >> > --------------------
>> >> >> > Thread-Topic: Trigger on IVR10015
>> >> >> > thread-index: Acbx5m5fKoAsD9cFSdqBxy6KSMm5hA==
>> >> >> > X-WBNR-Posting-Host: 207.59.211.138
>> >> >> > From: =?Utf-8?B?SmltQFR1cmJvQ2hlZg==?= <jim.harris@turbochef.com>
>> >> >> > Subject: Trigger on IVR10015
>> >> >> > Date: Tue, 17 Oct 2006 05:19:02 -0700
>> >> >> > Lines: 17
>> >> >> > Message-ID: <8EEFF7D6-57D4-4A0A-A492-6A518335FD00@microsoft.com>
>> >> >> > MIME-Version: 1.0
>> >> >> > Content-Type: text/plain;
>> >> >> > charset="Utf-8"
>> >> >> > Content-Transfer-Encoding: 7bit
>> >> >> > X-Newsreader: Microsoft CDO for Windows 2000
>> >> >> > Content-Class: urn:content-classes:message
>> >> >> > Importance: normal
>> >> >> > Priority: normal
>> >> >> > X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>> >> >> > Newsgroups: microsoft.public.greatplains
>> >> >> > Path: TK2MSFTNGXA01.phx.gbl
>> >> >> > Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.greatplains:36710
>> >> >> > NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>> >> >> > X-Tomcat-NG: microsoft.public.greatplains
>> >> >> >
>> >> >> > I'm trying to find a way to capture the actual created date on 
>> >> >> > new
>> >> >> > items
>> >> >> > when
>> >> >> > the item copy function is used (when you use the copy function it
>> >> >> > stores
>> >> >> > the
>> >> >> > created date of the source item as the created date of the new
>> >> >> > item).
>> >> >> > We
>> >> >> > have manufacturing but we don't have the QA module, so I was 
>> >> >> > trying
>> >> >> > to
>> >> >> add
>> >> >> > a
>> >> >> > trigger to IVR10015 to put the current date in the QA Start Date
>> >> >> > field
>> >> >> and
>> >> >> > or
>> >> >> > the QA End Date field upon insert to IVR10015.
>> >> >> >
>> >> >> > When I do that and add a new item I get the message below.  How 
>> >> >> > can
>> >> >> > I
>> >> >> tell
>> >> >> > what the constraint is that is being violated?  Any other ideas 
>> >> >> > on
>> >> >> > how
>> >> >> > to
>> >> >> > capture the actual created date?  Thanks.
>> >> >> >
>> >> >> > [Microsoft][ODBC SQL Server Driver][SQL Server]UPDATE statement
>> >> >> conflicted
>> >> >> > with COLUMN CHECK constraint 'CK__IVR10015__QASTDA__60253D90'. 
>> >> >> > The
>> >> >> conflict
>> >> >> > occurred in database 'TESTC', table 'IVR10015', column 
>> >> >> > 'QASTDATE'.
>> >> >> >
>> >> >> > -- 
>> >> >> > Jim@TurboChef
>> >> >> >
>> >> >>
>> >>
>> >>
>> >>
>>
>>
>> 


0
10/23/2006 2:44:45 PM
Stacey,
It is imperative that you (and others who read this newsgroup) understand 
how DUOS (Dynamics User Object Store) works.
Used properly, it can be quite helpful.

for the purposes of this article, you could initialize the DUOS object, such 
as the last dex_row_id
by inserting the value into the table

INSERT  sy90000 (PropertyValue, ObjectType, Objectid, PropertyName)
Values(1, 'InventoryItem' ,  'LastItem', 'DexID')

I've noticed that you collect every code snippet, example or suggestion that 
you can find in these newsgroups.
What are you doing with it?  How much of it do you use?
Are you building a library?  If so, consider making that accessible other 
newsgroup readers.
I'm sure we could all benefit from your diligence.

Regards
HS




-- 
www.DynExtra.com
A resource for the Microsoft Dynamics Community
Featuring FAQs, File Exchange and more
Current member count: 40
--------------------------------------------
Share your knowledge.  Add your favorite questions and answers
Help add questions to this site!  We want Your input.
--------------------------------------------
"M. E. Houston" <m.e.houston@gmail.com> wrote in message 
news:O8XsR%23I9GHA.3348@TK2MSFTNGP03.phx.gbl...
> The VBA Developers Guide explains what DUOS objects are and how to use 
> them (specifically with VBA but there is no reason why a DUOS object 
> couldn't be used in some other way as HSalim demonstrated.)  The VBA 
> Developers Guids should be in the documentation folder under the Great 
> Plains install.
>
> Whether or not you can utilize a DUOS object depends on what you are 
> trying to accomplish and you won't be able to figure out whether it will 
> work for you until you understand the structure of SY90000 (the DUOS 
> table).  I recommend you find and read the DUOS section of the VBA 
> Developers Guide to see if what you're trying to do will fit into that 
> structure.
>
> Ta,
> M.
>
>
> "Stacey" <Stacey@discussions.microsoft.com> wrote in message 
> news:D7FB1869-479A-4F9A-B7C7-FF9869DBF81D@microsoft.com...
>> We dont have VBA. I was hoping HSalim will give me a way to do this 
>> without VBA
>>
>> "M. E. Houston" wrote:
>>
>>> The VBA Developers Guide explains DUOS objects.  The objects are stored 
>>> in
>>> SY90000.
>>>
>>> TA,
>>> M.
>>>
>>> "Stacey" <Stacey@discussions.microsoft.com> wrote in message
>>> news:4CADAE31-BEAB-4657-9EE7-38C81CE4D0C7@microsoft.com...
>>> > HS,
>>> > How do you create a DUOS object?
>>> > Stacey
>>>
>>>
>>>
>
> 


0
HSalim (1270)
10/24/2006 8:03:32 PM
------=_NextPart_0001_22A4F118
Content-Type: text/plain
Content-Transfer-Encoding: 7bit

Hello, 

Checking in to see if there is anything else we can do for you on this 
topic.
Please post again at your convenience and we will be here for you.

Have a great week!

 

Jan Smith 

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_22A4F118
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 Hello, 
\par 
\par Checking in to see if there is anything else we can do for you on this topic.
\par Please post again at your convenience and we will be here for you.
\par 
\par Have a great week!
\par 
\par  
\par 
\par Jan Smith 
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_22A4F118--

0
jsmit (219)
10/30/2006 9:02:35 PM
------=_NextPart_0001_329232AF
Content-Type: text/plain
Content-Transfer-Encoding: 7bit


 Hi there

Anything else we can do for you here on this query?

Please post back at your convenience and we will be here for you.
Thanks again for using the partner newsgroups!

Have a great week!


Jan Smith 

Microsoft Online Support Engineer

 

Get Secure! - www.microsoft.com/security

==============================================

PLEASE NOTE:  The partner managed newsgroups are provided 

to assist with break/fix issues and simple how to questions.   

 

We also love to hear your product feedback!  

Let us know what you think by posting 

    from the web interface: Partner Feedback 

    from your newsreader:  microsoft.private.directaccess.partnerfeedback. 

We look forward to hearing from you!

===================================================== 

When responding to posts, please "Reply to Group" via your newsreader 

so that others may learn and benefit from this issue. 

======================================================

This posting is provided "AS IS" with no warranties, and confers no rights. 

======================================================
------=_NextPart_0001_329232AF
Content-Type: text/x-rtf
Content-Transfer-Encoding: 7bit

{\rtf1\ansi\ansicpg1252\deff0\deflang1033{\fonttbl{\f0\fnil\fprq2\fcharset0 MS Sans Serif;}}
\viewkind4\uc1\pard\f0\fs20 
\par  Hi there
\par 
\par Anything else we can do for you here on this query?
\par 
\par Please post back at your convenience and we will be here for you.
\par Thanks again for using the partner newsgroups!
\par 
\par Have a great week!
\par 
\par 
\par Jan Smith 
\par 
\par Microsoft Online Support Engineer
\par 
\par  
\par 
\par Get Secure! - www.microsoft.com/security
\par 
\par ==============================================
\par 
\par PLEASE NOTE:  The partner managed newsgroups are provided 
\par 
\par to assist with break/fix issues and simple how to questions.   
\par 
\par  
\par 
\par We also love to hear your product feedback!  
\par 
\par Let us know what you think by posting 
\par 
\par     from the web interface: Partner Feedback 
\par 
\par     from your newsreader:  microsoft.private.directaccess.partnerfeedback. 
\par 
\par We look forward to hearing from you!
\par 
\par ===================================================== 
\par 
\par When responding to posts, please "Reply to Group" via your newsreader 
\par 
\par so that others may learn and benefit from this issue. 
\par 
\par ======================================================
\par 
\par This posting is provided "AS IS" with no warranties, and confers no rights. 
\par 
\par ======================================================
\par 
\par }
------=_NextPart_0001_329232AF--

0
jsmit (219)
11/2/2006 11:25:10 PM
Reply:

Similar Artilces:

Rob...Cost update trigger
I found a post that originally included a cost update trigger that Rob had created. It updates the sales price based on a new cost. I can't get the attachment anymore. Anybody out there have the trigger? Thanks, G E ...

Trigger
CREATE TABLE tblA ( Symbol varchar(50) NOT NULL, APIFormat varchar(50) NULL, DataFormat varchar(50) NULL ) I would like to do the following: If a new row is added or APIFormat is edited, and DataFormat is not being updated, I would like to set DataFormat like this: If RIGHT(APIFORMAT,1) = '#', then SET DataFormat = LEFT(APIFORMAT,LEN(APIFORMAT)-1) + '0' If right(apiformat,1) <> '#', then SET DataFormat = APIFORMAT If I do the below trigger, if I don't update DataFormat, it sets it correctly, but...

Trigger email
Is there a way that you can have a countdown and when the countdown reaches 0 for it to automaticly send out a email to a certian person? You will first have to determine how you are going to calculate your countdown. Then try the following: Dim strSubject As String Dim strAddress As String Dim strMessage As String Dim blSendEmail as Boolean strSubject = "Countdown reached zero" strMessage = "This is the message that will be the body of the email." If 'This is where you would put the countdown check value' Then ...

could not unregister BLM trigger
Hello: One of our Dynamics GP 9.0 clients is experiencing an unusual error message when exiting Report Writer and returning to GP. Although the error message is not priventing them from using Report Writer or GP, it is a point of interest. And, I would like to see what can be done to eliminate this message. The message is "Could not unregister the BLM trigger." Does anyone know what this means or how to eliminate it? I do not know, as of yet, if all workstations get this message or if even only just some users get this message. Thanks! childofthe1980s Hi I would sugg...

SQL Triggers in Headquarters
We are planning on implementing RMS and Headquarters for our multiple-store retail sales (do not have it installed yet). We are also going to have a virtual store, which will sell items from our brick-and-mortar store inventory. Without getting into too many details at this time, we have determined that the best way for us to accomplish our business needs is to have an external SQL database which will be used to keep track of brick-and-mortar store inventory and will also be used to record virtual store sales. We would then have triggers on this database and on the Headquarters databas...

Possible to trigger rules from MSN through VBA?
I understand that it is not possible to apply rules to email that comes in from MSN. In my case, my Outlook 2003 knows about two email sources, a POP3 account and an MSN account. Is it possible to write a VB macro that, upon delivery of an MSN email message, transfers that email message to the top of the POP3 email tree and have the rules execute on it from there? -- Richard Lewis Haggard www.Haggard-And-Associates.com ...

Trigger on IVR10015
I'm trying to find a way to capture the actual created date on new items when the item copy function is used (when you use the copy function it stores the created date of the source item as the created date of the new item). We have manufacturing but we don't have the QA module, so I was trying to add a trigger to IVR10015 to put the current date in the QA Start Date field and or the QA End Date field upon insert to IVR10015. When I do that and add a new item I get the message below. How can I tell what the constraint is that is being violated? Any other ideas on how to capt...

Replication Not being Triggered
Guys, I've created a new user more than 15 minutes ago and this user has not been replicated yet from the branch to my hub site. I'm testing the replication because of a few complaints of late that the replication is not happening as fast as it should. Any ideas of where I can start looking. I tested using dcdiag, repadmin and everything is ok. However, if i force the replication repadmin /replicate it works fine. I don't want to have to invoke replication everytime an object is created. Nik. Nik schrieb: > I've created a new user more than 15 minutes...

Can't get trigger to work with linked SQL Instance...
I'm trying to create a trigger on the Customer Master Table (RM00101) that will insert certain fields from a new record into another table on a seprate instance of SQL. I have linked the 2nd instance to the first from within Enterprise Manager. Whenever a new Customer record is created and saved the following SQL exception is thrown : "Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query" I have checked several sources for the cause...

About trigger
Hi. My application works on Accessa 2000. Alla my data are in SQL server, so there is an ODBC link between them. I have a table which includes for instance names of countries. Is it possible to creater some kind of trigger or something else to automatically popup a message box to each user, when someone adds a new coutnry to the database? User would not have to go and see, whether there are new countries in database, but the application tells the user when new countries are added. thanks Hannu ...

Triggers
Greetings... I'm getting more and more sophsiticated -- but its getting scarier and scarier... I'd like to have a macro run when a user changes the value in a certain cell (via a dropdown picklist). The macro hides certain lines depending upon the view chosen. How can I do this? I know marcro's need to be RUN explicitly -- yet there seems to be some sort of EVENT command. Many thanks.../Randy Randy, There are plenty of events that happen in Excel. Go to the sheet module for your sheet (double-click it in the Project Explorer to open the code window). Open the Object dropdo...

Debug Trigger
Hi Folks Trying to develop a trigger - what is the best way to debug a trigger? Any log files or query analyzer available to assist in debugging. RMS version 1.3R MSDE Database Thanks Dan Danny wrote: > Hi Folks > > Trying to develop a trigger - what is the best way to debug a trigger? Any > log files or query analyzer available to assist in debugging. I not know a tool specific to debug the trigger.. I test the code through the query analyzer and sql profiler ... cod hi Danny, the excellent tool is sql profile tells you what happen. I traced many issue and resolve u...

trigger
I want to add a trigger to one of the CRM tables to perform an action on a different database. It's on an activity so I can't just use the Workflow Manager. I am not going to change any of the CRM data with this trigger. Can anybody tell me why Microsoft would invalidate the support agreement because of this? I understand if I went ahead and changed data, but I'm just adding a trigger that is not touching the CRM data. Thanks. I think you can safely add that trigger, MS will not support on any upgrades, patches, hotfixes, or errors resuling in addition of trigger. Other tha...

SQL Trigger when Tendering
I would like to add a comment to each TransactionEntry record, which would show up on the receipt. Rather than write a DLL, I thought a SQL Trigger would be simple enough. (Yes, I know my way around SQL. :-) The trigger, itself, seems to work fine. Unfortunately, the comment doesn't show up on the receipt. It does when I add a comment directly at POS, so I know the receipt definition can handle a comment. And after Tendering, the correct value appears in the Comment field of the TransactionEntry table. But it's like the receipt (journal?) image is getting generated before the Tr...

Click ok to MessageBox in vb.net triggers form.activate in calling form
Hey guys, We're in the process of converting a vb6 app to vb.net 2008 and are having some minor issues. One of these is that it appears that the form.activate code is fired after clicking ok to any messageboxes that we display. Is this normal? Is there any way to call a messagebox in a way that won't fire the form.activate when clicking ok? We have much of our form setup code in there which we don't want run again. Sure we can code around this with flags but I thought maybe there's a way to call the msgbox with an optional param or similar? Thanks in A...

trigger on select
Hi all, I know that "trigger on select" does not exists, I'm searching something to accomplish this need: I have a table, let's say: [Some_table] [ID] int primary key [Flag] bit [Some_Field] nvarchar(40) when a user (all user) does a select on a row of that table that has [Flag]=1 the select statement does not returns the correct value for [Some_Field] column but a specific value that I specify in the "trigger on selest". How can I do this? "Andrea Caldarone" <andrea.caldarone@poste.it> wrote in message news:...

Activity workflow - assign - event triggered when follow-up task created
Hi, I have created a workflow rule under Task > Assign with an action to send an e-mail to the owner stating a task has been assigned to them. The rule works great to notify users when tasks are assigned to them, however, it seems to be additionally triggered when a user Creates a new task using the "Follow-up" (Form Assistant). In this case it causes an e-mail to go out to the individual creating the task, which is more of a nuissance than anything. Has anyone experienced this or have an alternative? Thank you, Graeme Hi, before sending an email, You could check i...

Triggers
When I launch GP V9.0, I get a message that says "Error Registering Triggers - Contact your System Administrator. This happens in all of the companies. I have searched all of the possible resources to identify a resolution of this error. Does anyone have a suggestion that I can follow to fix this error? A quick and detailed response will be most appreciated. Thanks to all in advance. -- Deesom I am assuming that you have recently upgraded to v9.00. I believe your issue is caused by having Dexterity customisations from a previous version on your system. Please look at your DYN...

Cell entry triggers Worksheet_SelectionChange event
Tested in Excel 2003 fr, Excel 2007 fr, Excel 2010 beta en. When you enter any character in a cell, the Worksheet_SelectionChange event macro is fired (the target cell is still the active cell, no move after entry). Anyone heard of it ? TIA Daniel code NOT restricted? As ALWAYS, post your code for comments. -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett1@austin.rr.com "Daniel.C" <dcolardelleZZZ@gmail.com> wrote in message news:u4fO5UraKHA.2680@TK2MSFTNGP04.phx.gbl... > Tested in Excel 2003 fr, Excel 2007 fr, Excel 2010 beta en. &...

Using ODBC in a trigger
Can someone point me in the right direction on how to connect 2 tables, in 2 different SQL Servers, with ODBC? I have no idea what the syntax would be for the connect. ON INSERT into Table1, I need to INSERT that record into Table2 on the remote SQL Server. thanks! On Aug 7, 2:54=A0pm, "shank" <sh...@tampabay.rr.com> wrote: > Can someone point me in the right direction on how to connect 2 tables, i= n 2 > different SQL Servers, with ODBC? I have no idea what the syntax would be > for the connect. > > ON INSERT into Table1, I need to INSER...

Runing two macros triggered by a button
I have the first macro set-up to start when a button is clicked, I would like bothe the macros to run when then button is clicked, any help would be great, not real up on this macro stuff yet. MACRO 1 (triggered by button) Sub nametab() Dim i As Integer On Error Resume Next For i = 1 To Sheets.Count Sheets(i).Name = Sheets(i).Range("G1").Value Next i On Error GoTo 0 End Sub WANT TO ADD THE FOLLOWING TO TRIGGER AT THE SAME TIME Sub CellInfooter() With ActiveSheet .PageSetup.CenterFooter = .Range("G1").Text End With End Su...

Insert trigger not appearing to work for bulk insert with FIRE-TRIGGERS
I have an insert trigger that works just fine when I insert one row When I perfrom a bulk insert, the trigger only seems to work on the last row in the input file. What should the trigger be doing to process each row of the inserted data? Cheers Bob What's your trigger code look like? -- Thanks Michael Coles SQL Server MVP Author, "Expert SQL Server 2008 Encryption" (http://www.apress.com/book/view/1430224649) ---------------- "thing" <someone@microsoft.com> wrote in message news:O4c4cSGeKHA.3792@TK2MSFTNGP02.phx.gbl... >I ha...

Triggering system tray app from custom button
Is there a way to trigger a program that's running in the system tray from a custom button? Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. ...

Triggers after Insert
Hi, I'm having a temporary table in SQL Server 2005, which receives the data from external Orace DB. The data from Oracle DB inserts the data once in a month time. How to create a trigger which calls the storedprocedures, after all the data is get inserted to the temporary table, not for each row insertion. On Sat, 12 Jun 2010 04:01:21 GMT, "Visha" <u60840@uwe> wrote: >Hi, > I'm having a temporary table in SQL Server 2005, which receives the data >from external Orace DB. The data from Oracle DB inserts the data once in a >month time. >...

Help with Trigger
I would really appreciate some help with a trigger I have a Soheader table and a customer table. I would like upon data entry to update the Soheader.sotypeid field with the customer.user5 field when the soheader.custid is selected. so upon entry or change of the soheader.custid I would like the Soheader.sotypeid field to get updated with the value on the customer.user5 field. where soheader.custid = customer.custid thanks very much S Commar Sammy (s_commar@hotmail.com) writes: > I would really appreciate some help with a trigger > > I have a S...