Renee
Renee
Sorry if this is elementary level BAQ question, however, does anyone know the right statement to convert the MM/DD/YY fields in a query to YYYY? I want to change the Job Completion Date from this to just displaying the year.
Thanks in advance,
Renee
EMT International, Inc.
These are BAQ calculations I have gleaned off of EpicWeb. There are a couple on dates.
order Detail GP Calculation
(IF OrderDtl.ExtPriceDtl = 0 THEN 0 ELSE
(If (+ PartCost.StdLaborCost + PartCost.StdBurdenCost + PartCost.StdMtlBurCost + PartCost.StdMaterialCost + PartCost.StdSubContCost) > 0 THEN
((+ OrderDtl.ExtPriceDtl - ((+ PartCost.StdLaborCost + PartCost.StdBurdenCost +
PartCost.StdMtlBurCost + PartCost.StdMaterialCost + PartCost.StdSubContCost) * OrderDtl.OrderOrderQty) / OrderDtl.ExtPriceDtl) * 100 ELSE 0))
Use Aggregate on table to find high and low values
Low_Qty Minimum( QuoteQty.OurQuantity )
High_Qty Maximum( QuoteQty.OurQuantity )
Then you can use in calculation
Low_Needed 3 Decimal Low_Qty * (QuoteMtl.QtyPer * UOM_Factor) 0 ->>,>>9.99 Low Needed False False
High_Needed 4 Decimal High_Qty * (QuoteMtl.QtyPer * UOM_Factor) 0 ->>,>>9.99 HIgh Needed False False
Use ISnull to set outer joins for calculations
UOM_Factor If ISNULL(PartUOM.ConvFactor) Then 1 else PartUOM.ConvFactor
Or isnull on char fields to set the display
PUM If ISNULL(Part.IUM) Then QuoteMtl.IUM Else Part.IUM
/* Replace all the LINEFEED LF characters with the SPACE character */
cOutputLine = REPLACE(cOutputLine, CHR(10), CHR(32)).
/* Replace all the FORMFEED FF characters with the SPACE character */
cOutputLine = REPLACE(cOutputLine, CHR(12), CHR(32)).
/* Replace all the ENTER / RETURN CR characters with the SPACE character */
cOutputLine = REPLACE(cOutputLine, CHR(13), CHR(32)).
Here is the Progress syntax
string(Month(OrderHed.OrderDate), "99") + "-" + string(Year(OrderHed.OrderDate), "9999")
The progress results for an OrderHed.Orderdate of 09/01/2011 is 09-2011
The SQL results would be 9-2011.
Here is the correct syntax for SQL:
entry( Month(OrderHed.OrderDate),'01,02,03,04,05,06,07,08,09,10,11,12', ',') + "-" + string(Year(OrderHed.OrderDate), "9999")
The SQL results would be 09-2011.
DESCRIPTION:
BAQ General information on BAQ Calculated fields
To create calculated fields, you will need to understand how Progress evaluates calculations. To help with this we have provided specific examples that you can review. In addition in Note 3 at the bottom of this answerbook you will find a web address for a progress pdf file with syntax information. Finally, most of these examples do not give you formatting instructions. Please see Notes 1 for format information.
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.MtlBurUnitCost + InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) * InvcDtl.SellingShipQty
NOTE: At VN/VS 8.03.406+ versions remove the leading plus sign, so that the calculation looks like this:
( InvDtl.LbrUnitCost + InvcDtl.BurUnitCost + InvcDtl.MtlBurUnitCost + InvcDtl.MtlUnitCost + InvcDtl.SubUnitCost) * InvcDtl.SellingShipQty
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.ActualClockinDate) = 1 then "Sunday" else
(if WEEKDAY(LaborHed.ActualClockinDate) = 2 then "Monday" else
(If WEEKDAY(LaborHed.ActualClockinDate) = 3 then "Tuesday" else
( IF WEEKDAY(LaborHed.ActualClockinDate) = 4 then "Wednesday" else
(IF WEEKDAY(LaborHed.ActualClockinDate) = 5 then "Thursday" else
(If WEEKDAY(LaborHed.ActualClockinDate) = 6 then "Friday" else
( If WEEKDAY(LaborHed.ActualClockinDate) = 7 then "Saturday" else "0" )))) )) )
***For SQL where you want to display the MONTH use this syntax:
entry( Month( RMAHead.RMADate),
'January,February,March,April,May,June,July,August,September,
October,November,December', ',')
NOTE: A similar Calc can be used in Progress as well instead of IF statements which have a limit as to number of IF statements used in one string. But you don't need to
identify the delimiter in Progress, so the Calc would look like this:
entry( Month( RMAHead.RMADate),
'January,February,March,April,May,June,July,August,September,October,November,December')
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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")
***For SQL where you want to display the Period use this syntax:
entry( Month(OrderHed.OrderDate),'01,02,03,04,05,06,07,08,09,10,11,12', ',') + "-" + string(Year(OrderHed.OrderDate), "9999")
Example # 6.1: How to change format of Todays date to MMDDYYYY
String( Month(Today),"99") + "/" + String( Day(Today),"99") + "/" + string(Year(Today),"9999" )
- data type is character, format is x(10)
Example 6.2: How to change Todays date to format YYYMMDD
string(Year(Today),"9999" ) + "/" + String( Month(Today),"99") + "/" + String( Day(Today),"99")
- data type is character, format is x(10)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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))
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example # 8: how to do concatenate two character field together? Note: for this example you would set the data type to Character and the format to X(100).
string(EmpBasic.address) + string(EmpBasic.address2)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example # 9: how to calculate the unit price of a part that uses a unit of measure of /100 or /1000 or /Each? Note for this example you would set the data type to Double and the format to >>,>>>,>>>.99.
if OrderDtl.PricePerCode = "E" then (OrderDtl.OrderQty * OrderDtl.UnitPrice) else if OrderDtl.PricePerCode = "C" then ((OrderDtl.OrderQty / 100) * OrderDtl.UnitPrice) else ((OrderDtl.OrderQty / 1000) * OrderDtl.UnitPrice)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example #10: how to convert time to HH:MM:SS format
STRING(HDCase.LastUpdatedTime,"HH:MM:SS")
Example #10a: how to convert time to HH:MM AM/PM format
STRING(HDCase.LastUpdatedTime,"HH:MM AM")
--data type is Character, format is X(8)
(may not work in SQL - may be fixed in Epicor 9 SQL)
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Example #11: How to shorten a text box for example I want to display the Part Description as only 25 characters.
SUBSTRING(JobHead.PartDescription,1,25)
--data type is Character, format is X(25)
-----------------------------------------------------------------------------------------------------------------------------------------------
Example #12:
If you need to use a long string of If statements - you can use a "case" that utilizes the Progress "Lookup" function:
Syntax for Progress:
entry( lookup( substring( Part.PartNum, 1, 2 ),
"AB,AC,CE,CP,DE,HD,HI,NY,PC,PP,PV" ),
"ABS,Acrylic,Celtec,CPVC,Acetal,High Density Polyethylene / HDPE,Hi Impact Styrene / HIS,Nylon,Polycarbonate,Polypropylene,PVC" )
Syntax for SQL:
entry( lookup( substring( Part.PartNum, 1, 2 ),
"AB,AC,CE,CP,DE,HD,HI,NY,PC,PP,PV", "," ),
"ABS,Acrylic,Celtec,CPVC,Acetal,High Density Polyethylene / HDPE,Hi Impact Styrene / HIS,Nylon,Polycarbonate,Polypropylene,PVC", "," )
-- NOTE: At some point in the future (when a fix is available) SQL db users may be able to use the Progress syntax above, but this SQL syntax should continue to work. It supplies the delimiter info needed.
-------------------------------------------------------------------------------------------------------------------------------------------------------------
Example # 13 If you need to use a date in your calculation DO NOT use quotes around the value your comparing too. JEDate is declares as Date type and is treated as a numeric rather than a character string.
If GLJrnDtl.JEDate = 07/14/2009 Then GLJrnDtl.TranAmt else 0
-- data type is Decimal, format is >>>>>>>9.99
-------------------------------------------------------------------------------------------------------------------------------------------
Example #14 How do you pick up the current time
String (time,"HH:MM:SS")
-- data type is Character, format is X(8)
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Example #15 How to extract the first salesrep from the salesrepList field on the OrderHed
entry(1,OrderHed.SalesRepList,"~~")
-- date type is Character, format is X(10)
---------------------------------------------------------------------------------------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Example #16 How do you pick up the current date
Field Name: CurDate
Expression: Today
- data type is date, format is 99/99/99
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Example #17 How do you age against today's date?
Expression: Integer(Today) - Integer(OrderHed.RequestDate)
- data type is integer, format is 999
----------------------------------------------------------------------------------------------------------------------------------------------------------------
Example # 18 How to you convert Invchead.Paydates to a date?
InvcHead.Paydates is a number stored in a character field. Is not an actual date. To convert it to a date add 01/01/1899 + 1 day.
Field Name: ConvPayDate
Expression: 01/01/1899 + 1 + Integer(InvcHead.PayDates)
- data type is date, format is 99/99/99
----------------------------------------------------------------------------------------------------------------------------------------------------------------
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
> - 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
>>>>9 -- A number only field that displays numeral results up to 1,000
Hint: If you add a format be sure to save and exit the BAQ before testing it.
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. (Also, in VN8 you cannot use this summary Calc field in another Calculation.)
NOTE 3: Because the BAQs in Vantage are using the Progress 4GL programming language in depth syntaxes will not be found in Vantage help. But you may find syntax information 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://communities.progress.com/pcom/docs/DOC-4748
NOTE 4: If you want/need today's date use the TODAY function.
NOTE 5: 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 " it will not syntax but " LaborDtl.BurdenHrs + 0.005 " is okay.
Attachments:
(If (OrderDtl.PricePerCode = 'M') Then
(((If (OrderRel.OurReqQty - OrderRel.OurJobShippedQty
- OrderRel.OurStockShippedQty) > 0
Then
(OrderRel.OurReqQty - OrderRel.OurJobShippedQty
- OrderRel.OurStockShippedQty)
Else 0)/ 1000) * OrderDtl.DocUnitPrice * (1 - (OrderDtl.DiscountPercent / 100))) Else
(If (OrderDtl.PricePerCode = 'C') Then
(((If (OrderRel.OurReqQty - OrderRel.OurJobShippedQty
- OrderRel.OurStockShippedQty) > 0
Then
(OrderRel.OurReqQty - OrderRel.OurJobShippedQty
- OrderRel.OurStockShippedQty)
Else 0)/ 100) * OrderDtl.DocUnitPrice * (1 - (OrderDtl.DiscountPercent / 100))) Else
((
(Message over 64 KB, truncated)