Thanks for publishing the link Bob. (Progress offers a free 4GL handbook on their site in pdf - about 1000 pgs - packed with this stuff.)
Most of the fancy 4GL fails in BAQs (no support built in to the BAQ designer) - but sometimes a useful jewel can be found.
Rob
Most of the fancy 4GL fails in BAQs (no support built in to the BAQ designer) - but sometimes a useful jewel can be found.
Rob
--- On Thu, 8/7/08, Robert Carlson <rcarlson@...> wrote:
From: Robert Carlson <rcarlson@...>
Subject: RE: [Vantage] BAQ Calculated Field Format to $.00 Question
To: vantage@yahoogroups.com
Date: Thursday, August 7, 2008, 3:43 PM
For those interested parties, Dale's reference to Answerbook 3643ESC reads
as below: note-the reference to
http://www.psdn com/library/ servlet/KbServle t/download/ / dvhbk.pdf
does work and yeilds an Open Edge 10 Progress 4GL Handbook.... ...very
technical but some good clues if you have the interest to dig for them:
AnswerBook #: 3643ESC
Product: Vantage
Added: 06/27/2007
Version: 8.00.811
Changed: 05/28/2008
Module: BAQ
Summary: BAQ General information on BAQ Calculated fields - Calculations
Details:
PAGE NO: 3643ESC
DESCRIPTION: BAQ General information on BAQ Calculated fields
The following comes from the Vantage help.
------------ --------- --------- --------- --------- --------- -
To create calculated fields, you will need to understand how Progress
evaluates calculations.
Example #1: This calculation is based on the InvDtl file. It calculates the
total cost of the invoice line (not its sales revenue) by adding together
the Labor, Burden, Material Burden, Material Cost and Subcontract Cost; it
then multiplies this sum against the line's Ship Quantity.
(+ InvDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitC ost +
InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost ) * InvcDtl.SellingShip Qty
Example #2: These equations calculate a value that is placed in either the
Debit or Credit column. This is based on the GL Journal Transaction amount.
Debit - (if gljrndtl.transamt > 0 then gljrndtl.transamt else 0)
Credit - (if gljrnidtl.transamt < 0 then gljrnldtl.transamt else 0)
Both calculations evaluate the Transaction Amount value field from the GL
Journal Detail table. If the amount is greater than zero, the amount is
placed within the Debit column. If the amount is less than zero, the amount
is placed within the Credit column.
Example #3 To round a value to two decimal places you can use the formula:
Round(LaborDtl. LaborHrs, 2)
Example #4 To add a value and then round use the formula.
Round(( + LaborDtl.LaborHrs + 0.005), 2).
Notice that I used parentheses to enclose the formula to add .005 to the
LaborHrs.
Example #5 To convert a date to the day of the week e.g. 1/1/2007 to Monday.
(If WEEKDAY(LaborHed. ActualClockinDat e) = 1 then "Sunday" else
(if WEEKDAY(LaborHed. ActualClockinDat e) = 2 then "Monday" else
(If WEEKDAY(LaborHed. ActualClockinDat e) = 3 then "Tuesday" else
( IF WEEKDAY(LaborHed. ActualClockinDat e) = 4 then "Wednesday" else
(IF WEEKDAY(LaborHed. ActualClockinDat e) = 5 then "Thursday" else
(If WEEKDAY(LaborHed. ActualClockinDat e) = 6 then "Friday" else
( If WEEKDAY(LaborHed. ActualClockinDat e) = 7 then "Saturday" else "0" )))) )) )
Example # 6: To create a MonthYear field. You will need use both a
concatenation and a string function. To do a concatenation use the +
symbol. Use the string as the "outer function. Note: for this example you
must set the data type to Character and the format to X(7).
string(Month( OrderHed. OrderDate) , "99") + "-" +
string(Year( OrderHed. OrderDate) , "9999")
Example # 7: To subtract two date fields you need to convert them to
integers first. Note: for this example, set the data type to integer and
the format to >>>,>>9.
(integer(ShipHead. ShipDate) - integer(OrderRel. ReqDate))
------------ --------- --------- --------- --------- --------- -
NOTE 1: When calculating a Cost field you will want to set the data type to
Decimal and the format to >>>>>9.9999 where ">" implies suppress leading
zeros.
Available Data Types are:
Character
Integer
Decimal
Date
Logical
The format that this calculated field will use to display its result. There
are several single character format options. You use these options in
various combinations to display the results in the format that you want.
Here are the single character formats:
X - Any Character
N - Number or Letter
A - Letter Only
! - Lower Case Letter
9 - Number Only
> - Suppress Zeros
Commonly you will enter a single character format that with the syntax
<single-char- format>(nnn) . The <single-char- format> is any of the format
options listed above. The (nnn) variable is an integer that defines the
length of the calculated field.
Example: Here are some format examples that you can enter in this field:
X(16) -- A 16 character field
A(4) -- A letter only field that displays up to 4 letters
>>>>9 -- A number only field that displays numeral results up to 1,000
NOTE 2: If you are creating a calculated field for a summarized table, you
can only use the TOTAL, AVG, or COUNT expressions to display the table's
data. These expressions calculate summary views of the table's data.
------------ --------- --------- --------- --------- --------- -
Additional Notes:
1) Because the BAQs in Vantage are using the Progress 4GL programming
language in depth syntaxes will not be found in theVantage help. But you
may find syntax information in in this PDF file found on Progress website:
OpenEdge Development: Progress 4GL Handbook by John Sadd
Chapter 2 contains information on Arithmetic expressions, 4GL functions
(Day, Month, Year, Today), etc.
http://www.psdn com/library/ servlet/KbServle t/download/ / dvhbk.pdf
2) If you want need today's date use the TODAY function.
3) When adding a constant to a formula be sure that the constant starts
with a number rather than a decimal place for example if you add .005 to
LaborHrs the formula " + LaborDtl.BurdenHrs + .005 " will not syntax but "
+ LaborDtl.BurdenHrs + 0.005 " is okay.
At 03:25 PM 8/7/2008, you wrote:
>Seems to be broken, I have tested it here and it is not working either.
>
>Please go look up Answerbook 3643ESC, it has examples. This is supposed
>to use Progress 4GL syntax.
>
>This answerbook references a progress document.
>
>I am waiting on a BAQ call for another issue; I will ask when they call
>me.
>
>8.03.404A Progress
>
>Dale Schuerman
>
>Senior Consultant
>
>RAM Software Systems, Inc.
>1701 North Collins Suite 200
>Richardson, Texas 75080
>Phone:
>Fax:
>Web Site: www.ramsys.com
><blocked::<http://www.ramsys com/>http://www.ramsys com/>
>
>___________ _________ _________ ___
>
>From: <mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com
>[mailto:vantage@yahoogroups .com] On Behalf
>Of Robert Carlson
>Sent: Thursday, August 07, :35 PM
>To: <mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com;
><mailto:vantage% 40yahoogroups. com>vantage@yahoogroups .com
>Subject: RE: [Vantage] BAQ Calculated Field Format to $.00 Question
>
>Hi Dale,
>
>Tried that format as an integer, character, & decimal..... ...puts the
>commas in; always returns 5 decimal places, never inserts the dollar
>sign.
>
>This is in 8.03.404B SQL
>
>Thanks for the suggestion though.
>
>At 10:13 AM 8/7/2008, Dale Schuerman wrote:
> >$->>>,>>>,> >>.99
>
>[Non-text portions of this message have been removed]
>
>
[Non-text portions of this message have been removed]