Vantage BAQ - If... Then... Else

Yahoo Archive
1
I've had to use one of the convert functions before to get the types to match--like converting double precision decimal to decimal.

It's the little things. :-)

Joe

On Tue, Feb 2, 2016 at 4:46 AM, tracy.smith@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p>Thanks Joe.  I&#39;m not sure why but I&#39;ve figured out that it doesn&#39;t like me using the &#39;OnTimeOrig&#39; calculated field name in a calculation even though it is fine with me using the &#39;OnTime&#39; one!  I have had to resort to putting 2 calculations into one calculated field.  Thank you for all your advice.</p>

</div><span class="ygrps-yiv-364984042">
 


<div style="color:#fff;min-height:0;"></div>


2

I have the been working on our OTIF reporting and have the last calculation to work on for reporting but I am getting a syntax error on the last calculation.  Formula aren't my strong point so I just wanted to check I wasn't missing anything really obvious as I've been working on various ones the last couple of days and my head is spinning!


If OnTime = 1
Then OnTime
Else
If ShipDtl.ShortChar02 = 'OTIF-20160128104547'
Then 0
Else
OnTimeOrig


OnTime and OnTimeOrig are calculated fields with Integer results and work fine.  Thanks in advance.

3
Is there supposed to be an "End" there? I can't remember.

On Thu, Jan 28, 2016 at 12:16 PM, tracy.smith@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p></p><p><span></span></p><p>I have the been working on our OTIF reporting and have the last calculation to work on for reporting but I am getting a syntax error on the last calculation.  Formula aren&#39;t my strong point so I just wanted to check I wasn&#39;t missing anything really obvious as I&#39;ve been working on various ones the last couple of days and my head is spinning!</p><p><br></p><p>If OnTime = 1<br>Then OnTime <br>Else<br>If ShipDtl.ShortChar02 = &#39;OTIF-20160128104547&#39; <br>Then 0 <br>Else<br>OnTimeOrig</p><p><br></p><p>OnTime and OnTimeOrig are calculated fields with Integer results and work fine.  Thanks in advance.</p><p></p>

</div>
 


<div style="color:#fff;min-height:0;"></div>

4
Thanks... I've just tried that but still no joy :(
5
Okay, got where I could look it up. Here's the format:

case Â
  when  thenÂ
  when  thenÂ
  elseÂ
end

Joe

On Thu, Jan 28, 2016 at 2:48 PM, tracy.smith@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p>Thanks... I&#39;ve just tried that but still no joy :(</p>

</div><span class="ygrps-yiv-1438320326">
 


<div style="color:#fff;min-height:0;"></div>


6
No, she has a Char field and relating it back to an int field. She can do that, but she has to convert the Char field using something like Convert.ToInt32(Console.ReadLine()) or if using VB using your Dim statements. Or you could just say:

If ShipDtl.ShortChar02 = 'OTIF-20160128104547' 
Then "0" 
 
by using the quotes you're stating '0' is a char not an int.

Jonathan Lang
I.T Manager
Database Engineer



On Thursday, January 28, 2016 3:07 PM, "Joe Douglas Trent jdtrent@... [vantage]" <vantage@yahoogroups.com> wrote:


  
<div id="ygrps-yiv-1044920796yiv7486241036ygrp-text">
  
  
  <div></div><div dir="ltr">Okay, got where I could look it up. Here&#39;s the format:<div><br clear="none"></div><div><div>case &nbsp;</div><div>&nbsp; &nbsp; when &nbsp;then&nbsp;</div><div>&nbsp; &nbsp; when &nbsp;then&nbsp;</div><div>&nbsp; &nbsp; else&nbsp;</div><div>end</div></div><div><br clear="none"></div><div>Joe</div></div><div class="ygrps-yiv-1044920796yiv7486241036yqt2002789518" id="ygrps-yiv-1044920796yiv7486241036yqt61356"><div class="ygrps-yiv-1044920796yiv7486241036gmail_extra"><br clear="none"><div class="ygrps-yiv-1044920796yiv7486241036gmail_quote">On Thu, Jan 28, 2016 at 2:48 PM, <a rel="nofollow" shape="rect" target="_blank" href="mailto:tracy.smith@...">tracy.smith@...</a> [vantage] <span dir="ltr">&lt;<a rel="nofollow" shape="rect" target="_blank" href="mailto:vantage@yahoogroups.com">vantage@yahoogroups.com</a>&gt;</span> wrote:<br clear="none"><blockquote><span title="qreply"> <blockquote class="ygrps-yiv-1044920796yiv7486241036gmail_quote" style="border-left:1px #ccc solid;">

  
<div>
  
  
  <div>Thanks... I&#39;ve just tried that&nbsp;but still no joy :(</div>

</div><span class="ygrps-yiv-1044920796yiv7486241036class">
 


</span><div style="color:#fff;min-height:0;"></div>


</div>
 


<div class="ygrps-yiv-1044920796yiv7486241036yqt2002789518" id="ygrps-yiv-1044920796yiv7486241036yqt84092"><div style="color:#fff;height:0;"></div></div>
#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036 -- #ygrps-yiv-1044920796yiv7486241036ygrp-mkp { border:1px solid #d8d8d8;font-family:Arial;margin:10px 0;padding:0 10px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp hr {
border:1px solid #d8d8d8;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp #ygrps-yiv-1044920796yiv7486241036hd {
color:#628c2a;font-size:85%;font-weight:700;line-height:122%;margin:10px 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp #ygrps-yiv-1044920796yiv7486241036ads {
margin-bottom:10px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp .ygrps-yiv-1044920796yiv7486241036ad {
padding:0 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp .ygrps-yiv-1044920796yiv7486241036ad p {
margin:0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mkp .ygrps-yiv-1044920796yiv7486241036ad a {
color:#0000ff;text-decoration:none;}
#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ygrp-lc {
font-family:Arial;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ygrp-lc #ygrps-yiv-1044920796yiv7486241036hd {
margin:10px 0px;font-weight:700;font-size:78%;line-height:122%;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ygrp-lc .ygrps-yiv-1044920796yiv7486241036ad {
margin-bottom:10px;padding:0 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036actions {
font-family:Verdana;font-size:11px;padding:10px 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity {
background-color:#e0ecee;float:left;font-family:Verdana;font-size:10px;padding:10px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity span {
font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity span:first-child {
text-transform:uppercase;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity span a {
color:#5085b6;text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity span span {
color:#ff7900;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036activity span .ygrps-yiv-1044920796yiv7486241036underline {
text-decoration:underline;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036attach {
clear:both;display:table;font-family:Arial;font-size:12px;padding:10px 0;width:400px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036attach div a {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036attach img {
border:none;padding-right:5px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036attach label {
display:block;margin-bottom:5px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036attach label a {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 blockquote {
margin:0 0 0 4px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036bold {
font-family:Arial;font-size:13px;font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036bold a {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 dd.ygrps-yiv-1044920796yiv7486241036last p a {
font-family:Verdana;font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 dd.ygrps-yiv-1044920796yiv7486241036last p span {
margin-right:10px;font-family:Verdana;font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 dd.ygrps-yiv-1044920796yiv7486241036last p span.ygrps-yiv-1044920796yiv7486241036yshortcuts {
margin-right:0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036attach-table div div a {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036attach-table {
width:400px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036file-title a, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036file-title a:active, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036file-title a:hover, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036file-title a:visited {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036photo-title a, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036photo-title a:active, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036photo-title a:hover, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div.ygrps-yiv-1044920796yiv7486241036photo-title a:visited {
text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 div#ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg #ygrps-yiv-1044920796yiv7486241036ygrp-msg p a span.ygrps-yiv-1044920796yiv7486241036yshortcuts {
font-family:Verdana;font-size:10px;font-weight:normal;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036green {
color:#628c2a;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036MsoNormal {
margin:0 0 0 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 o {
font-size:0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036photos div {
float:left;width:72px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036photos div div {
border:1px solid #666666;height:62px;overflow:hidden;width:62px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036photos div label {
color:#666666;font-size:10px;overflow:hidden;text-align:center;white-space:nowrap;width:64px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036reco-category {
font-size:77%;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036reco-desc {
font-size:77%;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 .ygrps-yiv-1044920796yiv7486241036replbq {
margin:4px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-actbar div a:first-child {
margin-right:2px;padding-right:5px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg {
font-size:13px;font-family:Arial, helvetica, clean, sans-serif;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg table {
font-size:inherit;font:100%;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg select, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 input, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 textarea {
font:99% Arial, Helvetica, clean, sans-serif;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg pre, #ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 code {
font:115% monospace;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg * {
line-height:1.22em;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-mlmsg #ygrps-yiv-1044920796yiv7486241036logo {
padding-bottom:10px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-msg p a {
font-family:Verdana;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-msg p#ygrps-yiv-1044920796yiv7486241036attach-count span {
color:#1E66AE;font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-reco #ygrps-yiv-1044920796yiv7486241036reco-head {
color:#ff7900;font-weight:700;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-reco {
margin-bottom:20px;padding:0px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ov li a {
font-size:130%;text-decoration:none;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ov li {
font-size:77%;list-style-type:square;padding:6px 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-sponsor #ygrps-yiv-1044920796yiv7486241036ov ul {
margin:0;padding:0 0 0 8px;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-text {
font-family:Georgia;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-text p {
margin:0 0 1em 0;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-text tt {
font-size:120%;}

#ygrps-yiv-1044920796 #ygrps-yiv-1044920796yiv7486241036 #ygrps-yiv-1044920796yiv7486241036ygrp-vital ul li:last-child {
border-right:none !important;}
#ygrps-yiv-1044920796



7

Just to clarify this is a BAQ.. I thought the case statement was VB\form customisation?


Also, I am only using the character field as a criteria for the 'If'....


If OnTime = 1
Then OnTime
Else
If ShipDtl.ShortChar02 = 'OTIF-20160128104547'
Then 0
Else
OnTimeOrig



I was expecting this to work as follows:

If [Calculated field]OnTime = 1

Then [Result is calculated field]OnTime

Else [So OnTime <> 1]

If ShipDtl.ShortChar02 = 'OTIF-20160128104547' [Result of ShortChar02 = OTIFNumber]

Then 0 [Result is 0]

Else [So ShortChar02 <> OTIF Number]

OnTimeOrig [Result is calculated field]OnTimeOrig



Hopefully this explains what I am trying to achieve a little better?

8
In E10 the BAQ calculated fields use the Case statement. I don't know about older versions.

Joe

On Mon, Feb 1, 2016 at 5:54 AM, tracy.smith@... [vantage] <vantage@yahoogroups.com> wrote:

 
<div>
  
  
  <p></p><p>Just to clarify this is a BAQ.. I thought the case statement was VB&#92;form customisation?</p><p><br></p><p>Also, I am only using the character field as a criteria for the &#39;If&#39;....</p><p><br></p><p><span class="ygrps-yiv-1085582894">If OnTime = 1<br>Then OnTime <br>Else<br></span><span class="ygrps-yiv-1085582894">If ShipDtl.ShortChar02 = &#39;OTIF-20160128104547&#39; <br>Then 0 <br></span>Else<br>OnTimeOrig</p><p><br></p><p><br></p><p>I was expecting this to work as follows:</p><p>If [Calculated field]OnTime = 1</p><p>Then [Result is calculated field]OnTime</p><p>Else [So OnTime &lt;&gt; 1]</p><p>If ShipDtl.ShortChar02 = &#39;OTIF-20160128104547&#39; [Result of ShortChar02 = OTIFNumber]</p><p>Then 0 [Result is 0]</p><p>Else [So ShortChar02 &lt;&gt; OTIF Number]</p><p>OnTimeOrig [Result is calculated field]OnTimeOrig</p><p><br></p><p><br></p><p>Hopefully this explains what I am trying to achieve a little better?<br></p><p></p>

</div>
 


<div style="color:#fff;min-height:0;"></div>

9
Thanks Joe.  I'm not sure why but I've figured out that it doesn't like me using the 'OnTimeOrig' calculated field name in a calculation even though it is fine with me using the 'OnTime' one!  I have had to resort to putting 2 calculations into one calculated field.  Thank you for all your advice.