Smartlist builder SQL problem

Hi,

I have been trying to create a calculated field in the smartlist builder in 
Dynamics GP. The calculated field is supposed to show the amount of time 
until a service call runs out of SLA. The actual code for that works fine ( 
see the @SLA section of the code). But I also need it to have it so that 
before it returns a time it checks to see if there is any data in the arrival 
date and time columns, if there is data in there then it should just say "LA 
Met". Also some of the rows do not have SLAs and they return a number: 
955138:39 I want to set the calculated field to display "No SLA" for these. 
Here is the code I have been trying out( please bear in mind this is my first 
real forray into SQL):

Declare @SLA varchar(10)
Declare @ARR datetime
Declare @FIN varchar(10)
SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
Time} 

Set @SLA = 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 )


IF @SLA = ‘955138:39’
	SET @FIN = ' No SLA '
	ELSE SET @FIN = @SLA
IF @ARR =  > 0
	SET @FIN = 'SLA Met'
	ELSE SET @FIN = @SLA

SELECT @SLA



Although this just returns nothing in the smartlist.
0
Toby (34)
12/17/2008 3:45:11 PM
greatplains 29623 articles. 6 followers. Follow

11 Replies
948 Views

Similar Articles

[PageSpeed] 25

Quick question, what do you store in user defined 1?

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:

> Hi,
> 
> I have been trying to create a calculated field in the smartlist builder in 
> Dynamics GP. The calculated field is supposed to show the amount of time 
> until a service call runs out of SLA. The actual code for that works fine ( 
> see the @SLA section of the code). But I also need it to have it so that 
> before it returns a time it checks to see if there is any data in the arrival 
> date and time columns, if there is data in there then it should just say "LA 
> Met". Also some of the rows do not have SLAs and they return a number: 
> 955138:39 I want to set the calculated field to display "No SLA" for these. 
> Here is the code I have been trying out( please bear in mind this is my first 
> real forray into SQL):
> 
> Declare @SLA varchar(10)
> Declare @ARR datetime
> Declare @FIN varchar(10)
> SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> Time} 
> 
> Set @SLA = 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 )
> 
> 
> IF @SLA = ‘955138:39’
> 	SET @FIN = ' No SLA '
> 	ELSE SET @FIN = @SLA
> IF @ARR =  > 0
> 	SET @FIN = 'SLA Met'
> 	ELSE SET @FIN = @SLA
> 
> SELECT @SLA
> 
> 
> 
> Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/17/2008 5:03:06 PM
User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"

"Mariano Gomez" wrote:

> Quick question, what do you store in user defined 1?
> 
> 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:
> 
> > Hi,
> > 
> > I have been trying to create a calculated field in the smartlist builder in 
> > Dynamics GP. The calculated field is supposed to show the amount of time 
> > until a service call runs out of SLA. The actual code for that works fine ( 
> > see the @SLA section of the code). But I also need it to have it so that 
> > before it returns a time it checks to see if there is any data in the arrival 
> > date and time columns, if there is data in there then it should just say "LA 
> > Met". Also some of the rows do not have SLAs and they return a number: 
> > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > Here is the code I have been trying out( please bear in mind this is my first 
> > real forray into SQL):
> > 
> > Declare @SLA varchar(10)
> > Declare @ARR datetime
> > Declare @FIN varchar(10)
> > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > Time} 
> > 
> > Set @SLA = 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 )
> > 
> > 
> > IF @SLA = ‘955138:39’
> > 	SET @FIN = ' No SLA '
> > 	ELSE SET @FIN = @SLA
> > IF @ARR =  > 0
> > 	SET @FIN = 'SLA Met'
> > 	ELSE SET @FIN = @SLA
> > 
> > SELECT @SLA
> > 
> > 
> > 
> > Although this just returns nothing in the smartlist.
0
Toby (34)
12/17/2008 5:21:02 PM
Toby,

I was more asking what does User-Defined 1 means in business terms. What 
does this field represent in relation to the service call?

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:

> User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> 
> "Mariano Gomez" wrote:
> 
> > Quick question, what do you store in user defined 1?
> > 
> > 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:
> > 
> > > Hi,
> > > 
> > > I have been trying to create a calculated field in the smartlist builder in 
> > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > see the @SLA section of the code). But I also need it to have it so that 
> > > before it returns a time it checks to see if there is any data in the arrival 
> > > date and time columns, if there is data in there then it should just say "LA 
> > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > Here is the code I have been trying out( please bear in mind this is my first 
> > > real forray into SQL):
> > > 
> > > Declare @SLA varchar(10)
> > > Declare @ARR datetime
> > > Declare @FIN varchar(10)
> > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > Time} 
> > > 
> > > Set @SLA = 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 )
> > > 
> > > 
> > > IF @SLA = ‘955138:39’
> > > 	SET @FIN = ' No SLA '
> > > 	ELSE SET @FIN = @SLA
> > > IF @ARR =  > 0
> > > 	SET @FIN = 'SLA Met'
> > > 	ELSE SET @FIN = @SLA
> > > 
> > > SELECT @SLA
> > > 
> > > 
> > > 
> > > Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/17/2008 5:45:01 PM
Toby,

Try this formula:

select case when {Service Call Master:Arrival Date} + {Service Call 
Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
else case when ({Service Call master:User Defined 1} <> '01-01-1900 
00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
103)) else 'No SLA' end end 

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


"Mariano Gomez" wrote:

> Toby,
> 
> I was more asking what does User-Defined 1 means in business terms. What 
> does this field represent in relation to the service call?
> 
> 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:
> 
> > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > 
> > "Mariano Gomez" wrote:
> > 
> > > Quick question, what do you store in user defined 1?
> > > 
> > > 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:
> > > 
> > > > Hi,
> > > > 
> > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > date and time columns, if there is data in there then it should just say "LA 
> > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > real forray into SQL):
> > > > 
> > > > Declare @SLA varchar(10)
> > > > Declare @ARR datetime
> > > > Declare @FIN varchar(10)
> > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > Time} 
> > > > 
> > > > Set @SLA = 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 )
> > > > 
> > > > 
> > > > IF @SLA = ‘955138:39’
> > > > 	SET @FIN = ' No SLA '
> > > > 	ELSE SET @FIN = @SLA
> > > > IF @ARR =  > 0
> > > > 	SET @FIN = 'SLA Met'
> > > > 	ELSE SET @FIN = @SLA
> > > > 
> > > > SELECT @SLA
> > > > 
> > > > 
> > > > 
> > > > Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/17/2008 6:26:07 PM
The User defined field is our internal SLA when we have to have a response 
time by(the arrival date and time fields show the time when we have initially 
responded). 

The formula you gave does not work it just returns blank columns. I am 
having a hard time figuring out where my code is going wrong. Is there any 
way I can view the error messages in some way instead of the smartlist just 
returning no entries?

"Mariano Gomez" wrote:

> Toby,
> 
> Try this formula:
> 
> select case when {Service Call Master:Arrival Date} + {Service Call 
> Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
> else case when ({Service Call master:User Defined 1} <> '01-01-1900 
> 00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
> convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
> 103)) else 'No SLA' end end 
> 
> 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
> 
> 
> "Mariano Gomez" wrote:
> 
> > Toby,
> > 
> > I was more asking what does User-Defined 1 means in business terms. What 
> > does this field represent in relation to the service call?
> > 
> > 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:
> > 
> > > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > > 
> > > "Mariano Gomez" wrote:
> > > 
> > > > Quick question, what do you store in user defined 1?
> > > > 
> > > > 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:
> > > > 
> > > > > Hi,
> > > > > 
> > > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > > date and time columns, if there is data in there then it should just say "LA 
> > > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > > real forray into SQL):
> > > > > 
> > > > > Declare @SLA varchar(10)
> > > > > Declare @ARR datetime
> > > > > Declare @FIN varchar(10)
> > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > > Time} 
> > > > > 
> > > > > Set @SLA = 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 )
> > > > > 
> > > > > 
> > > > > IF @SLA = ‘955138:39’
> > > > > 	SET @FIN = ' No SLA '
> > > > > 	ELSE SET @FIN = @SLA
> > > > > IF @ARR =  > 0
> > > > > 	SET @FIN = 'SLA Met'
> > > > > 	ELSE SET @FIN = @SLA
> > > > > 
> > > > > SELECT @SLA
> > > > > 
> > > > > 
> > > > > 
> > > > > Although this just returns nothing in the smartlist.
0
Toby (34)
12/22/2008 9:41:01 AM
Toby,

I tested the formula in a SQL Server Management Studio query window. I have 
seen cases of queries working straight on SQL Server, but not in SLB. If this 
happens to be the case then you will need to create a SQL Server view rather 
than trying to implement this directly in SLB.

By the way, you will need to replace the {} field names with their 
equivalent physical name.

select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 103) 
then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') and 
(USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 103)) 
else 'No SLA' end end 

Hope this helps, 
--
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:

> The User defined field is our internal SLA when we have to have a response 
> time by(the arrival date and time fields show the time when we have initially 
> responded). 
> 
> The formula you gave does not work it just returns blank columns. I am 
> having a hard time figuring out where my code is going wrong. Is there any 
> way I can view the error messages in some way instead of the smartlist just 
> returning no entries?
> 
> "Mariano Gomez" wrote:
> 
> > Toby,
> > 
> > Try this formula:
> > 
> > select case when {Service Call Master:Arrival Date} + {Service Call 
> > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
> > else case when ({Service Call master:User Defined 1} <> '01-01-1900 
> > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
> > convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
> > 103)) else 'No SLA' end end 
> > 
> > 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
> > 
> > 
> > "Mariano Gomez" wrote:
> > 
> > > Toby,
> > > 
> > > I was more asking what does User-Defined 1 means in business terms. What 
> > > does this field represent in relation to the service call?
> > > 
> > > 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:
> > > 
> > > > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > > > 
> > > > "Mariano Gomez" wrote:
> > > > 
> > > > > Quick question, what do you store in user defined 1?
> > > > > 
> > > > > 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:
> > > > > 
> > > > > > Hi,
> > > > > > 
> > > > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > > > date and time columns, if there is data in there then it should just say "LA 
> > > > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > > > real forray into SQL):
> > > > > > 
> > > > > > Declare @SLA varchar(10)
> > > > > > Declare @ARR datetime
> > > > > > Declare @FIN varchar(10)
> > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > > > Time} 
> > > > > > 
> > > > > > Set @SLA = 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 )
> > > > > > 
> > > > > > 
> > > > > > IF @SLA = ‘955138:39’
> > > > > > 	SET @FIN = ' No SLA '
> > > > > > 	ELSE SET @FIN = @SLA
> > > > > > IF @ARR =  > 0
> > > > > > 	SET @FIN = 'SLA Met'
> > > > > > 	ELSE SET @FIN = @SLA
> > > > > > 
> > > > > > SELECT @SLA
> > > > > > 
> > > > > > 
> > > > > > 
> > > > > > Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/22/2008 2:59:02 PM
Correction!

select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 103) 
then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') and 
(USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 103)) 
else 'No SLA' end end FROM SVC00200

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


"Mariano Gomez" wrote:

> Toby,
> 
> I tested the formula in a SQL Server Management Studio query window. I have 
> seen cases of queries working straight on SQL Server, but not in SLB. If this 
> happens to be the case then you will need to create a SQL Server view rather 
> than trying to implement this directly in SLB.
> 
> By the way, you will need to replace the {} field names with their 
> equivalent physical name.
> 
> select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 103) 
> then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') and 
> (USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 103)) 
> else 'No SLA' end end 
> 
> Hope this helps, 
> --
> 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:
> 
> > The User defined field is our internal SLA when we have to have a response 
> > time by(the arrival date and time fields show the time when we have initially 
> > responded). 
> > 
> > The formula you gave does not work it just returns blank columns. I am 
> > having a hard time figuring out where my code is going wrong. Is there any 
> > way I can view the error messages in some way instead of the smartlist just 
> > returning no entries?
> > 
> > "Mariano Gomez" wrote:
> > 
> > > Toby,
> > > 
> > > Try this formula:
> > > 
> > > select case when {Service Call Master:Arrival Date} + {Service Call 
> > > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
> > > else case when ({Service Call master:User Defined 1} <> '01-01-1900 
> > > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
> > > convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
> > > 103)) else 'No SLA' end end 
> > > 
> > > 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
> > > 
> > > 
> > > "Mariano Gomez" wrote:
> > > 
> > > > Toby,
> > > > 
> > > > I was more asking what does User-Defined 1 means in business terms. What 
> > > > does this field represent in relation to the service call?
> > > > 
> > > > 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:
> > > > 
> > > > > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > > > > 
> > > > > "Mariano Gomez" wrote:
> > > > > 
> > > > > > Quick question, what do you store in user defined 1?
> > > > > > 
> > > > > > 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:
> > > > > > 
> > > > > > > Hi,
> > > > > > > 
> > > > > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > > > > date and time columns, if there is data in there then it should just say "LA 
> > > > > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > > > > real forray into SQL):
> > > > > > > 
> > > > > > > Declare @SLA varchar(10)
> > > > > > > Declare @ARR datetime
> > > > > > > Declare @FIN varchar(10)
> > > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > > > > Time} 
> > > > > > > 
> > > > > > > Set @SLA = 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 )
> > > > > > > 
> > > > > > > 
> > > > > > > IF @SLA = ‘955138:39’
> > > > > > > 	SET @FIN = ' No SLA '
> > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > IF @ARR =  > 0
> > > > > > > 	SET @FIN = 'SLA Met'
> > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > 
> > > > > > > SELECT @SLA
> > > > > > > 
> > > > > > > 
> > > > > > > 
> > > > > > > Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/22/2008 3:34:01 PM
Thanks a lot for the reply Mario.

I have recreated the smartlist as an sql table as well as trying it with 
normal GP tables and neither worked I am afraid. I do not have access to the 
SQL management studio as of yet, I was hoping that it could be done as a 
smartlist so that the entire company could see it. I am not very familiar 
with SQL unfortunately.

"Mariano Gomez" wrote:

> Toby,
> 
> I tested the formula in a SQL Server Management Studio query window. I have 
> seen cases of queries working straight on SQL Server, but not in SLB. If this 
> happens to be the case then you will need to create a SQL Server view rather 
> than trying to implement this directly in SLB.
> 
> By the way, you will need to replace the {} field names with their 
> equivalent physical name.
> 
> select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 103) 
> then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') and 
> (USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 103)) 
> else 'No SLA' end end 
> 
> Hope this helps, 
> --
> 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:
> 
> > The User defined field is our internal SLA when we have to have a response 
> > time by(the arrival date and time fields show the time when we have initially 
> > responded). 
> > 
> > The formula you gave does not work it just returns blank columns. I am 
> > having a hard time figuring out where my code is going wrong. Is there any 
> > way I can view the error messages in some way instead of the smartlist just 
> > returning no entries?
> > 
> > "Mariano Gomez" wrote:
> > 
> > > Toby,
> > > 
> > > Try this formula:
> > > 
> > > select case when {Service Call Master:Arrival Date} + {Service Call 
> > > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
> > > else case when ({Service Call master:User Defined 1} <> '01-01-1900 
> > > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
> > > convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
> > > 103)) else 'No SLA' end end 
> > > 
> > > 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
> > > 
> > > 
> > > "Mariano Gomez" wrote:
> > > 
> > > > Toby,
> > > > 
> > > > I was more asking what does User-Defined 1 means in business terms. What 
> > > > does this field represent in relation to the service call?
> > > > 
> > > > 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:
> > > > 
> > > > > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > > > > 
> > > > > "Mariano Gomez" wrote:
> > > > > 
> > > > > > Quick question, what do you store in user defined 1?
> > > > > > 
> > > > > > 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:
> > > > > > 
> > > > > > > Hi,
> > > > > > > 
> > > > > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > > > > date and time columns, if there is data in there then it should just say "LA 
> > > > > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > > > > real forray into SQL):
> > > > > > > 
> > > > > > > Declare @SLA varchar(10)
> > > > > > > Declare @ARR datetime
> > > > > > > Declare @FIN varchar(10)
> > > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > > > > Time} 
> > > > > > > 
> > > > > > > Set @SLA = 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 )
> > > > > > > 
> > > > > > > 
> > > > > > > IF @SLA = ‘955138:39’
> > > > > > > 	SET @FIN = ' No SLA '
> > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > IF @ARR =  > 0
> > > > > > > 	SET @FIN = 'SLA Met'
> > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > 
> > > > > > > SELECT @SLA
> > > > > > > 
> > > > > > > 
> > > > > > > 
> > > > > > > Although this just returns nothing in the smartlist.
0
Toby (34)
12/22/2008 4:39:03 PM
Toby,

My apologies if I was not clear. The query must be recreated as a view in 
each company database. Here is the syntax of the view:

CREATE VIEW dbo.SLAInfo AS
SELECT CALLNBR, SRVRECTYPE, case when ARRIVDTE + ARRIVTME <> 
convert(datetime, '01-01-1900', 103) then 'SLA met' else case when (USERDEF1 
<> '01-01-1900 00:00:00.000') and (USERDEF1 <> '') then convert(varchar(20), 
convert(datetime, USERDEF1, 103)) else 'No SLA' end end FROM SVC00200
GO

GRANT SELECT ON dbo.SLAInfo TO DYNGRP
GO

What I also meant was, once the view is created in SQL Server, you can then 
add it as part of the SLB you are trying to create. You will need the Service 
Call Master (SVC00200) and this view, SLAInfo.

Could you have your database admin verify the query is returning data?

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 a lot for the reply Mario.
> 
> I have recreated the smartlist as an sql table as well as trying it with 
> normal GP tables and neither worked I am afraid. I do not have access to the 
> SQL management studio as of yet, I was hoping that it could be done as a 
> smartlist so that the entire company could see it. I am not very familiar 
> with SQL unfortunately.
> 
> "Mariano Gomez" wrote:
> 
> > Toby,
> > 
> > I tested the formula in a SQL Server Management Studio query window. I have 
> > seen cases of queries working straight on SQL Server, but not in SLB. If this 
> > happens to be the case then you will need to create a SQL Server view rather 
> > than trying to implement this directly in SLB.
> > 
> > By the way, you will need to replace the {} field names with their 
> > equivalent physical name.
> > 
> > select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 103) 
> > then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') and 
> > (USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 103)) 
> > else 'No SLA' end end 
> > 
> > Hope this helps, 
> > --
> > 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:
> > 
> > > The User defined field is our internal SLA when we have to have a response 
> > > time by(the arrival date and time fields show the time when we have initially 
> > > responded). 
> > > 
> > > The formula you gave does not work it just returns blank columns. I am 
> > > having a hard time figuring out where my code is going wrong. Is there any 
> > > way I can view the error messages in some way instead of the smartlist just 
> > > returning no entries?
> > > 
> > > "Mariano Gomez" wrote:
> > > 
> > > > Toby,
> > > > 
> > > > Try this formula:
> > > > 
> > > > select case when {Service Call Master:Arrival Date} + {Service Call 
> > > > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 'SLA met' 
> > > > else case when ({Service Call master:User Defined 1} <> '01-01-1900 
> > > > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') then 
> > > > convert(varchar(20), convert(datetime, {Service Call master:User Defined 1}, 
> > > > 103)) else 'No SLA' end end 
> > > > 
> > > > 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
> > > > 
> > > > 
> > > > "Mariano Gomez" wrote:
> > > > 
> > > > > Toby,
> > > > > 
> > > > > I was more asking what does User-Defined 1 means in business terms. What 
> > > > > does this field represent in relation to the service call?
> > > > > 
> > > > > 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:
> > > > > 
> > > > > > User defined 1 is a string containing a date and time "dd/mm/yyyyhh:mm:ss"
> > > > > > 
> > > > > > "Mariano Gomez" wrote:
> > > > > > 
> > > > > > > Quick question, what do you store in user defined 1?
> > > > > > > 
> > > > > > > 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:
> > > > > > > 
> > > > > > > > Hi,
> > > > > > > > 
> > > > > > > > I have been trying to create a calculated field in the smartlist builder in 
> > > > > > > > Dynamics GP. The calculated field is supposed to show the amount of time 
> > > > > > > > until a service call runs out of SLA. The actual code for that works fine ( 
> > > > > > > > see the @SLA section of the code). But I also need it to have it so that 
> > > > > > > > before it returns a time it checks to see if there is any data in the arrival 
> > > > > > > > date and time columns, if there is data in there then it should just say "LA 
> > > > > > > > Met". Also some of the rows do not have SLAs and they return a number: 
> > > > > > > > 955138:39 I want to set the calculated field to display "No SLA" for these. 
> > > > > > > > Here is the code I have been trying out( please bear in mind this is my first 
> > > > > > > > real forray into SQL):
> > > > > > > > 
> > > > > > > > Declare @SLA varchar(10)
> > > > > > > > Declare @ARR datetime
> > > > > > > > Declare @FIN varchar(10)
> > > > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service Call Master:Arrival 
> > > > > > > > Time} 
> > > > > > > > 
> > > > > > > > Set @SLA = 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 )
> > > > > > > > 
> > > > > > > > 
> > > > > > > > IF @SLA = ‘955138:39’
> > > > > > > > 	SET @FIN = ' No SLA '
> > > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > > IF @ARR =  > 0
> > > > > > > > 	SET @FIN = 'SLA Met'
> > > > > > > > 	ELSE SET @FIN = @SLA
> > > > > > > > 
> > > > > > > > SELECT @SLA
> > > > > > > > 
> > > > > > > > 
> > > > > > > > 
> > > > > > > > Although this just returns nothing in the smartlist.
0
MarianoGomez (3440)
12/22/2008 5:10:01 PM
Hello Toby,

Give the following code into your SLB calculation a go....

first level CASE determines if there is a datetime for the SLA.
If not, give ' NO SLA'
If so, second level CASE determines if an arrival datetime is present
   If so, returns 'SLA Met'
   if Not, determines the elapse time to the 'SLA target'





case when ( {Service Call Meter:User Defined 1} = ''

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

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

convert ( datetime, '', 121 ) ) )

then ' NO SLA '

else

/* begin -- has SLA been met or still time to elapse */

case when

not ( {Service Call Meter:Arrival Date} = '' )

or ( convert ( datetime, left ( {Service Call Master::Arrival Date}, 10), 
103 ) +

convert ( datetime, substring ( {Service Call Master:Arrival Time}, 11, 8 ) 
, 108 ) =

convert ( datetime, '', 121 ) )

then ' SLA Met ' /* Arrival datetime is available */

else

/* insert calculation for elapsed time */

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 )

end /* as ARR */

/* end -- has SLA been met or still time to elapse */

end /* as SLA */



------
Robert









"Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
news:6C137FF7-C48E-4E07-82D8-BC41B91B70C8@microsoft.com...
> Toby,
>
> My apologies if I was not clear. The query must be recreated as a view in
> each company database. Here is the syntax of the view:
>
> CREATE VIEW dbo.SLAInfo AS
> SELECT CALLNBR, SRVRECTYPE, case when ARRIVDTE + ARRIVTME <>
> convert(datetime, '01-01-1900', 103) then 'SLA met' else case when 
> (USERDEF1
> <> '01-01-1900 00:00:00.000') and (USERDEF1 <> '') then 
> convert(varchar(20),
> convert(datetime, USERDEF1, 103)) else 'No SLA' end end FROM SVC00200
> GO
>
> GRANT SELECT ON dbo.SLAInfo TO DYNGRP
> GO
>
> What I also meant was, once the view is created in SQL Server, you can 
> then
> add it as part of the SLB you are trying to create. You will need the 
> Service
> Call Master (SVC00200) and this view, SLAInfo.
>
> Could you have your database admin verify the query is returning data?
>
> 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 a lot for the reply Mario.
>>
>> I have recreated the smartlist as an sql table as well as trying it with
>> normal GP tables and neither worked I am afraid. I do not have access to 
>> the
>> SQL management studio as of yet, I was hoping that it could be done as a
>> smartlist so that the entire company could see it. I am not very familiar
>> with SQL unfortunately.
>>
>> "Mariano Gomez" wrote:
>>
>> > Toby,
>> >
>> > I tested the formula in a SQL Server Management Studio query window. I 
>> > have
>> > seen cases of queries working straight on SQL Server, but not in SLB. 
>> > If this
>> > happens to be the case then you will need to create a SQL Server view 
>> > rather
>> > than trying to implement this directly in SLB.
>> >
>> > By the way, you will need to replace the {} field names with their
>> > equivalent physical name.
>> >
>> > select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 
>> > 103)
>> > then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') 
>> > and
>> > (USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 
>> > 103))
>> > else 'No SLA' end end
>> >
>> > Hope this helps,
>> > --
>> > 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:
>> >
>> > > The User defined field is our internal SLA when we have to have a 
>> > > response
>> > > time by(the arrival date and time fields show the time when we have 
>> > > initially
>> > > responded).
>> > >
>> > > The formula you gave does not work it just returns blank columns. I 
>> > > am
>> > > having a hard time figuring out where my code is going wrong. Is 
>> > > there any
>> > > way I can view the error messages in some way instead of the 
>> > > smartlist just
>> > > returning no entries?
>> > >
>> > > "Mariano Gomez" wrote:
>> > >
>> > > > Toby,
>> > > >
>> > > > Try this formula:
>> > > >
>> > > > select case when {Service Call Master:Arrival Date} + {Service Call
>> > > > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 
>> > > > 'SLA met'
>> > > > else case when ({Service Call master:User Defined 1} <> '01-01-1900
>> > > > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') 
>> > > > then
>> > > > convert(varchar(20), convert(datetime, {Service Call master:User 
>> > > > Defined 1},
>> > > > 103)) else 'No SLA' end end
>> > > >
>> > > > 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
>> > > >
>> > > >
>> > > > "Mariano Gomez" wrote:
>> > > >
>> > > > > Toby,
>> > > > >
>> > > > > I was more asking what does User-Defined 1 means in business 
>> > > > > terms. What
>> > > > > does this field represent in relation to the service call?
>> > > > >
>> > > > > 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:
>> > > > >
>> > > > > > User defined 1 is a string containing a date and time 
>> > > > > > "dd/mm/yyyyhh:mm:ss"
>> > > > > >
>> > > > > > "Mariano Gomez" wrote:
>> > > > > >
>> > > > > > > Quick question, what do you store in user defined 1?
>> > > > > > >
>> > > > > > > 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:
>> > > > > > >
>> > > > > > > > Hi,
>> > > > > > > >
>> > > > > > > > I have been trying to create a calculated field in the 
>> > > > > > > > smartlist builder in
>> > > > > > > > Dynamics GP. The calculated field is supposed to show the 
>> > > > > > > > amount of time
>> > > > > > > > until a service call runs out of SLA. The actual code for 
>> > > > > > > > that works fine (
>> > > > > > > > see the @SLA section of the code). But I also need it to 
>> > > > > > > > have it so that
>> > > > > > > > before it returns a time it checks to see if there is any 
>> > > > > > > > data in the arrival
>> > > > > > > > date and time columns, if there is data in there then it 
>> > > > > > > > should just say "LA
>> > > > > > > > Met". Also some of the rows do not have SLAs and they 
>> > > > > > > > return a number:
>> > > > > > > > 955138:39 I want to set the calculated field to display "No 
>> > > > > > > > SLA" for these.
>> > > > > > > > Here is the code I have been trying out( please bear in 
>> > > > > > > > mind this is my first
>> > > > > > > > real forray into SQL):
>> > > > > > > >
>> > > > > > > > Declare @SLA varchar(10)
>> > > > > > > > Declare @ARR datetime
>> > > > > > > > Declare @FIN varchar(10)
>> > > > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service 
>> > > > > > > > Call Master:Arrival
>> > > > > > > > Time}
>> > > > > > > >
>> > > > > > > > Set @SLA = 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 )
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > IF @SLA = '955138:39'
>> > > > > > > > SET @FIN = ' No SLA '
>> > > > > > > > ELSE SET @FIN = @SLA
>> > > > > > > > IF @ARR =  > 0
>> > > > > > > > SET @FIN = 'SLA Met'
>> > > > > > > > ELSE SET @FIN = @SLA
>> > > > > > > >
>> > > > > > > > SELECT @SLA
>> > > > > > > >
>> > > > > > > >
>> > > > > > > >
>> > > > > > > > Although this just returns nothing in the smartlist. 


0
Robert
12/23/2008 8:29:34 AM
Thank you very much  for the replies!

Mariano, I will ask my database admin to do this in the new year.

Robert, I tried this code in the SLB and it did not work unfortunatley. Its 
the same with trying my code, I cannot see where I am going wrong as the SLB 
does not tell you it either works or returns no results.  Unfortunatley its 
much harder than VBA where I can see exactly which line of code is wrong, its 
getting quite frustrating for me now!

"Robert Cavill" wrote:

> Hello Toby,
> 
> Give the following code into your SLB calculation a go....
> 
> first level CASE determines if there is a datetime for the SLA.
> If not, give ' NO SLA'
> If so, second level CASE determines if an arrival datetime is present
>    If so, returns 'SLA Met'
>    if Not, determines the elapse time to the 'SLA target'
> 
> 
> 
> 
> 
> case when ( {Service Call Meter:User Defined 1} = ''
> 
> or ( convert ( datetime, left ( {Service Call Master:User Defined 1}, 10), 
> 103 ) +
> 
> convert ( datetime, substring ( {Service Call Master:User Defined 1}, 11, 
> 8 ) , 108 ) =
> 
> convert ( datetime, '', 121 ) ) )
> 
> then ' NO SLA '
> 
> else
> 
> /* begin -- has SLA been met or still time to elapse */
> 
> case when
> 
> not ( {Service Call Meter:Arrival Date} = '' )
> 
> or ( convert ( datetime, left ( {Service Call Master::Arrival Date}, 10), 
> 103 ) +
> 
> convert ( datetime, substring ( {Service Call Master:Arrival Time}, 11, 8 ) 
> , 108 ) =
> 
> convert ( datetime, '', 121 ) )
> 
> then ' SLA Met ' /* Arrival datetime is available */
> 
> else
> 
> /* insert calculation for elapsed time */
> 
> 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 )
> 
> end /* as ARR */
> 
> /* end -- has SLA been met or still time to elapse */
> 
> end /* as SLA */
> 
> 
> 
> ------
> Robert
> 
> 
> 
> 
> 
> 
> 
> 
> 
> "Mariano Gomez" <MarianoGomez@discussions.microsoft.com> wrote in message 
> news:6C137FF7-C48E-4E07-82D8-BC41B91B70C8@microsoft.com...
> > Toby,
> >
> > My apologies if I was not clear. The query must be recreated as a view in
> > each company database. Here is the syntax of the view:
> >
> > CREATE VIEW dbo.SLAInfo AS
> > SELECT CALLNBR, SRVRECTYPE, case when ARRIVDTE + ARRIVTME <>
> > convert(datetime, '01-01-1900', 103) then 'SLA met' else case when 
> > (USERDEF1
> > <> '01-01-1900 00:00:00.000') and (USERDEF1 <> '') then 
> > convert(varchar(20),
> > convert(datetime, USERDEF1, 103)) else 'No SLA' end end FROM SVC00200
> > GO
> >
> > GRANT SELECT ON dbo.SLAInfo TO DYNGRP
> > GO
> >
> > What I also meant was, once the view is created in SQL Server, you can 
> > then
> > add it as part of the SLB you are trying to create. You will need the 
> > Service
> > Call Master (SVC00200) and this view, SLAInfo.
> >
> > Could you have your database admin verify the query is returning data?
> >
> > 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 a lot for the reply Mario.
> >>
> >> I have recreated the smartlist as an sql table as well as trying it with
> >> normal GP tables and neither worked I am afraid. I do not have access to 
> >> the
> >> SQL management studio as of yet, I was hoping that it could be done as a
> >> smartlist so that the entire company could see it. I am not very familiar
> >> with SQL unfortunately.
> >>
> >> "Mariano Gomez" wrote:
> >>
> >> > Toby,
> >> >
> >> > I tested the formula in a SQL Server Management Studio query window. I 
> >> > have
> >> > seen cases of queries working straight on SQL Server, but not in SLB. 
> >> > If this
> >> > happens to be the case then you will need to create a SQL Server view 
> >> > rather
> >> > than trying to implement this directly in SLB.
> >> >
> >> > By the way, you will need to replace the {} field names with their
> >> > equivalent physical name.
> >> >
> >> > select case when ARRIVDTE + ARRIVTME <> convert(datetime, '01-01-1900', 
> >> > 103)
> >> > then 'SLA met' else case when (USERDEF1 <> '01-01-1900 00:00:00.000') 
> >> > and
> >> > (USERDEF1 <> '') then convert(varchar(20), convert(datetime, USERDEF1, 
> >> > 103))
> >> > else 'No SLA' end end
> >> >
> >> > Hope this helps,
> >> > --
> >> > 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:
> >> >
> >> > > The User defined field is our internal SLA when we have to have a 
> >> > > response
> >> > > time by(the arrival date and time fields show the time when we have 
> >> > > initially
> >> > > responded).
> >> > >
> >> > > The formula you gave does not work it just returns blank columns. I 
> >> > > am
> >> > > having a hard time figuring out where my code is going wrong. Is 
> >> > > there any
> >> > > way I can view the error messages in some way instead of the 
> >> > > smartlist just
> >> > > returning no entries?
> >> > >
> >> > > "Mariano Gomez" wrote:
> >> > >
> >> > > > Toby,
> >> > > >
> >> > > > Try this formula:
> >> > > >
> >> > > > select case when {Service Call Master:Arrival Date} + {Service Call
> >> > > > Master:Arrival Time} <> convert(datetime, '01-01-1900', 103) then 
> >> > > > 'SLA met'
> >> > > > else case when ({Service Call master:User Defined 1} <> '01-01-1900
> >> > > > 00:00:00.000') and ({Service Call master:User Defined 1} <> '') 
> >> > > > then
> >> > > > convert(varchar(20), convert(datetime, {Service Call master:User 
> >> > > > Defined 1},
> >> > > > 103)) else 'No SLA' end end
> >> > > >
> >> > > > 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
> >> > > >
> >> > > >
> >> > > > "Mariano Gomez" wrote:
> >> > > >
> >> > > > > Toby,
> >> > > > >
> >> > > > > I was more asking what does User-Defined 1 means in business 
> >> > > > > terms. What
> >> > > > > does this field represent in relation to the service call?
> >> > > > >
> >> > > > > 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:
> >> > > > >
> >> > > > > > User defined 1 is a string containing a date and time 
> >> > > > > > "dd/mm/yyyyhh:mm:ss"
> >> > > > > >
> >> > > > > > "Mariano Gomez" wrote:
> >> > > > > >
> >> > > > > > > Quick question, what do you store in user defined 1?
> >> > > > > > >
> >> > > > > > > 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:
> >> > > > > > >
> >> > > > > > > > Hi,
> >> > > > > > > >
> >> > > > > > > > I have been trying to create a calculated field in the 
> >> > > > > > > > smartlist builder in
> >> > > > > > > > Dynamics GP. The calculated field is supposed to show the 
> >> > > > > > > > amount of time
> >> > > > > > > > until a service call runs out of SLA. The actual code for 
> >> > > > > > > > that works fine (
> >> > > > > > > > see the @SLA section of the code). But I also need it to 
> >> > > > > > > > have it so that
> >> > > > > > > > before it returns a time it checks to see if there is any 
> >> > > > > > > > data in the arrival
> >> > > > > > > > date and time columns, if there is data in there then it 
> >> > > > > > > > should just say "LA
> >> > > > > > > > Met". Also some of the rows do not have SLAs and they 
> >> > > > > > > > return a number:
> >> > > > > > > > 955138:39 I want to set the calculated field to display "No 
> >> > > > > > > > SLA" for these.
> >> > > > > > > > Here is the code I have been trying out( please bear in 
> >> > > > > > > > mind this is my first
> >> > > > > > > > real forray into SQL):
> >> > > > > > > >
> >> > > > > > > > Declare @SLA varchar(10)
> >> > > > > > > > Declare @ARR datetime
> >> > > > > > > > Declare @FIN varchar(10)
> >> > > > > > > > SET @ARR = {Service Call Master:Arrival Date} + {Service 
> >> > > > > > > > Call Master:Arrival
> >> > > > > > > > Time}
> >> > > > > > > >
> >> > > > > > > > Set @SLA = 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 ,
> >> > > > > > > >
0
Toby (34)
12/24/2008 9:50:03 AM
Reply:

Similar Artilces:

Problem with CRM Mobile
I installed CRM mobile and since then a lot of my services will not start. Especially the world wide publishing service. I am logged in as the administrator but I keep getting an error 1068. help! ...

Analytical Accounting AAG10000 file problem
For some reason, there are records in our AAG10000 work file for journal entries that have been posted. As a result, if we try and go into adjust the AA information (transactions >> financial >> Analytical Accounting >> edit analysis) and put in one of these journal entries, we are given an error: [Microsoft][ODBC Sql Server Driver][SQL Server]Cannot insert duplicate key row in object 'AAG10000' with unique index 'AK2AAG10000'. My questions are 1) Why did this happen in the first place, and 2) is there any type of quick reconciliation to remove these records...

Dlookup Problem Text VS Number type
Hello all- I have a form (in access) that has a section of code which fills in certain cells for an excel spread sheet. Code has worked perfectly for a while, but recently I changed the table's 'OrderNumber' field from a Number type to a Text type. Now my Dlookup doesn't work. I tried changing it back, and it works again. Why does it matter? I need/would really like it to be a text type field. All help/explinations would help a lot. Thanks! Zach Zach - Did you also update the DLookup statement? A text field requires the single-quote delimiter arou...

Drop down List problems
I created a drop down list using the data validation tool. When I view my list, the little scroll bar on the list starts about 3/4 of the way down the list instead of starting at the top of the list. (If this is a poor description of the problem, I am very sorry!) How do I get the little scroll bar to start at the top? The list is sorted alphabetically and it is a pain to start in the "T"s instead of the "A"s. Any help is much appreciated...I have exhausted all my excel knowledge already! You have likely selected blank cells as part of your validation range. If you ...

External contacts problem
I have recently migrated to exch2k3 ent sp1 on 2003 sp1 server. I created external contacts, used custom and assigned an external smtp email address. People within the org receive an error when sending to dist list with external contacts in them (only external contacts report a delivery error). The e-mail account does not exist at the organization this message was sent to. Check the e-mail address, or contact the recipient directly to find out the correct address. <xyz.abc.local #5.1.1> I have verified the email address is correct. I will intermitantly receive this erro...

RECT Structure problem
Hi all, I am working on 16 bit app on windows. My problem is I am using windows RECT structure. on 16 bit windows it 's all member are declared as short which allows max 32767 max value. and i require more valus to be stored for the Rect. So i created my own rect structure with long members. but when i do this call windows API GetWindowRect() fails. it does fill the rect structure with appropriate values. can any one help. Thanks in advance. You can't do GetWindowRect in Win16 with your own custom structure. You have to use the RECT structure defined by the OS interface. R...

Problem with Access 2003
Hi, I recently re-installed Access 2003 after installing a new hard drive. I re-installed Access & the data files. Now, on certain forms the cursor will flash & the status bar says "Calculating" continually. I know that I must be missing some references or something. But nothing shows up in the module window when I check for references. Anyone have any idea on what is happening? Any direction would be great. Thank you in advance for any help you could offer. JOE On Thu, 8 Nov 2007 12:57:54 -0800, "Joe" <joe90026@sbcglobal.net> wrote: Not sure w...

Problem adding a control
I'm trying to add a checkbox to a dialog. GetDlgItem(IDC_MY_CHECK) returns NULL and if I try to give it a member variable I get "An unsupported operation was attempted" in the DoDataExchange() function when I go to that dialog. What causes this? WinXP Pro SP2, VS6 SP6. Thanks, Drew Drew wrote: > I'm trying to add a checkbox to a dialog. GetDlgItem(IDC_MY_CHECK) returns > NULL > and if I try to give it a member variable I get "An unsupported operation > was attempted" > in the DoDataExchange() function when I go to that dialog. What cause...

File Open Dialog Problem
Hello Dears, I am using WINCE 5 with SMDK2442. My problem is I am developing an application that i replace with explorer in common.reg to start on startup. When i Build the NK and burn it works fine. But problem is when i try to open file using Open File Dialog the dialog does not appears. I included shell into the NK and when i put explorer.exe as startup application its works fine and all file dialogs apears fine. But when i replace explorer with my startup.exe it does not display Open File Dialog. So please help me. thanks in advance Regards Nomi -- Nomi Message po...

Problem with External Data range Properties
Hi In the External Data Range Properties, there are 3 options for 'If the number of rows in the data range changes upon refresh:' I want it to stay on option 3 - Overwrite existing cells with new data, clear unused cells. but it keeps defaulting to option2 - Insert entire rows for new data, clear unused cells. I am using Excel 2000 and my data is from Access 97. Any ideas?? Steve ...

sql server deadlock (keylock)
hi guys, I have two procedures: one for selecting data from a table and do some calculations and the other for update or insert into the same table. There are two indexes on that table: one clustered (primary key): Pk_Table and one nonclustered: Ix_Table. I've captured a few deadlocks on those two procedure executions. It seems one proc gets x lock on the Pk_table key first and the other proc gets the x lock on Ix_Table and then both proc tries to get s lock on the other key. Then the deadlock happens. The below is part of deadlock xml: <resource-list> ...

Problem
I have a problem when running Outlook Express (IE version 6; under Win 2000 Professional SP3). Despite having configured my mail account to have the User Name and Password remembered (remember password tick box ticked) whenever I click Send/Receive the Logon dialogue box appears. This is the box that shows my Mail Server name, User Name and Password. The boxes are filled in with the correct details and the remember Password box is ticked but greyed out. When I click OK there is a slight delay and then the same dialogue box appears, on clicking OK the process repeats; throughout mai...

Vista major problem
hey i have a big problem with my computer... most of the times i open a program i get a error... like missing .dll or application error. please i need help really fast. i am on vista 32 bit. some of the problems are.. when i open control pannel, my computer or anything with windows explorer. it just closes and windows explorer crashes. i have tried to run chkdsk and errors appeared but i couldnt fix them. any advice is really great plz help :( -- kingcoolryan Repoяt problems and check for solutions automatically <http://windowshelp.microsoft.com/Windows/en-US/Help/fb...

Domain and Forest Prep Problem?
Hello, I recently helped a customer of mine upgrade their NT 4.0 domain to Win2k3 AD... The domain is currently running in mixed mode with a few NT BDC's still around. They are also running Exchange 5.5, and installed the Active Directory Connector... The problem is that I don't think they ran /DomainPrep or /ForestPrep.. Is it ok, and/or recommended, to run those now that the ADC has already been installed?? Thanks, Troy Yes they did (run forestprep/domainprep). The way setup is designed is that it will perform a check to make sure they have been run. If they have not ...

Meeting room booking conflicts in Exchange 2000 \ Outlook XP problem
Hi, We have a Citrix based Windows 2000 domain, running Exchange 2000 server and Outlook XP client. Meeting rooms are booked in our organistaion through an Outlook calendar (one for each room). This is done in the following way: The user creates a new appointment in Outlook The 'add others' buttons is depressed. Users are invited via the address books 'required' and 'optional' buttons, while the room is selected through the 'resources' buttons. The user pres...

Outlook 2007 eml attachment problem
Hi, I'm trying to figure out why a forwarded .eml attachment (sent from Thunderbird, containing email txt) is incomplete when opened in Outlook 2007. Mail store is iPlanet IMAP. The fwd'd mail and attachment from Tbird is HTML format. It appears that when Outlook pulls down the attachment it converts it to .msg format and truncates all of the inline quoted text, leaving only the last non-quoted part of the message. I've tried various "text only" settings in both Tbird and Outlook but the fwd'd attachment already contains HTML and I don't think those apply to it....

PO Entry problem
when I use a vb6 to create a purchase order entry. I used gateway.createpurchaseorder, it work and create a po, however, when i used the sql statement inside my vb6 program to create the po entris, the entries were inserted into the database, i can query it out and see they are there. However, if I used the RMS Manager program to view the order, there not entry for the order, please advice Raemond wrote: > when I use a vb6 to create a purchase order entry. I used > gateway.createpurchaseorder, it work and create a po, however, when i used > the sql statement inside my vb6 prog...

Problem with Combo Box FindNext
I'm an old dBase programer that trying to help some folks write an inventory control database. I made one form that substracts inventory from one table and adds it to another. When I copied this form to make another 'Move' screen I started having trouble. I copied Allen Browne's code from his help screen (listed below) and it gives me this error message. Unknown or Invalied Field Reference Error 3345 cboMaterialLookup is a combo box that finds the data RouterID is the field that FindFirst can't find! It's the Primary Key field and AutoNumber field...

Outlook 2002 Personal Folder problem
When booting the system and just as Outlook started loading it gave me the following message: the data file "old personal folders" was not closed properly. this file is being checked for problems. I watched the little window showing the minutes that took to check and at the end Outlook worked fine. It was very annoying and time consuming. I was planning to address the issue in the same forum I'm doing now but then disaster hit. I had to re-install everything. I used Norton Ghost and re-installation worked perfectly! Outlook did not give any problems and the personal f...

Problems with Inserting Rows
Morning Everyone In my previous version of Excel I could click on a row and then "Insert copied cells" anywhere on the sheet (as a new row). My problem is that with this version (2003 professional), when I try and re-insert the row, all the formulas have been converted to values. How do I switch back to inserting the row with formulas Many thanks in advanc Ja Inserting a row seems to work the same in Excel 2003 as in earlier versions, but maybe if you lay out the _precise_ steps to duplicate the problem someone can help you. In particular "re-insert the row" is unc...

COM threaded problem
Hi, I'm parsing multiple HTML documents, one per thread using MSHTML. I've cut and pasted the code from this article: http://www.codeguru.com/Cpp/I-N/ieprogram/article.php/c4385/ I include my function below which is basically a direct copy. When multiple threads are running the program occasionally crashes and the pDoc->Release(); statement in the MSHTML.dll- I assume because of some sort of contention in COM. Now I know nothing of COM and I am unsure what declarations should be process wide and which thread wide. For instance should CoInitialize(NULL); be declared once in main...

Problem sending mail from Outlook Express
I have been asking my ISP for help in resolving a problem I'm having with sending mail from Outlook Express 6 for the last 3 weeks. It is not resulting from long periods of inactivity because this message will pop up immediately after I log in. They suggested I reinput the mail server info etc. I have done that to no avail. They recently said the messages seemed to indicate the presence of a "Trojan" type virus and they said to scan my entire system. I updated my Norton and scanned every file and it found no error. I went back and had it scan only the Outlook file...

Annoying problem...Running a Query off of a Listbox
I've been working on this for a few days and it's annoying the crap out of me. I have a form that has a listbox bound to a query and 5 or so fields. The listbox is bound to a query and lists the names of all the clients in the system. When you click on a name in the listbox the fields on the form (Name, address, birthdate, etc) all autofill using a macro in the AfterUpdate on the listbox. This all works perfectly. Now I want to put a button on the page that will run and export a parameter query with the parameter being the ClientID number. The Client ID number is one of the field...

Problem copying notes
This is really getting frustrating. I'm asking the question again. When I try to copy the notes in the Notes folder to another folder by selecting all notes, copying it and then pasting it to new folder, I get the following message: "Creating a new item from the selected items could take some time. Are you sure you want to create a new item from these 292 items?" When I click Yes, i get the message: "Out of memory or system resources. Close some windows or programs and try again." I closed everything except outlook, tried it again and i get the same message. Ques...

inserting DateTime in Sql Server
Hi When I insert a DateTime value to a database, and read it back again, it appears that some of the data I wrote is missing. For example, if I insert a DateTime, with a time portion of 16:24:12.421 (that is, 4:24pm and 12.421 seconds), when I read it back I get 4:24pm and 12.420 seconds. It seems there is a millisecond missing. Is this a known "issue", or have I likely something else wrong in my program? Thanks, Peter "Peter K" <pk@alpha-solutions.dk> wrote in message news:%239e3lp3kKHA.5820@TK2MSFTNGP06.phx.gbl... > Is this a know...