Time of Record Creation in Goldmine

Hi,

Where are these deals imported too? , Detail records?

The data is imported to various fields in Contact1 and Contact 2 tables.

The Createon field is getting populated with the correct date whilst the createat field is not recording the time.

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 -

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 ?

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

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

perhaps post where you have used substring on another field that worked.

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

And the correct entry of the one you tried with the error?

<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)

Do you know the value that is in timeDate ?, i suspect your ,12 is too long

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.

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

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!!

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

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>

Select allOpen in new window

Thanks GM Genius, will give this a whirl first thing. Thanks for your help so far.

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?

ok, will check that table and will post the error when Im back in the office,.

Thanks so far GMG

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?

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>

Select allOpen 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>

Select allOpen 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 :-)

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.

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>"

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?)

Points are your choice. if you feel I helped to come up with your solution please award accordingly.

Thank you GMGenius