I have a new report that I'm writing and the data is being sent from a vendor. The user can select what they see in column1, column2, etc. when they run the report. Column1 could be a date in the format of mm/yyyy or a date in the format of mm/dd or it could be an amount. The dates come over in the format of mm/dd/yyyy so I have to convert them to the proper format to display (either mm/yyyy or mm/dd). The amount field is also sent with 8 decimal places and the user only wants to see 2. I will be told what the user selected for each field so I know what format I would need for that column.

I figured I would define the field in the master as A20 for both usage and actual so that all values could be read easily but that complicates all 3 formats ... dates are much easier to work with when they are read in as smart date format. I've tried other formats for usage like D14.2BC to cover majority of scenarios and left actual as A20 but then I get nothing to display when the dates appear ... so I'm thinking I'm stuck with A20 for both. Is this true?

I'm trying to now set up defines in my code to cover all scenarios (there are 8 fields so code has to be duplicated for each field). My define section is getting rather huge, so I'm now questioning if I'm doing this the smartest way possible. I'm creating 3 fields (one numeric and one for each date type) for each field that comes in and determining which kind of field it needs to be. Is there a better way to do this? I can define the master however I want to define it.

Thanks.

This message has been edited. Last edited by: Pam Kratt,

quote:

Reply

Pam,

Have you tried setting up your format as a variable to be used in your define.
some thing like
-SET &VAR11=IF &FIELD_TYPE EQ 'AMOUNT'
- THEN 'D14.2BC' ELSE
- IF &FIELD_TYPE EQ 'AMOUNT' THEN
- 'YY/M/D';

DEFINE INFIELD/&VAR11 = IN_FIELD;

SHOULD BE
-SET &VAR11=IF &FIELD_TYPE EQ 'AMOUNT'
- THEN 'D14.2BC' ELSE
- IF &FIELD_TYPE EQ 'DATE' THEN
- 'YY/M/D';
ETC.

I had not thought to use a variable for formatting, but it's a great idea if I can get it to work. When I was just playing with it, It wasn't liking the format because the field is defined on the master as A20 (so this would be equiv to your IN_FIELD in your example) so it said RESULT OF EXPRESSION IS NOT COMPATIBLE WITH THE FORMAT OF FIELD on INFIELD/YYMD (I tried a variety of date formats including you 'YY/M/D' and none worked). RSquared - Do I need to define my master as something different than A20?

Pam
In the ACTUAL/USAGE of an MFD, you can change the USAGE to something useful when the ACTUAL is an A20. e.g. USAGE=YYMD, ACTUAL=A20,.

However in a DEFINE this characteristic is not available.

Whilst others may have a better solution, I would approach this by using a dynamic mfd during the report using EDAPUT (there are examples of this in the forum). This way the fields can be formatted as the report requires. This would also require DEFINES to be added into the mfd where you need to change an ACTUAL of YYMD to MYY or MD.

Pam, Here's an example of varaible formatting that works ok. Maybe of use......

TABLE FILE CAR
SUM RCOST DCOST
COMPUTE 
MYFMT/A8 = IF RCOST GT 10000 THEN 'P15.2CL' ELSE 'P15.2CS'; NOPRINT
COMPUTE 
TESTER/P15.2C = RCOST-DCOST;
TESTER/MYFMT AS 'VARFORMAT'
BY COUNTRY
BY CAR
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
END
-EXIT

Have a play !!

I agree with Alan, use EDAPUT to create your MFD within your fex. I use this quite a bit and it works GREAT. Below is the 'How To' Alan gave me years ago:

For those that are interested EDAPUT info:

The EDAPUT command syntax is:

EX -LINES {n} EDAPUT {File Type},{App/}{File Name},{Create Type},{Create Location}

n : Number of lines including EDAPUT Line
File Type : Type of File (e.g. MASTER, FOCEXEC, ACCESS, etc See Below)
App/ : Optionally Specify the APP directory
File Name : Name of the File without the extension
Create Type : Type of Create (CV=Create Variable, C=Create Fixed)
Create Location : File Location (FILE=Write to Current Location, MEM=Write to Memory only (V5.2 or later))


Watch out for...
• In V5.2 or later, if a file exists in the EDAPATH, it WILL be replaced, unless you use the MEM option.
• IBI say that this is internal code and is therefore NOT Supported


Known File Types, there may be more...
Version 5.1.4 Version 7.1.6 & 7.6.4
FILETYPE Extension FILETYPE Extension
ACCESS acx ACCESS acx
ADR adr ACCESSU acx
AFM afm ADR adr
BMP bmp AFM afm
BST bst BMP bmp
DATA dat BST bst
DDS DDS DATA dat
EDASYNA acx DDS DDS
EDASYNM mas DTD dtd
EDASYNR fex EDASYNA acx
EPS eps EDASYNM mas
EXCEL xls EDASYNR fex
FMU fmu EPS eps
FOCCOMP fcm ETG etg
FOCDEF def ETL etl
FOCEXEC fex EXCEL xls
FOCFTMAP fmp FMU fmu
FOCPSB psb FOCCOMP fcm
FOCSQL acx FOCCOMPW fcm
FOCSTYLE sty FOCDEF def
FOCTEMP ftm FOCEXEC fex
FOCUS foc FOCEXECU fex
GIF gif FOCFTMAP fmp
HLI hli FOCPSB psb
HOLDACC acx FOCSQL acx
HOLDMAST mas GIF gif
HTML htm FOCSTYLE sty
MASTER mas FOCSTYLU sty
MASTERU mas FOCTEMP ftm
MNTPAINT mpt FOCUS foc
OMI omi GIFU gif
PDF pdf HLI hli
PFA pfa HOLDACC acx
PFB pfb HOLDMAST mas
PS ps HTML htm
TABS txt HTMLU htm
TDL tdl JPG jpg
TTEDIT tte LSN lsn
TXT txt MAINTAIN mnt
WINFORMS wfm MAINTAIU mnt
XHT xht MASTER mas
XML xml MASTERU mas
XSL xsl MNTPAINT mpt
OMI omi
PDF pdf
PFA pfa
PFB pfb
PS ps
SVG svg
TABS txt
TDL tdl
TRF trf
TTEDIT tte
TXT txt
WINFORMS wfm
WINFORMU wfm
WSDL wsd
XHT xht
XML xml
XSD xsd
XSL xsl


Example Code
EX -LINES 5 EDAPUT MASTER,tempmast,CV,FILE
FILENAME=TEMPMAST, SUFFIX=FIX,$
SEGNAME=ONE, SEGTYPE=S1 ,$
FIELD=FIELD1 ,ALIAS= ,A10 ,A10 ,$
FIELD=FIELD2 ,ALIAS= ,P18 ,A18 ,$


Or

EX -LINES 4 EDAPUT MASTER,tempmast,CV,FILE,FILENAME=TEMPMAST, SUFFIX=FIX,$
SEGNAME=ONE, SEGTYPE=S1 ,$
FIELD=FIELD1 ,ALIAS= ,A10 ,A10 ,$
FIELD=FIELD2 ,ALIAS= ,P18 ,A18 ,$

One approach might be to read the input file with the A20 as you have it and then separate the different input patterns (Amount, Date, etc.) into separate columns in a hold file. You could then do whatever transformations are necessary on the individual columns rather than a fantastically complicated one on a single column.

For example:

DEFINE FILE XYZ
ITS_A_DATE/A20=IF INPUT_FIELD CONTAINS '/' THEN INPUT_FIELD ELSE '';
ITS_AN_AMOUNT/A20=IF INPUT FIELD NOT CONTAINS '/' THEN INPUT_FIELD ELSE '';
END
[/CODE]
If course you could do you transformations within the same two lines if you wished.

This message has been edited. Last edited by: George Patton,

Thank you to all that responded. I had this pretty much working based on the responses, only to find out the vendor is sending a file format where the fields are fixed (column1 will always be an amount, column 2 will always be a date, etc.) and that what I was currently being given was not accurate. Thanks for all the help!