Smartlist builder calculated field problem

I am trying to build a smartlist with a calculated field that contains the 
number of hours and minutes until a service call expires. The user defined 1 
field is the date and time in smalldate format that shows when the call 
expires. I need to have it so that the field displays the hours / minutes 
until this time is reached.

I currently have the following in the :
DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )

this returns absolutly nothing when I add the columns to the smart list. The 
field description in great plains tells me that the user defined field is a 
string. The field typehas been set as a string in the smart list calculator 
also(i tried it with date as well and this did not work either).


Does anyone know where I am going wrong? Any help would be greatly 
appreciated.
0
Toby (34)
12/10/2008 11:07:01 AM
greatplains 29623 articles. 6 followers. Follow

9 Replies
1700 Views

Similar Articles

[PageSpeed] 48

DateDiff works only on date fields, not on strings.  You need to convert the 
data in the user def 1 to date format and use that result in the datediff, 
all in one line of code.
-- 
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



"Toby" wrote:

> I am trying to build a smartlist with a calculated field that contains the 
> number of hours and minutes until a service call expires. The user defined 1 
> field is the date and time in smalldate format that shows when the call 
> expires. I need to have it so that the field displays the hours / minutes 
> until this time is reached.
> 
> I currently have the following in the :
> DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> 
> this returns absolutly nothing when I add the columns to the smart list. The 
> field description in great plains tells me that the user defined field is a 
> string. The field typehas been set as a string in the smart list calculator 
> also(i tried it with date as well and this did not work either).
> 
> 
> Does anyone know where I am going wrong? Any help would be greatly 
> appreciated.
0
info4071 (3005)
12/10/2008 12:52:01 PM
Thanks for the help Richard.

Im having a little bit of trouble converting the data to a date instead of a 
string. the user defined1 field is a string of 20 characters long and ends up 
dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime, 
{Service Call Master:User Defined 1} , 21) to do it but it just comes up 
blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have tried 
the convert in various forms and failed each time.

Do you have any advice on where I am going wrong?

"Richard Whaley -- MVP 2006-2008" wrote:

> DateDiff works only on date fields, not on strings.  You need to convert the 
> data in the user def 1 to date format and use that result in the datediff, 
> all in one line of code.
> -- 
> 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
> 
> 
> 
> "Toby" wrote:
> 
> > I am trying to build a smartlist with a calculated field that contains the 
> > number of hours and minutes until a service call expires. The user defined 1 
> > field is the date and time in smalldate format that shows when the call 
> > expires. I need to have it so that the field displays the hours / minutes 
> > until this time is reached.
> > 
> > I currently have the following in the :
> > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> > 
> > this returns absolutly nothing when I add the columns to the smart list. The 
> > field description in great plains tells me that the user defined field is a 
> > string. The field typehas been set as a string in the smart list calculator 
> > also(i tried it with date as well and this did not work either).
> > 
> > 
> > Does anyone know where I am going wrong? Any help would be greatly 
> > appreciated.
0
Toby (34)
12/10/2008 1:30:15 PM
I Have also tried the following which was unsucsessful:

CONVERT(NVARCHAR(20), {Service Call Master:User Defined 1} , 23)

This one just brought up a load of blank fields down the entire smartlist

"Toby" wrote:

> Thanks for the help Richard.
> 
> Im having a little bit of trouble converting the data to a date instead of a 
> string. the user defined1 field is a string of 20 characters long and ends up 
> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime, 
> {Service Call Master:User Defined 1} , 21) to do it but it just comes up 
> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have tried 
> the convert in various forms and failed each time.
> 
> Do you have any advice on where I am going wrong?
> 
> "Richard Whaley -- MVP 2006-2008" wrote:
> 
> > DateDiff works only on date fields, not on strings.  You need to convert the 
> > data in the user def 1 to date format and use that result in the datediff, 
> > all in one line of code.
> > -- 
> > 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
> > 
> > 
> > 
> > "Toby" wrote:
> > 
> > > I am trying to build a smartlist with a calculated field that contains the 
> > > number of hours and minutes until a service call expires. The user defined 1 
> > > field is the date and time in smalldate format that shows when the call 
> > > expires. I need to have it so that the field displays the hours / minutes 
> > > until this time is reached.
> > > 
> > > I currently have the following in the :
> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> > > 
> > > this returns absolutly nothing when I add the columns to the smart list. The 
> > > field description in great plains tells me that the user defined field is a 
> > > string. The field typehas been set as a string in the smart list calculator 
> > > also(i tried it with date as well and this did not work either).
> > > 
> > > 
> > > Does anyone know where I am going wrong? Any help would be greatly 
> > > appreciated.
0
Toby (34)
12/10/2008 1:39:19 PM
Yes, actually create a SQL Server view. For some reason SLB is not 
interpreting this function correctly. I was able to recreate the issue and 
was able to get the CONVERT function to work directly on SQL Server.

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


"Toby" wrote:

> Thanks for the help Richard.
> 
> Im having a little bit of trouble converting the data to a date instead of a 
> string. the user defined1 field is a string of 20 characters long and ends up 
> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime, 
> {Service Call Master:User Defined 1} , 21) to do it but it just comes up 
> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have tried 
> the convert in various forms and failed each time.
> 
> Do you have any advice on where I am going wrong?
> 
> "Richard Whaley -- MVP 2006-2008" wrote:
> 
> > DateDiff works only on date fields, not on strings.  You need to convert the 
> > data in the user def 1 to date format and use that result in the datediff, 
> > all in one line of code.
> > -- 
> > 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
> > 
> > 
> > 
> > "Toby" wrote:
> > 
> > > I am trying to build a smartlist with a calculated field that contains the 
> > > number of hours and minutes until a service call expires. The user defined 1 
> > > field is the date and time in smalldate format that shows when the call 
> > > expires. I need to have it so that the field displays the hours / minutes 
> > > until this time is reached.
> > > 
> > > I currently have the following in the :
> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> > > 
> > > this returns absolutly nothing when I add the columns to the smart list. The 
> > > field description in great plains tells me that the user defined field is a 
> > > string. The field typehas been set as a string in the smart list calculator 
> > > also(i tried it with date as well and this did not work either).
> > > 
> > > 
> > > Does anyone know where I am going wrong? Any help would be greatly 
> > > appreciated.
0
MarianoGomez (3440)
12/10/2008 3:07:01 PM
Yet again, why we typically recommend just creating a view directly in SQL. 
:-)

-- 
Victoria Yudin
Dynamics GP MVP
Flexible Solutions - home of GP Reports
http://www.flex-solutions.com/gpreports.html
blog: www.victoriayudin.com


"Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
news:2079A85C-5FAF-4BAA-9B87-003CDE76515D@microsoft.com...
> Yes, actually create a SQL Server view. For some reason SLB is not
> interpreting this function correctly. I was able to recreate the issue and
> was able to get the CONVERT function to work directly on SQL Server.
>
> 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
>
>
> "Toby" wrote:
>
>> Thanks for the help Richard.
>>
>> Im having a little bit of trouble converting the data to a date instead 
>> of a
>> string. the user defined1 field is a string of 20 characters long and 
>> ends up
>> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime,
>> {Service Call Master:User Defined 1} , 21) to do it but it just comes up
>> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have 
>> tried
>> the convert in various forms and failed each time.
>>
>> Do you have any advice on where I am going wrong?
>>
>> "Richard Whaley -- MVP 2006-2008" wrote:
>>
>> > DateDiff works only on date fields, not on strings.  You need to 
>> > convert the
>> > data in the user def 1 to date format and use that result in the 
>> > datediff,
>> > all in one line of code.
>> > -- 
>> > 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
>> >
>> >
>> >
>> > "Toby" wrote:
>> >
>> > > I am trying to build a smartlist with a calculated field that 
>> > > contains the
>> > > number of hours and minutes until a service call expires. The user 
>> > > defined 1
>> > > field is the date and time in smalldate format that shows when the 
>> > > call
>> > > expires. I need to have it so that the field displays the hours / 
>> > > minutes
>> > > until this time is reached.
>> > >
>> > > I currently have the following in the :
>> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
>> > >
>> > > this returns absolutly nothing when I add the columns to the smart 
>> > > list. The
>> > > field description in great plains tells me that the user defined 
>> > > field is a
>> > > string. The field typehas been set as a string in the smart list 
>> > > calculator
>> > > also(i tried it with date as well and this did not work either).
>> > >
>> > >
>> > > Does anyone know where I am going wrong? Any help would be greatly
>> > > appreciated. 

0
victoria (3340)
12/10/2008 3:27:10 PM
I dont have anything in the field service table, so created some data based 
on available dates and times on the Fixed Asset log  [FA00199].

The following assumes that the USERDEF1 in SVC00200 is precisely in the 
format
"dd/mm/yyyyhh:mm:ss" with no spaces between the year and hours and that 
there is a leading zero present for dates having a day or month less than 
10.

The calculated field in SLB would be:

str ( datediff ( hh,

convert ( datetime, left ( USERDEF1, 10), 103 ) +

convert ( datetime, substring ( USERDEF1, 11, 8), 108 )

, getdate ()

) ) + ':'

+ right ( str (

datediff ( mi ,

convert ( datetime, left ( USERDEF1, 10), 103 ) +

convert ( datetime, substring ( USERDEF1, 11, 8), 108 )

, getdate ()

) % 60 + 100 ) , 2 )





 Replace the USERDEF1 with

{Service Call Master:User Defined 1}


How to break this down?


A. the value of the string representing a datetime stamp in USERDEF1 does 
not match any of the available formats that can be used in the SQL CONVERT 
function. Therefore you need to separate the date and the time into separate 
components and re combine.
-- date component

convert ( datetime, left ( USERDEF1, 10), 103 )

--- time component

convert ( datetime, substring ( USERDEF1, 11, 8), 108 )


B. Given these two components determine the number of hours to the present.
datediff ( hh,

convert ( datetime, left ( USERDEF1, 10), 103 ) +

convert ( datetime, substring ( USERDEF1, 11, 8), 108 )

, getdate ()

) as HH



C. Given these two components determine the number of minutes to the present 
time, but since we already have the hours, we only want minutes in a range 0 
to 59. The percent symbol acts as the modulus function

datediff ( mi ,

convert ( datetime, left ( USERDEF1, 10), 103 ) +

convert ( datetime, substring ( USERDEF1, 11, 8), 108 )

, getdate ()

) % 60



D. Convert these two components back to strings and concatenate, giving the 
result present above.



------

Robert


"Victoria [MVP]" <victoria@flex-solutions.com> wrote in message 
news:7E2487E5-0918-4A3A-8339-0ADE8121AFE8@microsoft.com...
> Yet again, why we typically recommend just creating a view directly in 
> SQL. :-)
>
> -- 
> Victoria Yudin
> Dynamics GP MVP
> Flexible Solutions - home of GP Reports
> http://www.flex-solutions.com/gpreports.html
> blog: www.victoriayudin.com
>
>
> "Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
> news:2079A85C-5FAF-4BAA-9B87-003CDE76515D@microsoft.com...
>> Yes, actually create a SQL Server view. For some reason SLB is not
>> interpreting this function correctly. I was able to recreate the issue 
>> and
>> was able to get the CONVERT function to work directly on SQL Server.
>>
>> 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
>>
>>
>> "Toby" wrote:
>>
>>> Thanks for the help Richard.
>>>
>>> Im having a little bit of trouble converting the data to a date instead 
>>> of a
>>> string. the user defined1 field is a string of 20 characters long and 
>>> ends up
>>> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime,
>>> {Service Call Master:User Defined 1} , 21) to do it but it just comes up
>>> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have 
>>> tried
>>> the convert in various forms and failed each time.
>>>
>>> Do you have any advice on where I am going wrong?
>>>
>>> "Richard Whaley -- MVP 2006-2008" wrote:
>>>
>>> > DateDiff works only on date fields, not on strings.  You need to 
>>> > convert the
>>> > data in the user def 1 to date format and use that result in the 
>>> > datediff,
>>> > all in one line of code.
>>> > -- 
>>> > 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
>>> >
>>> >
>>> >
>>> > "Toby" wrote:
>>> >
>>> > > I am trying to build a smartlist with a calculated field that 
>>> > > contains the
>>> > > number of hours and minutes until a service call expires. The user 
>>> > > defined 1
>>> > > field is the date and time in smalldate format that shows when the 
>>> > > call
>>> > > expires. I need to have it so that the field displays the hours / 
>>> > > minutes
>>> > > until this time is reached.
>>> > >
>>> > > I currently have the following in the :
>>> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
>>> > >
>>> > > this returns absolutly nothing when I add the columns to the smart 
>>> > > list. The
>>> > > field description in great plains tells me that the user defined 
>>> > > field is a
>>> > > string. The field typehas been set as a string in the smart list 
>>> > > calculator
>>> > > also(i tried it with date as well and this did not work either).
>>> > >
>>> > >
>>> > > Does anyone know where I am going wrong? Any help would be greatly
>>> > > appreciated.
> 


0
Robert
12/11/2008 8:21:07 AM
Hi Robert,

Thanks a lot for the help. The solution you have given is almost correct but 
the minutes are being displayed as fractions of 100 instead of 60. I have 
tried looking at the formula and understand the different parts but cannot 
see what is going wrong. If someone could shed some light on this it would be 
most appreciated!

Thanks

Toby



"Robert Cavill" wrote:

> I dont have anything in the field service table, so created some data based 
> on available dates and times on the Fixed Asset log  [FA00199].
> 
> The following assumes that the USERDEF1 in SVC00200 is precisely in the 
> format
> "dd/mm/yyyyhh:mm:ss" with no spaces between the year and hours and that 
> there is a leading zero present for dates having a day or month less than 
> 10.
> 
> The calculated field in SLB would be:
> 
> str ( datediff ( hh,
> 
> convert ( datetime, left ( USERDEF1, 10), 103 ) +
> 
> convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> 
> , getdate ()
> 
> ) ) + ':'
> 
> + right ( str (
> 
> datediff ( mi ,
> 
> convert ( datetime, left ( USERDEF1, 10), 103 ) +
> 
> convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> 
> , getdate ()
> 
> ) % 60 + 100 ) , 2 )
> 
> 
> 
> 
> 
>  Replace the USERDEF1 with
> 
> {Service Call Master:User Defined 1}
> 
> 
> How to break this down?
> 
> 
> A. the value of the string representing a datetime stamp in USERDEF1 does 
> not match any of the available formats that can be used in the SQL CONVERT 
> function. Therefore you need to separate the date and the time into separate 
> components and re combine.
> -- date component
> 
> convert ( datetime, left ( USERDEF1, 10), 103 )
> 
> --- time component
> 
> convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> 
> 
> B. Given these two components determine the number of hours to the present.
> datediff ( hh,
> 
> convert ( datetime, left ( USERDEF1, 10), 103 ) +
> 
> convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> 
> , getdate ()
> 
> ) as HH
> 
> 
> 
> C. Given these two components determine the number of minutes to the present 
> time, but since we already have the hours, we only want minutes in a range 0 
> to 59. The percent symbol acts as the modulus function
> 
> datediff ( mi ,
> 
> convert ( datetime, left ( USERDEF1, 10), 103 ) +
> 
> convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> 
> , getdate ()
> 
> ) % 60
> 
> 
> 
> D. Convert these two components back to strings and concatenate, giving the 
> result present above.
> 
> 
> 
> ------
> 
> Robert
> 
> 
> "Victoria [MVP]" <victoria@flex-solutions.com> wrote in message 
> news:7E2487E5-0918-4A3A-8339-0ADE8121AFE8@microsoft.com...
> > Yet again, why we typically recommend just creating a view directly in 
> > SQL. :-)
> >
> > -- 
> > Victoria Yudin
> > Dynamics GP MVP
> > Flexible Solutions - home of GP Reports
> > http://www.flex-solutions.com/gpreports.html
> > blog: www.victoriayudin.com
> >
> >
> > "Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
> > news:2079A85C-5FAF-4BAA-9B87-003CDE76515D@microsoft.com...
> >> Yes, actually create a SQL Server view. For some reason SLB is not
> >> interpreting this function correctly. I was able to recreate the issue 
> >> and
> >> was able to get the CONVERT function to work directly on SQL Server.
> >>
> >> 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
> >>
> >>
> >> "Toby" wrote:
> >>
> >>> Thanks for the help Richard.
> >>>
> >>> Im having a little bit of trouble converting the data to a date instead 
> >>> of a
> >>> string. the user defined1 field is a string of 20 characters long and 
> >>> ends up
> >>> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime,
> >>> {Service Call Master:User Defined 1} , 21) to do it but it just comes up
> >>> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have 
> >>> tried
> >>> the convert in various forms and failed each time.
> >>>
> >>> Do you have any advice on where I am going wrong?
> >>>
> >>> "Richard Whaley -- MVP 2006-2008" wrote:
> >>>
> >>> > DateDiff works only on date fields, not on strings.  You need to 
> >>> > convert the
> >>> > data in the user def 1 to date format and use that result in the 
> >>> > datediff,
> >>> > all in one line of code.
> >>> > -- 
> >>> > 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
> >>> >
> >>> >
> >>> >
> >>> > "Toby" wrote:
> >>> >
> >>> > > I am trying to build a smartlist with a calculated field that 
> >>> > > contains the
> >>> > > number of hours and minutes until a service call expires. The user 
> >>> > > defined 1
> >>> > > field is the date and time in smalldate format that shows when the 
> >>> > > call
> >>> > > expires. I need to have it so that the field displays the hours / 
> >>> > > minutes
> >>> > > until this time is reached.
> >>> > >
> >>> > > I currently have the following in the :
> >>> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> >>> > >
> >>> > > this returns absolutly nothing when I add the columns to the smart 
> >>> > > list. The
> >>> > > field description in great plains tells me that the user defined 
> >>> > > field is a
> >>> > > string. The field typehas been set as a string in the smart list 
> >>> > > calculator
> >>> > > also(i tried it with date as well and this did not work either).
> >>> > >
> >>> > >
> >>> > > Does anyone know where I am going wrong? Any help would be greatly
> >>> > > appreciated.
> > 
> 
> 
> 
0
Toby (34)
12/15/2008 12:41:00 PM
I have found out where I was going wrong, in the code there is % 60 + 100 
where it should be just % 60 for working out the minutes. As soon as that was 
changed it worked fine

Thanks for the help!

"Toby" wrote:

> Hi Robert,
> 
> Thanks a lot for the help. The solution you have given is almost correct but 
> the minutes are being displayed as fractions of 100 instead of 60. I have 
> tried looking at the formula and understand the different parts but cannot 
> see what is going wrong. If someone could shed some light on this it would be 
> most appreciated!
> 
> Thanks
> 
> Toby
> 
> 
> 
> "Robert Cavill" wrote:
> 
> > I dont have anything in the field service table, so created some data based 
> > on available dates and times on the Fixed Asset log  [FA00199].
> > 
> > The following assumes that the USERDEF1 in SVC00200 is precisely in the 
> > format
> > "dd/mm/yyyyhh:mm:ss" with no spaces between the year and hours and that 
> > there is a leading zero present for dates having a day or month less than 
> > 10.
> > 
> > The calculated field in SLB would be:
> > 
> > str ( datediff ( hh,
> > 
> > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > 
> > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > 
> > , getdate ()
> > 
> > ) ) + ':'
> > 
> > + right ( str (
> > 
> > datediff ( mi ,
> > 
> > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > 
> > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > 
> > , getdate ()
> > 
> > ) % 60 + 100 ) , 2 )
> > 
> > 
> > 
> > 
> > 
> >  Replace the USERDEF1 with
> > 
> > {Service Call Master:User Defined 1}
> > 
> > 
> > How to break this down?
> > 
> > 
> > A. the value of the string representing a datetime stamp in USERDEF1 does 
> > not match any of the available formats that can be used in the SQL CONVERT 
> > function. Therefore you need to separate the date and the time into separate 
> > components and re combine.
> > -- date component
> > 
> > convert ( datetime, left ( USERDEF1, 10), 103 )
> > 
> > --- time component
> > 
> > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > 
> > 
> > B. Given these two components determine the number of hours to the present.
> > datediff ( hh,
> > 
> > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > 
> > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > 
> > , getdate ()
> > 
> > ) as HH
> > 
> > 
> > 
> > C. Given these two components determine the number of minutes to the present 
> > time, but since we already have the hours, we only want minutes in a range 0 
> > to 59. The percent symbol acts as the modulus function
> > 
> > datediff ( mi ,
> > 
> > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > 
> > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > 
> > , getdate ()
> > 
> > ) % 60
> > 
> > 
> > 
> > D. Convert these two components back to strings and concatenate, giving the 
> > result present above.
> > 
> > 
> > 
> > ------
> > 
> > Robert
> > 
> > 
> > "Victoria [MVP]" <victoria@flex-solutions.com> wrote in message 
> > news:7E2487E5-0918-4A3A-8339-0ADE8121AFE8@microsoft.com...
> > > Yet again, why we typically recommend just creating a view directly in 
> > > SQL. :-)
> > >
> > > -- 
> > > Victoria Yudin
> > > Dynamics GP MVP
> > > Flexible Solutions - home of GP Reports
> > > http://www.flex-solutions.com/gpreports.html
> > > blog: www.victoriayudin.com
> > >
> > >
> > > "Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
> > > news:2079A85C-5FAF-4BAA-9B87-003CDE76515D@microsoft.com...
> > >> Yes, actually create a SQL Server view. For some reason SLB is not
> > >> interpreting this function correctly. I was able to recreate the issue 
> > >> and
> > >> was able to get the CONVERT function to work directly on SQL Server.
> > >>
> > >> 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
> > >>
> > >>
> > >> "Toby" wrote:
> > >>
> > >>> Thanks for the help Richard.
> > >>>
> > >>> Im having a little bit of trouble converting the data to a date instead 
> > >>> of a
> > >>> string. the user defined1 field is a string of 20 characters long and 
> > >>> ends up
> > >>> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime,
> > >>> {Service Call Master:User Defined 1} , 21) to do it but it just comes up
> > >>> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have 
> > >>> tried
> > >>> the convert in various forms and failed each time.
> > >>>
> > >>> Do you have any advice on where I am going wrong?
> > >>>
> > >>> "Richard Whaley -- MVP 2006-2008" wrote:
> > >>>
> > >>> > DateDiff works only on date fields, not on strings.  You need to 
> > >>> > convert the
> > >>> > data in the user def 1 to date format and use that result in the 
> > >>> > datediff,
> > >>> > all in one line of code.
> > >>> > -- 
> > >>> > 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
> > >>> >
> > >>> >
> > >>> >
> > >>> > "Toby" wrote:
> > >>> >
> > >>> > > I am trying to build a smartlist with a calculated field that 
> > >>> > > contains the
> > >>> > > number of hours and minutes until a service call expires. The user 
> > >>> > > defined 1
> > >>> > > field is the date and time in smalldate format that shows when the 
> > >>> > > call
> > >>> > > expires. I need to have it so that the field displays the hours / 
> > >>> > > minutes
> > >>> > > until this time is reached.
> > >>> > >
> > >>> > > I currently have the following in the :
> > >>> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> > >>> > >
> > >>> > > this returns absolutly nothing when I add the columns to the smart 
> > >>> > > list. The
> > >>> > > field description in great plains tells me that the user defined 
> > >>> > > field is a
> > >>> > > string. The field typehas been set as a string in the smart list 
> > >>> > > calculator
> > >>> > > also(i tried it with date as well and this did not work either).
> > >>> > >
> > >>> > >
> > >>> > > Does anyone know where I am going wrong? Any help would be greatly
> > >>> > > appreciated.
> > > 
> > 
> > 
> > 
0
Toby (34)
12/15/2008 3:39:01 PM
I have now changed the formula to the following:

str ( datediff ( minute,

convert ( datetime, left ( {Service Call Master:User Defined 1}, 10), 103 ) +

convert ( datetime, substring ( {Service Call Master:User Defined 1}, 11, 
8), 108 )

, getdate ()

) / 60 ) + ':'

+ right ( str (

datediff ( mi ,

convert ( datetime, left ( {Service Call Master:User Defined 1}, 10), 103 ) +

convert ( datetime, substring ( {Service Call Master:User Defined 1}, 11, 
8), 108 )

, getdate ()

 ) % 60 ) , 2 )


As the previous one was not counting an extra hour when the minutes went 
past 60.

"Toby" wrote:

> I have found out where I was going wrong, in the code there is % 60 + 100 
> where it should be just % 60 for working out the minutes. As soon as that was 
> changed it worked fine
> 
> Thanks for the help!
> 
> "Toby" wrote:
> 
> > Hi Robert,
> > 
> > Thanks a lot for the help. The solution you have given is almost correct but 
> > the minutes are being displayed as fractions of 100 instead of 60. I have 
> > tried looking at the formula and understand the different parts but cannot 
> > see what is going wrong. If someone could shed some light on this it would be 
> > most appreciated!
> > 
> > Thanks
> > 
> > Toby
> > 
> > 
> > 
> > "Robert Cavill" wrote:
> > 
> > > I dont have anything in the field service table, so created some data based 
> > > on available dates and times on the Fixed Asset log  [FA00199].
> > > 
> > > The following assumes that the USERDEF1 in SVC00200 is precisely in the 
> > > format
> > > "dd/mm/yyyyhh:mm:ss" with no spaces between the year and hours and that 
> > > there is a leading zero present for dates having a day or month less than 
> > > 10.
> > > 
> > > The calculated field in SLB would be:
> > > 
> > > str ( datediff ( hh,
> > > 
> > > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > > 
> > > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > > 
> > > , getdate ()
> > > 
> > > ) ) + ':'
> > > 
> > > + right ( str (
> > > 
> > > datediff ( mi ,
> > > 
> > > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > > 
> > > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > > 
> > > , getdate ()
> > > 
> > > ) % 60 + 100 ) , 2 )
> > > 
> > > 
> > > 
> > > 
> > > 
> > >  Replace the USERDEF1 with
> > > 
> > > {Service Call Master:User Defined 1}
> > > 
> > > 
> > > How to break this down?
> > > 
> > > 
> > > A. the value of the string representing a datetime stamp in USERDEF1 does 
> > > not match any of the available formats that can be used in the SQL CONVERT 
> > > function. Therefore you need to separate the date and the time into separate 
> > > components and re combine.
> > > -- date component
> > > 
> > > convert ( datetime, left ( USERDEF1, 10), 103 )
> > > 
> > > --- time component
> > > 
> > > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > > 
> > > 
> > > B. Given these two components determine the number of hours to the present.
> > > datediff ( hh,
> > > 
> > > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > > 
> > > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > > 
> > > , getdate ()
> > > 
> > > ) as HH
> > > 
> > > 
> > > 
> > > C. Given these two components determine the number of minutes to the present 
> > > time, but since we already have the hours, we only want minutes in a range 0 
> > > to 59. The percent symbol acts as the modulus function
> > > 
> > > datediff ( mi ,
> > > 
> > > convert ( datetime, left ( USERDEF1, 10), 103 ) +
> > > 
> > > convert ( datetime, substring ( USERDEF1, 11, 8), 108 )
> > > 
> > > , getdate ()
> > > 
> > > ) % 60
> > > 
> > > 
> > > 
> > > D. Convert these two components back to strings and concatenate, giving the 
> > > result present above.
> > > 
> > > 
> > > 
> > > ------
> > > 
> > > Robert
> > > 
> > > 
> > > "Victoria [MVP]" <victoria@flex-solutions.com> wrote in message 
> > > news:7E2487E5-0918-4A3A-8339-0ADE8121AFE8@microsoft.com...
> > > > Yet again, why we typically recommend just creating a view directly in 
> > > > SQL. :-)
> > > >
> > > > -- 
> > > > Victoria Yudin
> > > > Dynamics GP MVP
> > > > Flexible Solutions - home of GP Reports
> > > > http://www.flex-solutions.com/gpreports.html
> > > > blog: www.victoriayudin.com
> > > >
> > > >
> > > > "Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
> > > > news:2079A85C-5FAF-4BAA-9B87-003CDE76515D@microsoft.com...
> > > >> Yes, actually create a SQL Server view. For some reason SLB is not
> > > >> interpreting this function correctly. I was able to recreate the issue 
> > > >> and
> > > >> was able to get the CONVERT function to work directly on SQL Server.
> > > >>
> > > >> 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
> > > >>
> > > >>
> > > >> "Toby" wrote:
> > > >>
> > > >>> Thanks for the help Richard.
> > > >>>
> > > >>> Im having a little bit of trouble converting the data to a date instead 
> > > >>> of a
> > > >>> string. the user defined1 field is a string of 20 characters long and 
> > > >>> ends up
> > > >>> dd/mm/yyyyhh:mi:ss. Im trying to use the following: CONVERT(datetime,
> > > >>> {Service Call Master:User Defined 1} , 21) to do it but it just comes up
> > > >>> blank I need to convert to the yyyy-mm-dd hh:mi:ss.mmm format. I have 
> > > >>> tried
> > > >>> the convert in various forms and failed each time.
> > > >>>
> > > >>> Do you have any advice on where I am going wrong?
> > > >>>
> > > >>> "Richard Whaley -- MVP 2006-2008" wrote:
> > > >>>
> > > >>> > DateDiff works only on date fields, not on strings.  You need to 
> > > >>> > convert the
> > > >>> > data in the user def 1 to date format and use that result in the 
> > > >>> > datediff,
> > > >>> > all in one line of code.
> > > >>> > -- 
> > > >>> > 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
> > > >>> >
> > > >>> >
> > > >>> >
> > > >>> > "Toby" wrote:
> > > >>> >
> > > >>> > > I am trying to build a smartlist with a calculated field that 
> > > >>> > > contains the
> > > >>> > > number of hours and minutes until a service call expires. The user 
> > > >>> > > defined 1
> > > >>> > > field is the date and time in smalldate format that shows when the 
> > > >>> > > call
> > > >>> > > expires. I need to have it so that the field displays the hours / 
> > > >>> > > minutes
> > > >>> > > until this time is reached.
> > > >>> > >
> > > >>> > > I currently have the following in the :
> > > >>> > > DATEDIFF ( hh , GETDATE()  , {Service Call Master:User Defined 1}  )
> > > >>> > >
> > > >>> > > this returns absolutly nothing when I add the columns to the smart 
> > > >>> > > list. The
> > > >>> > > field description in great plains tells me that the user defined 
> > > >>> > > field is a
> > > >>> > > string. The field typehas been set as a string in the smart list 
> > > >>> > > calculator
> > > >>> > > also(i tried it with date as well and this did not work either).
> > > >>> > >
> > > >>> > >
> > > >>> > > Does anyone know where I am going wrong? Any help would be greatly
> > > >>> > > appreciated.
> > > > 
> > > 
> > > 
> > > 
0
Toby (34)
12/15/2008 5:29:02 PM
Reply:

Similar Artilces:

Creating a new field based on conditions
I have a database that tracks insurance information for our various vendors. Each insurance type has 2 fields - a requirement field (yes/no), and an effective field (some show an expiration date, some are yes/no). I have created a query that will return only the records for which insurance is required but is expired/missing. My problem is that I want to create a new field that is calculated based on the values in the other two fields in order to make the resulting report more user-friendly. For example, if GLRequired is True and GLExpiration is <Now(), I want the new field to say...

parsing a date and time field #2
I am having trouble parsing the date and time in a field. I download data from a data base and the date and time come together in one field. I want to seperate the two. The date and time comes across as the following: "2/1/2009 14:37" in the cell. When I parse it, it seperates into three columns as follows: "2/1/2009", 2:37 AM", and "PM" I can see what is going on but I would like to get two columns with one as the date and the other as the correct time. are they any ideas on how to address this? Try using the TimeValue and DateValue functions. First format ...

Payroll calculator template
What are the tax status numbers on the payroll calculator? Any suggestions on the set up of the calculator? ...

Having problem of Outlook not receiving new e-mails
I have been using Outlook for over 1 year with Comcast Broadband. Suddenly last week, I received a message that Outlook 2000 was experiencing a problem when receiving new e-mails and was going to shut down. I re-opened Outlook and it looked different and then it asked for me set up a profile and I canceled it. But after that window coming up a couple of times after it shut down, I decided to put in a new name and then I could not get to my old e-mails and it acted like a new set up. Then I really got frustrated. I was eventaully able to get back to my old e-mails and have been able t...

An Outlook 2003 Problem
I just upgraded from Outlook 97 to Outlook 2003. However, when trying to reply, forward, and compose with Outlook 2003, I got this error message: "The messaging interface has returned an unknown error. If the problem persists, restart Outlook." The problem persisted. I restarted Outlook 2003, but it didn't help. Any suggestions? Thanks. How did you upgrade Outlook 2003 from Outlook 97? Anyway If you use Hotmail or MSN mail, check MSKB info below. You receive a "The messaging interface has returned an unknown error " error message when you try to use Outlook to...

Problems Converting Data from Quicken 2001 Deluxe to MS Money
Hello, I have a relatively new Compaq Desktop (2.5 GHz Celeron with 512 MB RAM). I have a Viewsonic Pocket PC and I wanted to use it to track my financial data so I purchased Money 2003 Standard. I tried several times to convert my Qucken Data (it's a big file--I've been using Quicken since 1995). My Quicken program is Quicken 2001 Deluxe. Anyway, the MS Money program started to convert and after a few minutes said: "Your Quicken file could not be converted. Money could not convert your Quicken file. You might have run out of disk space or system memory. Try closing othe...

Exchange Server 2000 Secuirty Setup problems
Dear I have apply wrong setting to our exchange server 2000 . The wrong setting as from exchange manager . mail store , I have apply everyone send as and receive as access right, the group included remote access via offline sync. This setting once apply , all user access other mailbox without apply access right. I have try disable this setting, after all local office user no problems, but a remote offline sync laptop user does not work . The error as below: Unable to display the folder. Microsoft Outlook could not access the specified folder location. The file C:\exchange\shawnb.ost cannot b...

Smartlist Export to CSV
I've notice when I try to save a file as tab or comma delimited, it still just comes out as a regular text file with no formatting. Only thing I can do is export to excel and then save to CSV. Is there a problem with this in GP 8? Try exporting to Excel and then saving in whatever format you want. David Musgrave [MSFT] Senior Development Consultant Escalation Engineer MBS Support - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft...

Extract text from field
If you have a filed that contatins the following data:LastName, FirstNameWhere LastName has varing lengths. Can you run an update query to obtain just the LastName part of the field? If so, what would be the command?Many, many thanks in advance. On Tue, 6 Mar 2007 14:09:45 -0500, "Mary M" <none@no_email.com> wrote:>If you have a filed that contatins the following data:>>LastName, FirstName>>Where LastName has varing lengths. Can you run an update query to obtain >just the LastName part of the field? If so, what would be the command?>>Many, many thanks in...

manufacturing scrap not calculating on pick list and serial #'s no
I have entered my percentage of scrap on a MFG BOM and is seems to be calculating properly in the setup. When I release the components and add them to a pick list the scrap is not calculating. I also choose serial numbers and they do not carry thorugh and print on the pick list. Is there some setting that I have missed? -- Paula ...

CRM 4.0 Custom Report Filter Problem
I am using the Report Wizard to create a simple report. Report is using Quotes and Quote Products I have a custom field in Quote Products which is a bit field Yes-No When I use that field as a filter for report output, I get all records. The filter criteria appears to be ignored Is this an inherent problem with Report Wizard or Am I doing something wrong? Thanks. depends on your business logic and what you want to see. If you have three quotes: Quote-1 has three products, all with the custom field set to Yes Q2 has three products, two set to Yes, 1 to No Q3 has three products, all set...

Help! Problem Referencing Excel 2003 in VB.NET
I am using Visual Studio 2008 Express Edition to develop some VB.NET code that manipulates Excel files. I am developing on a Vista/Excel 2007 machine, but I need the code to work on an XP/2003 machine as well. I initially set a reference to the Microsoft Excel 12.0 Object Library and imported the Microsoft.Office.Interop.Excel namespace. This works great on the VIsta/Excel 2007 machine, but it does not work on 2003, which needs the 11.0 library. I get an error that says "Unhandled Exception . . . Could not file or assembly 'Microsoft.Office.Interop.Excel, Version 12.0.0....

Duplicate personal folders problem
Hello all, I have a problem with Outlook 2003 and I hope someone can help me resolve it I imported a PST file from Outlook XP that was on a different machine, and now I have two versions of Personal Folders in my All Mail Folders list. They both have Deleted Items, Drafts, Inbox, Junk E-mail, Outbox, Sent Items and Search Folders in. One of them has an icon of a piece of paper with a clock to the top left and a house to the top right. The other one has an icon like a stack of files (like from a filing cabinet) They are clearly referencing the same thing, as the Deleted Items both have al...

Interesting Solver problem (Solver encounters an error)
Here's an interesting problem, I wonder if anyone has any thoughts o this. Recognize that my real problem is very complex (severa intermediate calculation including some iterative steps), but th problem I'm having seems similar (conceptually anyway) to this simpl problem. Given a data set: x,y 10,3.9 8,3.2 7,2.8 6,2.2 5,1.4 4.5,0.8 4,0.01 3.8,-0.4 3.6,-1 3.5,-1.4 3.4,-1.8 3.3,-2.4 3.2,-3.2 3.1,-4.6 3.05,-6 One could look at the data and say, "that looks like the curve y=ln(x) but with a different asymptote other than the y-axis and possibly scaling factor." So we choos...

Excel Problem #3
I have win2000 with Office 2000 in the network! When i try to rename one excel file my computer make restart. This hapening not to all files,and all files there are in the same folder in the network! What hapen,and what i can do so as to work out the problem? I have all the required permissions on that network folder. No one else has any of the files open. They are not shared workbooks. ...

reading and displaying memo fields
I got an ODBC database with a memo field. Now I use a CRecordSet in VC6 to read the database with CLongBinary for the memo field. After reading 18 fields he's out of memory. Is there a way to use CString instead of CLongBinary, cause it's only text in the memo fields (I already tried, but then I get an error message that the database could not be read)? Or is there a way to read all other fields first and only read the memo field when the user choose a specific item in the database? Thank you for helping me! ...

IM error ADO field is nothing
I am trying to do an import for manual payroll checks. I have used the same set up as is used in the sample but I recieve the above error. It also says 0 integrations failed. I can't find anything in knowledgebase. Thanks for any help. Tracey D Open IM, select your integration, double-click on Mappings. Click on the Transactions collection, then click on the Options tab. You will want to make sure the Record Source option rule is set to Use Source Recordset and that the Source is set to your source query. If the above is defined properly, then you will want to make sure you a...

Reminder Time vs Due By Field
I'm using O2003. For a contact, there is the Due by Field. There is also a Reminder Time field. If you update the Due By field, it updates the Reminder Time field. However, if you update the Reminder Time field, it does not update the Due By field. By default for a contact, you have access to the Due By field. The Reminder field is avaialble, but you have to manually add it. In Tasks, it seems to work the same in that if you update the Due By field, it updates the Reminder Time field. However, if you update the Reminder Time field, it does not update the Due By field. However, you have a...

Problems with MFC list controls
I have a large list control with many inventory control items displayed within. When someone single clicks on an item, it displays information about that item in another part of the dialog. I have a three-fold problem, the master list has multiple columns in it, in order to make the selection work properly, only column 0 can be selected, is there a way to make a valid row selection if you select an item using any of the other columns? Second issue is the highlight of the selection. Only column 0 in the list control will highlight, is there a way to make the entire row highlight when an...

Next button problems
I have a next button in a form that I have criteria for. I have a date field that has to be entered before going to another record. When I click the next button I get the pop up that says click update to continue but the form displays the next record instead of staying on the current record that needs updating. Here is the code that I am using. ____________________________________________________________________________________ Private Sub Command39_Click() If IsNull([Post Called Customer]) Then MsgBox "You must click the UPDATE button to continue!!!" Cancel = True End If On Erro...

Field mapping for Appointments in filtered views
I’ve been looking at this for ages but cannot find the field for Contact for an Appointment in the filtered views. Regarding is dbo_FilteredAppointment.regardingobjectidname and is also in dbo_FilteredActivityPointer.regardingobjectidname with a foreign key of dbo_FilteredAppointment.activityid Optional seems to come from dbo_FilteredActivityParty.partyidname with a foreign key of dbo_FilteredAppointment.activityid Does any one know where the Contact for the Appointment get stored? Also a link to a ER diagram for 3.0 or a mapping resource would help me from unnecessary trawling throu...

Problem with message routing between EXCH2K and EX2K3
We are running a ver simply Exchange topology, with an Exchange 2000 single server (ServerA) acting as "dumb" Information Store. Incoming and outbound SMTP email is relayed through a Linux based SMTP host relay on the same subnet, which handles all domain lookups for mail routing. ServerA is configured to use to the Linux SMTP server as it's smart host. The domain is a native Windows 2003 AD topology. My problem is the following: I have recently added a new Exchange 2003 Server (ServerB) into our Exchange site topology. If I create a user (UserA) on ServerA and then do a...

Problem inserting cut range
I'm trying to cut a range then insert the cut range at a particular location. The code looks like this: Range("F5:G" & ActiveSheet.UsedRange.Rows.Count).Cut Range("D5").Insert(xlShiftToRight) However, when it reaches this point, I get an error saying "Cut method of Range class failed". I've tried changing the destination range so that it reads similar to the origin range, but that didn't help. Can someone help me get the syntax on this line correct? I've got several similar lines further down in the code. If I can fix thi...

Visio Printing Problem
I am trying to print a flowchart. The print view displays the complet flowchart. However, when I print the flowchart, the top half of th flowchart is omitted and the bottom half prints. Any problem solvin recommendations would be appreciated -- jqsandma ----------------------------------------------------------------------- jqsandman's Profile: http://www.officehelp.in/member.php?userid=661 View this thread: http://www.officehelp.in/showthread.php?t=136719 Posted from - http://www.officehelp.i this usually ends up as being "apply available software patches to visio" or &quo...

Easy way to copy fields?
I have made a lot of customizations to the Leads form by adding a lot of fields. Now I want to add most of those fields to the Contacts form so that when I promote a Lead the info carries forward. Is there an easy way to copy and then link these fields or do I have to manually create them all all over again as Contact fields and then link them? You need to create the exact same fields in contacts then create mappings betweek leads and contacts in the deployment manager-schema manager. Have a look at the tool and see if it makes sense. As always I recommend you test this out on your tes...