How to put a formula as a criteria in a BAQ

Yahoo Archive
1
Hi Tony,

> first I want to get something out of my system... Whoever designed the BAQ
> GUI should be tied to a chair and forced to watch Full House, the Complete
> Series,

"Whoa!" he says in his best Joey voice, that's brutal...

> In SQL syntax, if I want only the release records with balance left to
> ship, I might do something like
> select * from orderrel where (orderrel.ourreqqty >
> (orderrel.ourjobshippedqty + orderrel.ourstockshippedqty))
>
> How do I translate this to a Vantage BAQ?

While the general problem of filtering on a calculation remains, in this
particular case you may want to select on OrderRel.OpenRelease (True = Open,
False = Closed/Voided). It's gets you want you're looking for and it won't
provide false positives where the release was voided and the quantity
calculation would show it as open.

Mark W.
2
first I want to get something out of my system... Whoever designed the BAQ GUI should be tied to a chair and forced to watch Full House, the Complete Series, as payment for all the time I have wasted trying to translate simple SQL statements in to... whatever it is that this thing wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to ship, I might do something like
select * from orderrel where (orderrel.ourreqqty > (orderrel.ourjobshippedqty + orderrel.ourstockshippedqty))

How do I translate this to a Vantage BAQ?


thank you!
3
I was under the impression that baq's in vantage used the 4gl language?
Your statement should be something like

For each orderrel where orderrel.qty > ......



Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg.com/>

(Click the logo to view our site)



________________________________

From: vantage@yahoogroups.com [mailto:vantage@yahoogroups.com] On Behalf
Of Tony Hughes
Sent: Thursday, March 26, 2009 7:18 AM
To: vantage@yahoogroups.com
Subject: [Vantage] How to put a formula as a criteria in a BAQ




first I want to get something out of my system... Whoever designed the
BAQ GUI should be tied to a chair and forced to watch Full House, the
Complete Series, as payment for all the time I have wasted trying to
translate simple SQL statements in to... whatever it is that this thing
wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to
ship, I might do something like
select * from orderrel where (orderrel.ourreqqty >
(orderrel.ourjobshippedqty + orderrel.ourstockshippedqty))

How do I translate this to a Vantage BAQ?

thank you!





[Non-text portions of this message have been removed]
4
You can not. It only works on tables and fields.
It is a tool wrote by Epicor.
Â
You have to have the BAQ pull all the orderrel records from the table. (You can filter on open order lines)
Â
You can also create a CALC field for Balance and set it to
orderrel.ourreqqty - (orderrel.ourjobshippedqty + orderrel.ourstockshippedqty)

Then in your crystal report you filter it by the calculation > 0

--- On Thu, 3/26/09, Tony Hughes <thughes281@...> wrote:

From: Tony Hughes <thughes281@...>
Subject: [Vantage] How to put a formula as a criteria in a BAQ
To: vantage@yahoogroups.com
Date: Thursday, March 26, 2009, 7:18 AM







first I want to get something out of my system... Whoever designed the BAQ GUI should be tied to a chair and forced to watch Full House, the Complete Series, as payment for all the time I have wasted trying to translate simple SQL statements in to... whatever it is that this thing wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to ship, I might do something like
select * from orderrel where (orderrel.ourreqqty > (orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty) )

How do I translate this to a Vantage BAQ?

thank you!



















[Non-text portions of this message have been removed]
5
Sorry, You can not use 4GL code also in a BAQ.
It is all done in the phrase builder where you can select tables and fields that are setup to use by Vantage. It then creates the 4GL code from the selections.
You can not edit the 4GL code in any way that I know of.
You can create a calculated field but you can not use that in a selection or filter also.


--- On Thu, 3/26/09, Rob Bucek <rbucek@...> wrote:

From: Rob Bucek <rbucek@...>
Subject: RE: [Vantage] How to put a formula as a criteria in a BAQ
To: vantage@yahoogroups.com
Date: Thursday, March 26, 2009, 8:32 AM






I was under the impression that baq's in vantage used the 4gl language?
Your statement should be something like

For each orderrel where orderrel.qty > ......

Rob Bucek

Manufacturing Engineer

PH: 715-284-5376 ext 311

FAX: 715-284-4084

<http://www.dsmfg com/>

(Click the logo to view our site)

____________ _________ _________ __

From: vantage@yahoogroups .com [mailto:vantage@yahoogroups .com] On Behalf
Of Tony Hughes
Sent: Thursday, March 26, 2009 7:18 AM
To: vantage@yahoogroups .com
Subject: [Vantage] How to put a formula as a criteria in a BAQ

first I want to get something out of my system... Whoever designed the
BAQ GUI should be tied to a chair and forced to watch Full House, the
Complete Series, as payment for all the time I have wasted trying to
translate simple SQL statements in to... whatever it is that this thing
wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to
ship, I might do something like
select * from orderrel where (orderrel.ourreqqty >
(orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty) )

How do I translate this to a Vantage BAQ?

thank you!

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]
6
Thank you
I didn't want it on paper, though, I wanted it in a dashboard, so they could right-click the order and open it.

lol, if I was wanting paper, I wouldn't be wasting time with BAQ, for sure.
I'd write a stored procedure.

Appreciate the help and the answers, thank you!





________________________________
From: DD <ddavis4569@...>
To: vantage@yahoogroups.com
Sent: Thursday, March 26, 2009 8:39:19 AM
Subject: Re: [Vantage] How to put a formula as a criteria in a BAQ


You can not. It only works on tables and fields.
It is a tool wrote by Epicor.

You have to have the BAQ pull all the orderrel records from the table. (You can filter on open order lines)

You can also create a CALC field for Balance and set it to
orderrel.ourreqqty - (orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty)

Then in your crystal report you filter it by the calculation > 0

--- On Thu, 3/26/09, Tony Hughes <thughes281@yahoo. com> wrote:

From: Tony Hughes <thughes281@yahoo. com>
Subject: [Vantage] How to put a formula as a criteria in a BAQ
To: vantage@yahoogroups .com
Date: Thursday, March 26, 2009, 7:18 AM

first I want to get something out of my system... Whoever designed the BAQ GUI should be tied to a chair and forced to watch Full House, the Complete Series, as payment for all the time I have wasted trying to translate simple SQL statements in to... whatever it is that this thing wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to ship, I might do something like
select * from orderrel where (orderrel.ourreqqty > (orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty) )

How do I translate this to a Vantage BAQ?

thank you!

[Non-text portions of this message have been removed]







[Non-text portions of this message have been removed]
7
Same thing.
Add the CALC field to the BAQ for the balance.
Next create the dashboard using that BAQ
now After the dashboard is running go back and add a filter using the CALC balance field <> 0.
Â
For some reason the CALC fields do not show up when setting up the dashboard query but do when you go back and add the filter.
Â
I just tried it with the orderrel on our system and it works fine.
shows just the data where the balance is > 0.


--- On Thu, 3/26/09, Tony Hughes <thughes281@...> wrote:

From: Tony Hughes <thughes281@...>
Subject: Re: [Vantage] How to put a formula as a criteria in a BAQ
To: vantage@yahoogroups.com
Date: Thursday, March 26, 2009, 10:13 AM






Thank you
I didn't want it on paper, though, I wanted it in a dashboard, so they could right-click the order and open it.

lol, if I was wanting paper, I wouldn't be wasting time with BAQ, for sure.
I'd write a stored procedure.

Appreciate the help and the answers, thank you!

____________ _________ _________ __
From: DD <ddavis4569@yahoo. com>
To: vantage@yahoogroups .com
Sent: Thursday, March 26, 2009 8:39:19 AM
Subject: Re: [Vantage] How to put a formula as a criteria in a BAQ

You can not. It only works on tables and fields.
It is a tool wrote by Epicor.

You have to have the BAQ pull all the orderrel records from the table. (You can filter on open order lines)

You can also create a CALC field for Balance and set it to
orderrel.ourreqqty - (orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty)

Then in your crystal report you filter it by the calculation > 0

--- On Thu, 3/26/09, Tony Hughes <thughes281@ yahoo. com> wrote:

From: Tony Hughes <thughes281@ yahoo. com>
Subject: [Vantage] How to put a formula as a criteria in a BAQ
To: vantage@yahoogroups .com
Date: Thursday, March 26, 2009, 7:18 AM

first I want to get something out of my system... Whoever designed the BAQ GUI should be tied to a chair and forced to watch Full House, the Complete Series, as payment for all the time I have wasted trying to translate simple SQL statements in to... whatever it is that this thing wants...

ok, feel better, now for the question.

Say for example, I wanted to put this as a condition :

In SQL syntax, if I want only the release records with balance left to ship, I might do something like
select * from orderrel where (orderrel.ourreqqty > (orderrel.ourjobshi ppedqty + orderrel.ourstocksh ippedqty) )

How do I translate this to a Vantage BAQ?

thank you!

[Non-text portions of this message have been removed]

[Non-text portions of this message have been removed]



















[Non-text portions of this message have been removed]