What is the TIME1 field used for in SOP10100 and SOP30200

We are looking at how we can extract data that has the date and time that an 
order was created.  We see the date in several fields but the time is 
included.  Is there a field that has the time and date, or is there a 
setting that we need to turn on that will record the time and date?  Also, 
could the TIME1 field be used to record the date and time and if so how?

Thanks,
Eric 

0
esf19991 (3)
6/11/2009 9:21:26 PM
greatplains 29623 articles. 4 followers. Follow

2 Replies
321 Views

Similar Articles

[PageSpeed] 19

According to the SDK, the TIME1 column is the time from the Exchange Table if 
the 'Use Rates Without Adding to Table' is not marked.  Otherwise, the Time 
for this MC transaction only. 

Of course, this will only apply if you are using Multicurrency. 

IF YOU ARE NOT USING MULTICURRENCY, you could perhaps create a simple DML 
trigger on the SOP10100 table that will automatically update the TIME1 column 
with the DEX_ROW_TS value only when a new record is inserted in the table. 
After all, the DEX_ROW_TS gets updated each and everytime there is a change 
to a document.

The following should do:

IF OBJECT_ID('dbo.setSOPTime', 'TR') IS NOT NULL
    DROP TRIGGER dbo.setSOPTime;
GO
CREATE TRIGGER setSOPTime ON dbo.SOP10100 AFTER INSERT
AS
BEGIN TRANSACTION;

BEGIN TRY
    UPDATE A SET A.TIME1 = I.DEX_ROW_TS
    FROM SOP10100 A INNER JOIN inserted I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
END TRY
BEGIN CATCH
   SELECT ERROR_NUMBER() AS ErrorNumber
       , ERROR_SEVERITY() AS ErrorSeverity
       , ERROR_STATE() AS ErrorState
       , ERROR_PROCEDURE() AS ErrorProcedure
       , ERROR_LINE() AS ErrorLine
       , ERROR_MESSAGE() AS ErrorMessage;

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

IF @@TRANCOUNT > 0
   COMMIT TRANSACTION;
GO

Typically, if you have multicurrency registered (but not using 
multicurrency) the arrow next to the Currency ID field in SOP would appear 
enabled. You can see the time in that window.

Best regards,
--
MG.-
Mariano Gomez, MIS, MCP, PMP
Maximum Global Business, LLC
http://www.maximumglobalbusiness.com
The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com


"Eric" wrote:

> We are looking at how we can extract data that has the date and time that an 
> order was created.  We see the date in several fields but the time is 
> included.  Is there a field that has the time and date, or is there a 
> setting that we need to turn on that will record the time and date?  Also, 
> could the TIME1 field be used to record the date and time and if so how?
> 
> Thanks,
> Eric 
> 
0
MarianoGomez (3440)
6/11/2009 9:59:01 PM
Maybe.....!

There are a number of updates to the SOP header table that actually delete 
and re-insert the record.  Never figured out why but documented it happening 
when I wrote the Info Flow book.
-- 
Richard L. Whaley
Author / Consultant / MVP 2006-2008
Documentation for Software Users

Get our Free Tips and Tricks Newsletter and check out our books at 
http://www.AccoladePublications.com



"Mariano Gomez" wrote:

> According to the SDK, the TIME1 column is the time from the Exchange Table if 
> the 'Use Rates Without Adding to Table' is not marked.  Otherwise, the Time 
> for this MC transaction only. 
> 
> Of course, this will only apply if you are using Multicurrency. 
> 
> IF YOU ARE NOT USING MULTICURRENCY, you could perhaps create a simple DML 
> trigger on the SOP10100 table that will automatically update the TIME1 column 
> with the DEX_ROW_TS value only when a new record is inserted in the table. 
> After all, the DEX_ROW_TS gets updated each and everytime there is a change 
> to a document.
> 
> The following should do:
> 
> IF OBJECT_ID('dbo.setSOPTime', 'TR') IS NOT NULL
>     DROP TRIGGER dbo.setSOPTime;
> GO
> CREATE TRIGGER setSOPTime ON dbo.SOP10100 AFTER INSERT
> AS
> BEGIN TRANSACTION;
> 
> BEGIN TRY
>     UPDATE A SET A.TIME1 = I.DEX_ROW_TS
>     FROM SOP10100 A INNER JOIN inserted I ON (A.DEX_ROW_ID = I.DEX_ROW_ID)
> END TRY
> BEGIN CATCH
>    SELECT ERROR_NUMBER() AS ErrorNumber
>        , ERROR_SEVERITY() AS ErrorSeverity
>        , ERROR_STATE() AS ErrorState
>        , ERROR_PROCEDURE() AS ErrorProcedure
>        , ERROR_LINE() AS ErrorLine
>        , ERROR_MESSAGE() AS ErrorMessage;
> 
>    IF @@TRANCOUNT > 0
>       ROLLBACK TRANSACTION;
> END CATCH;
> 
> IF @@TRANCOUNT > 0
>    COMMIT TRANSACTION;
> GO
> 
> Typically, if you have multicurrency registered (but not using 
> multicurrency) the arrow next to the Currency ID field in SOP would appear 
> enabled. You can see the time in that window.
> 
> Best regards,
> --
> MG.-
> Mariano Gomez, MIS, MCP, PMP
> Maximum Global Business, LLC
> http://www.maximumglobalbusiness.com
> The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com
> 
> 
> "Eric" wrote:
> 
> > We are looking at how we can extract data that has the date and time that an 
> > order was created.  We see the date in several fields but the time is 
> > included.  Is there a field that has the time and date, or is there a 
> > setting that we need to turn on that will record the time and date?  Also, 
> > could the TIME1 field be used to record the date and time and if so how?
> > 
> > Thanks,
> > Eric 
> > 
0
info4071 (3004)
6/12/2009 4:54:01 AM
Reply:

Similar Artilces:

Index using a defined name array
I have defined 3 arrays using DefineNames: Boys_11_12 Boys_13_14 Boys_15_16 I then have defined a Combo dropdown with the following choices: Boys_11_12 Boys_13_14 Boys_15_16 When the user selects the Boys age group using the Combo dropdown, I then use the selection in an Index function: Index(Boys_(one of the three),...) I get an error when using the Combo dropdown, but it works fine when I type in the array name directly into the Index function. Its as if it knows the array when typed in, but not using the Combo dropdown. I created the Combo dropdown by typing in the arr...

How to set up a combo box & Should I use a query?
I keep getting confused on how to use a combo box and prevent the wrong field from being changed ... i.e. I do not want the HA# to be edited in the table. I want to create a form to edit a table (tblHANames) The table has only 2 fields (HA#, HAName) I want to use a combo box with 2 columns to locate the record to be edited. The table is in HA# sequence (key) I do not want the user to be able to change the HA# field (containing the numbers 1 to 150) ... only the names in the HAName fields. The first question was whether or not it is best to use a query? Next, how do I set up the combo box...

OnChange event and a lookup field
Can anyone help me? I created onblur event to a textbox field using object.attachEvent('onblur',myFunction);. It does what I want, but... has anyone tried to create the same event to a lookup field? Is it possible at all? The problem is the lookup fields aren't standard fields and don't re-act to same "events" since the changes are done via Javascript code. Not sure what you can do to tap into this other than modifying the existing code that implements the lookup functionality. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------...

TIME1 on SOP30200
On SOP30200 there is a Time1 column. Ours all just have 1900-01-01 00:00:00.000. Is there a way to activate this column to get the times the sop rows are entered? We are using GP9. Thanks, Jeremy Jeremy, I believe that is only used for Multi Currencies. Mike "Jeremy Harrington" wrote: > On SOP30200 there is a Time1 column. Ours all just have 1900-01-01 > 00:00:00.000. Is there a way to activate this column to get the times the sop > rows are entered? > > We are using GP9. > > Thanks, > > Jeremy So if I used a trigger to populate it, do you k...

How to set color of field?
In ObjectPal I used the 'Arrive' event to set the color of a field or a record in a child table. This allowed the color to vary from one record to the next in a datasheet view. In Access, the 'On Current' event only works in form view. It doesn't work in datasheet view. The 'Format' event is only available in Report objects. The 'Conditional' format only allows three alternatives. How can Access vary colors from one record to the next in a datasheet, or datasheet-like view? "WDSnews" <wdsnews.0640@oregoncity.com> wrot...

Re: using microsoft publisher
I'm new to making flyers. There is a template which permits you to insert 3 photos and add you own text. Once I get to this screen I can't add my info. Can someone please help me get to the next page? Which version of Publisher are you using? Which template? What steps are you doing? I'm sure someone here can help if you provide a bit more information. JoAnn "linda" <lindasivilich@hotmail.com> wrote in message news:0a5001c3d3ee$20dbd1a0$a001280a@phx.gbl... > I'm new to making flyers. There is a template which > permits you to insert 3 photos and...

Joining fields into one field dependent on value of another field
Hi, Below is a sample of data of a query which I need assistance in - Group Fertiliser ApplicationRate 1 TSP 3 2 EFB 230 3 Kies. 3 4 EFB 230 4 Kies. 3 5 TSP 3 5 Kies. 3 7 EFB 230 7 TSP 3 7 Kies. 3 >From this, I would like to create a field such that it shows eg. Group FertiliserDescription 1 TSP (3) 2 EFB (230) 3 Kies. (3) 4 EFB (230), Kies(3) 7 EFB(230),TSP(3),Kies(3) I am able to put Fertiliser and ApplicationRate together ie. Fertiliser& "(" &ApplicationRate& ")" My problem being how to add a statement saying th...

Null Field to Interupt Closing
I am using this code to check for blank fields in BeforeUpdate: If NZ(Trim(Me!Date),vbnullstring) = vbnullstring then Msgbox "PLEASE ENTER DATE",,"Missing date" Me!Date.setfocus Cancel = True Exit Sub End if It almost works, except the form goes ahead and closes anyway. The user clicks the close button and the message box pops up if the field is empty, they click OK and instead of keeping the form open and then setting focus on the empty field, it closes the form. How can I get it to interupt the close and keep the form open? Thank you in advance for any...

Problem with after update code in field that disables another field
Hi I want to be able to disable the Non Member field in a record when something is entered in the Member field. Unfortunately, the code I am usng below disables the Non Member field in *all* records, and the database has to be closed and reopened to get round this before data entry can continue. I'd be grateful if someone could show me the correction, Thanks. Private Sub Member_AfterUpdate() If Not IsNull([Member]) Then Me![Non Members].Enabled = False Else Me![Non Members].Enabled = True End If End Sub Sam, You need to put that same code in on the OnCurrent event of the form...

how do i send a message using microsoft outlook?
i cannot send a message period! i jst cant find the button that says "send" on my screen! help plz! "charles hoying" <charleshoying@discussions.microsoft.com> wrote in message news:4B0EBE32-C3F9-43EB-9292-483F2BC89EF4@microsoft.com... >i cannot send a message period! i jst cant find the button that says "send" > on my screen! help plz! Perhaps your computer is broken- it doesn't seem type capital letters. Look in the menus. "charles hoying" <charleshoying@discussions.microsoft.com> wrote in message news:4B0EBE32-C3F9-43...

Field name derived from a string value returns a Run-time error 2465.
I have two labels on a form and want to populate them during the run time of my form. The name of the labels are "1" the other is called "2" (without the quotes of course). Field 1 runs OK but two returns a run-time error 2465. Of course, the real form is much bigger. A lot of fields called 1 through 31 are populated from within a loop. To simplify my question I down sized this post to two label fields. Any one know how to solve the problem with label "2"? Any help or hint is appreciated. ' This works OK Me![1].Caption = "abc"...

Values in Pay To Field
How do I remove values from the Pay To list? Through syncing with my PDA, a number of the valkues have become corrupted and I want to remove them In microsoft.public.money, Mike McLellan wrote: >How do I remove values from the Pay To list? Through syncing with my PDA, a >number of the valkues have become corrupted and I want to remove them Go to Banking->AccountTools->Categories&Payees->Payees, right-click a Payee and select Delete the selected Payee from the drop-list. Alternatively use the Delete button at the bottom to deleted the selected Payee from the drop-list....

ReportViewer
Using ReportViewer control v9 in .aspx page - When displaying date type parameter field in my report nothing happens when calendar icon is clicked (no calendar page, no error) in ie 8. FoxFire works. Same report works fine when viewed directly on the report server in ie8. Resolved myself. Sloppy coding on the Master page. <form> tag out of place in table structure. "Pete Hay" wrote: > Using ReportViewer control v9 in .aspx page - When displaying date type > parameter field in my report nothing happens when calendar icon is clicked > (no calen...

use of check box
I inserted a check box from the forms toolbar, and now there is a check box symbol, and next to it the words "check box 5." how do I get rid of the words? Hi! Right click on the checkbox and a menu of options will appear. eg: EDIT TEXT Biff >-----Original Message----- >I inserted a check box from the forms toolbar, and now there is a check box >symbol, and next to it the words "check box 5." how do I get rid of the >words? >. > ...

Updating a field in a Table with a subform field
Hi, I have a form that has a subform. I would like to update the Table that is bound to my form with the info from the subform once the use is done entering the info on the main form. Ex. I want the stockcode field in the subform to update in the stockcode field in the main form's table. Thank You Linda On Thu, 26 Nov 2009 09:33:03 -0800, linda <linda@discussions.microsoft.com> wrote: >Hi, > >I have a form that has a subform. I would like to update the Table that is >bound to my form with the info from the subform once the use is done entering ...

Required Fields
We were planning on making 1/3 address types required: phone, email or address. Is there any way to do this? One idea : 1°) You add a picklist "Contact Method" with 3 values : 'Phone', 'Email', 'Address' 2°) You code an OnChange method which force the user to enter a value in the field 'Phone' if the value of "Contact Method" is 'Phone'... According to me, there is no other method. Good luck -- Michael MARTIN BrainSoft (France) "erinm" wrote: > We were planning on making 1/3 address types required: p...

field based on field
I feel like I've asked this before. How do I make a field based on another field in the same query? exp1=a+b exp2=val(expr1) How do I write expr2? Kou On Thu, 13 Dec 2007 09:01:02 -0800, Kou Vang wrote: > I feel like I've asked this before. How do I make a field based on another > field in the same query? > > exp1=a+b > > exp2=val(expr1) > > How do I write expr2? > > Kou Your above question can have more than one result depending upon field datatype and what you mean by using the "+". Did you want to concatenate or add? Since your...

bound unbound fields into newrecord vb2005
hi, I'am trying to make an vb2005 application.I've a connection with a accessdatabase. on a winform i've some field which were scanned and are displayed on a winform. I compare this data with the data in the database on the same form, in a dtagridview. However , when i've receive totaly new data, i've nothing to compare , meaning i have a new client. how can i add these new data which are displayed in unbound textfields into a new record in the database ? regards, Jef ...

How do i create an excel file using asp
A part of the reporting solution requires me to generate web based reports. The reports i have already made in ASP. Now the problem is that i need to export this report to excel. Is there any way i can do this? ...

find all records in date field
How do I find all records for a field "Birthdate" for particular month Use a query, and in the "Criteria" box, put this (change dates to suit, and use the normal format for your location, eg 12/25/2009 instead of 25/12/2009) Between #01/01/2009# and #25/12/2009# Phil, London "Keith" <Keith@discussions.microsoft.com> wrote in message news:7E8ECAAA-D7DA-4F65-8ED7-80C4AEE28963@microsoft.com... > How do I find all records for a field "Birthdate" for particular month SELECT * FROM YourTable WHERE Month(BirthDate) = [Enter the...

Fields in detail section of form
Hi All, In one of my applications, I have the following situation. For a certain school activity to which all children can attend, some children have to pay and some children can attend free. The background and difference between these two groups is not relevant now. In a (continuous) form, all these children are listed, sorted by class and name. Listed fields are name, class ´┐Żnd the sum to pay. My question is about this amount-field. Even when a child doesn't have to pay, this field is shown (with value 0). As you can imagine, I have the wish that the user only sees this field if ...

Can't Use Microsoft Update
When I check for updates with Office Access it doesn't send me to the office web site and it doesn't trigger Windows Update. I suggests I install Microsoft Update. But, of course, I get the following error number: Error number: 0x80070002 I am told: "The website has encountered a problem and cannot display the page you are trying to view. The options provided below might help you solve the " I try later on and the same thing. No I didn't try all the options. So there is a controversy among Microsoft products as to which update service to use? Should I us...

concatenating fields for links in queries
hey all, i would like to link a PolicyNumber field concatenated w a field called INVSUB to a PolicyNumber field in another table. can i do that or do i have to create another field and update it to [PolicyNumber] & [INVSUB]? here is the FROM section of SQL code. i would like to concatenate ALLCOMPANIESDATA.[POL#] w ALLCOMPANIESDATA.[INVSUB] "FROM ALLCOMPANIESDATA INNER JOIN qryCPRackleyRecords ON ALLCOMPANIESDATA.[POL#] = qryCPRackleyRecords.[Policy Number]" TIA Ted You can join on a calculated value, however you cannot build this type of query using the Design view...

Select Part of field
I have a field that for EVERY record has a unique text/number followed by a common suffix so 2 records would look like this: sfxhdbyrneo1234 hey7dtdbfkri503mf812n1234 How do I create a query that would copy the entire field EXCEPT the common suffix? I can also run this as an update query to just delete the common suffix. Thanks Hi Firstly, make sure you back up your table. Then, it will depend on the data type of the field you want to change, but, using the sample data you gave, ie, a text field where you want to omit the last 4 characters... update tblMyTable set MyField = left(My...

inventory transfer log field, batch number and cost
I would like to know what part of the RMS application enters data into the inventory transfer log table and also appends a batch number and cost. Via PO, Transfer, or directly changing the avail qty in item card, the table is updated, but never with a batch number or with a cost. What part of RMS updates that table inluding the batch number and cost? Jami one more thing, the transfer type i am looking at is 5. "j" <jgill@savitas.net> wrote in message news:O29JBTKiGHA.1260@TK2MSFTNGP05.phx.gbl... >I would like to know what part of the RMS application enters data in...