Solved

Time of Record Creation in Goldmine

Posted on 2011-03-15
28
589 Views
Last Modified: 2013-11-15
Hi Guys,

I have a third party import program which imports deals into Goldmine. When doing so, the time of the record is not recorded in the normal place as per other processes which do a similar function (Import Wizard for example). Ive tried amending the import program to insert the time from a remote table which stores both date & time by using the substring function but it throws an error since the datatype of the remote table which holds this data is smalldatetime format. No source code is available for this app so this process cannot be amended.

Since Im not able to get the above process to work, I thought of doing this another way - either create a new goldmine field and insert the data from the remote table into this and query this field. Or - somewhere else in Goldmine the time of the record creation can be obtained from. Reason Im trying to do this is so I can identify deals by time of creation via a crystal report.

Thanks
Dave
0
Comment
Question by:daiwhyte
  • 14
  • 14
28 Comments
 
LVL 12

Expert Comment

by:GMGenius
ID: 35137327
Hi,

Where are these deals imported too? , Detail records?
0
 

Author Comment

by:daiwhyte
ID: 35137735
The data is imported to various fields in Contact1 and Contact 2 tables.
0
 

Author Comment

by:daiwhyte
ID: 35137742
The Createon field is getting populated with the correct date whilst the createat field is not recording the time.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35138121
Hi, if it imports and creates a new contact1 record then you can extract the date and time the record was created from the ACCOUNTNO field

if your ACCOUNTNO field is B1031542494....

B - 2010 (year)
10315 would be add 1 to year (2011) 03 Month 15 Day

The remaining is the number of seconds paste midnight on that day -
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35138186
How is the data being imported? using SQL update? if so then that will be why the createat is not being populated and is actually a data problem.

This is not supported by GoldMine at all and could cause some problems with other funtionality.

I strongly urge you to fix the import process.

What is this importer, is it a free standing application? SQL script ?
0
 

Author Comment

by:daiwhyte
ID: 35138308
The import process is written by a third party (who has since ceased trading) which utilises the GM API. No Source code is available so Im stuck with this process. There is a config file which goes with the app which allows me to map tables/fields from our remote sql server to our local goldmine server.

This is a sample of the config file which set outs the mapping of remote to local tables fields.

<GoldMindMapping>
<ColumnName>daytimeTel</ColumnName>
<GoldMineTable>CONTACT1</GoldMineTable>
<GoldMineField>phone1</GoldMineField>
</GoldMindMapping>

This particular of the config pulls a remote table called daytimeTel and inserts the data into CONTACT1.phone1 of the goldmine database.

What would be ideal is if I could do something like this (but I dont no the syntax, possibly sql?)

<GoldMindMapping>
<ColumnName>dateTime</ColumnName>
<GoldMineTable>CONTACT1</GoldMineTable>
<GoldMineField>substring(dateTime, 12,5)</GoldMineField>
</GoldMindMapping>

The remote table dateTime holds the time and date of when the app was loaded to the remote sql table yyyy-mm-dd hh:mm:ss.

Ive used the substring command in other parts of the config file but for some reason, the substring statement cannot be used on fields which is a SQL smalldatetime type and throws the following error

System.Data.SqlClient.SqlException: Argument data type smalldatetime is invalid for argument 1 of substring function.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
   at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateO
bject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
   at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
   at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
   at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables,
Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior
behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
   at DBPoler.Handler.Work() in




0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35138612
Well if this is what you actually tried i can see why its wrong

<GoldMindMapping>
<ColumnName>dateTime</ColumnName>
<GoldMineTable>CONTACT1</GoldMineTable>
<GoldMineField>substring(dateTime, 12,5)</GoldMineField>
</GoldMindMapping>

<GoldMineField>substring(dateTime, 12,5)</GoldMineField> is not a goldmine field name
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35138616
perhaps post where you have used substring on another field that worked.
0
 

Author Comment

by:daiwhyte
ID: 35138770
Ah, typo by me Im afraid but here is a sample of one of the entries

<ColumnName>substring(AddressDetailsTimeAtAddress,1,2)</ColumnName>
            <ColumnNameAlais>YearsAtAddress</ColumnNameAlais>
            <GoldMineTable>CONTACT2</GoldMineTable>
            <GoldMineField>UPRYRSATAD</GoldMineField>
          </GoldMindMapping>

I
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35139276
And the correct entry of the one you tried with the error?
0
 

Author Comment

by:daiwhyte
ID: 35140836
<ColumnName>substring(timeDate,12,5)</ColumnName>
            <ColumnNameAlais>Timeod</ColumnNameAlais>
            <GoldMineTable>CONTACT1</GoldMineTable>
            <GoldMineField>UCREATEAT</GoldMineField>
          </GoldMindMapping

and it threw this error

System.Data.SqlClient.SqlException: Argument data type smalldatetime is invalid for argument 1 of substring function.
   at System.Data.SqlClient.SqlConnection.OnError(SqlException
exception, Boolean breakConnection)
   at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException
exception, Boolean breakConnection)
   at
System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateO
bject stateObj)
   at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
   at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
   at System.Data.SqlClient.SqlDataReader.get_MetaData()
   at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader
ds, RunBehavior runBehavior, String resetOptionsString)
   at
System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean
async)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
   at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior
cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String
method)
   at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior
behavior, String method)
   at
System.Data.SqlClient.SqlCommand.ExecuteDbDataReader(CommandBehavior
behavior)
   at
System.Data.Common.DbCommand.System.Data.IDbCommand.ExecuteReader(Comman
dBehavior behavior)
   at System.Data.Common.DbDataAdapter.FillInternal(DataSet dataset, DataTable[] datatables, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable[] dataTables,
Int32 startRecord, Int32 maxRecords, IDbCommand command, CommandBehavior
behavior)
   at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35143213
Do you know the value that is in timeDate ?, i suspect your ,12 is too long
0
 

Author Comment

by:daiwhyte
ID: 35146109
in timeDate, the value is the date and time in the following format

2011-05-15 10:00:00

So the substring command should pull 10:00 but since the timeDate is a smalldatetime data type column, the substring doesnt work and throws an error.
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35146335
The value you see from an SQL query is not how the default value is when converted to a varchar

Example

2008-03-27 09:27:00 - converts to Mar 27 2008  9:27AM when you try convert(varchar,dateTime),

Substring is not allowed on the smalldatetime data type as you already worked out so you have to convert this to varchar but, when you then use the correct code

substring(cast(dateTimeas varchar ),12,5) you end up with   9:2 because its not as expected

Correcting for this gives "substring(cast(logentry as varchar ),13,10)" gives   9:27AM which is no good for inserting to the GoldMine field so the solution is simple

Try this

substring(cast(cast(dateTime as time) as varchar),0,9),

This will convert to a time data type, then to a varchar so the value would be 09:27:00.0000000 and you only want the first 8 characters upto the . which is 0 to 9

Please try it and let me know

0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 12

Expert Comment

by:GMGenius
ID: 35146361
Really need to be able to edit posts!!!! ,

"substring(cast(logentry as varchar ),13,10)"
should read
"substring(cast(dateTime as varchar ),13,10)"

I was testing on my own system!!
0
 

Author Comment

by:daiwhyte
ID: 35146863
So the entry in the config file should be like this?

<ColumnName>substring(cast(dateTime as varchar ),13,10)</ColumnName>
            <ColumnNameAlais>Timeod</ColumnNameAlais>
            <GoldMineTable>CONTACT1</GoldMineTable>
            <GoldMineField>UCREATEAT</GoldMineField>
          </GoldMindMapping

0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35148846
No

Try this

<ColumnName>substring(cast(cast(dateTime as time) as varchar),0,9)</ColumnName>
    <ColumnNameAlais>Timeod</ColumnNameAlais>
    <GoldMineTable>CONTACT1</GoldMineTable>
    <GoldMineField>UCREATEAT</GoldMineField>
</GoldMindMapping>

Open in new window

0
 

Author Comment

by:daiwhyte
ID: 35150092
Thanks GM Genius, will give this a whirl first thing. Thanks for your help so far.
0
 

Author Comment

by:daiwhyte
ID: 35155348
Alas, Im afraid no. When ive inserted that into the config file, save the config file and then try and launch the application, it fails straight away informing me there is a problem with the config file. So it must not like 1st line.

Initially, I thought it was cos we the goldmineField was UCREATEAT but the field is actually called CREATEAT so I changed that but it made no differences.

Looks like Im going to have insert the data into a newly created field and import the timeDate date into that unless there is another way GMGenius?
0
 
LVL 12

Accepted Solution

by:
GMGenius earned 500 total points
ID: 35156491
if the field was called UCREATEAT then the table should of been CONTACT2

CREATEAT is normally maintained by GoldMine but as you are createing the data via SQL queries then it wont.

as a last shot, can you post the error you get now?
0
 

Author Comment

by:daiwhyte
ID: 35176396
ok, will check that table and will post the error when Im back in the office,.

Thanks so far GMG
0
 

Author Comment

by:daiwhyte
ID: 35187865
GMG, Ive created a new table within the CONTACT2 table called UTIMEOD and im importing the data from the remote SQL table (dateTime) into this table. Im now able to identify the time of record creation using this table, a bit of a long winded way to do it but due to the limitations of the import program which has been developed by a third party - its not been possible to get this data into the correct place within goldmine (CREATEAT).

Thank you for you support GMGenius. Happy to award points but not add solution to knowledge base, without your help, I would not been able to get to this point.

What do you think, happy with awarding points?
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35188034
So to recap,

You have tried CONTACT1 - > CREATEAT
and now tried CONTACT2 - > UTIMEOD

CREATEAT in the CONTACT1 table is 5 characters long, you didnt post the error for the idea i suggested.

after checking through whats been tried , try these

<ColumnName>substring(cast(cast(dateTime as time) as varchar),0,5)</ColumnName>
    <ColumnNameAlais>Timeod</ColumnNameAlais>
    <GoldMineTable>CONTACT1</GoldMineTable>
    <GoldMineField>CREATEAT</GoldMineField>
</GoldMindMapping>

Open in new window


or


<ColumnName>substring(cast(cast(dateTime as time) as varchar),0,5)</ColumnName>
    <ColumnNameAlais>Timeod</ColumnNameAlais>
    <GoldMineTable>CONTACT2</GoldMineTable>
    <GoldMineField>UTIMEOD</GoldMineField>
</GoldMindMapping>

Open in new window


I really would like to know what error you get now, considering the substring command is now valid, really it should work, and from the original posting with the error you was getting indicates is will actually try the substring command.

Dont give up yet :-)
0
 

Author Comment

by:daiwhyte
ID: 35240172
Hi GM Genius,

No matter what I try to insert into the config file, it throws the same error "Problem with config file, please reinstall the application". The developer must of put some code into app which detects the command substring, when I try other similar commands, it just dosent like it.

Ive had to create a new table and insert the data/time into this table and query it.

Sorry GMG, but the program Im using is flexible enough to deal with the additional commands in the third party app.

0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35240457
If you are now getting "Problem with the config file" after adding the new code, and removing the new code that error goes away again then yes the app cant handle it.

However before you said you had tried the substring and the error was
System.Data.SqlClient.SqlException: Argument data type smalldatetime is invalid for argument 1 of substring function.

is this the same?

if not then there is something else broken in the XML, if you break the < > elements and they dont match up that could do it. Try a fresh config file and add my code example.

maybe you could supply the whole xml? i noticed the spelling mistake but i assume the application is coded to recognize the mistake "</GoldMindMapping>" and not "</GoldMineMapping>"



0
 

Author Comment

by:daiwhyte
ID: 35373740
GMG, Ive had it confirmed by a developer who we had in last week for something else, he informs me that the app cannot handle the additional command in the xml config file. So I think Ive created a new field and Im inserting the time into this. Im able to use this to identify what time the deals come into the db.

Im sorry we couldnt get this working using your ideas above - I think some you win, some you lose.

Happy to close (what should i do with the points?)
0
 
LVL 12

Expert Comment

by:GMGenius
ID: 35373808
Points are your choice. if you feel I helped to come up with your solution please award accordingly.
0
 

Author Closing Comment

by:daiwhyte
ID: 35392824
Thank you GMGenius
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Decide what contact details to include in email signatures with these top 10 email signature contact details DOs & DON'Ts.
These days socially coordinated efforts have turned into a critical requirement for enterprises.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now