Travel and Accommodation don't exist as fields in the database but I want to group the individual fields under those categories even if they don't contain any data (as I show with PARKING above).
So basically I want something equivalent to:
IF FIELDNAME IS AIR_FARE THEN EXPENSE_TYPE IS TRAVEL ELSE IF FIELDNAME IS TAXI THEN EXPENSE_TYPE IS TRAVEL ELSE IF FIELDNAME IS HOTEL THEN EXPENSE_TYPE IS ACCOMMODATION etc....
I've thought of a number ow ways to tackle this - MacGyver, OCCURS, change fieldname in Master, etc. - but have come up short each time. (The MFD produced by the synonym editor does not allow the creation of groups and the relevant fields are not next to each other in the MFD in any case.)
I bet this is really simple, but slinking off into the corner is less painful than banging my head against the wall.This message has been edited. Last edited by: George Patton,
TABLE FILE CAR
HEADING
"Fields1:"
"DEALER_COST : <TOT.DEALER_COST"
"WEIGHT : <TOT.WEIGHT"
" "
"Fields2:"
"RETAIL_COST: <TOT.RETAIL_COST"
"SALES: <TOT.SALES"
END
WF 7.6.11 Output: HTML, PDF, Excel
March 02, 2015, 06:54 AM
TimN
I think your example is correct.
"F FIELDNAME IS AIR_FARE THEN EXPENSE_TYPE IS TRAVEL ELSE IF FIELDNAME IS TAXI THEN EXPENSE_TYPE IS TRAVEL ELSE IF FIELDNAME IS HOTEL THEN EXPENSE_TYPE IS ACCOMMODATION etc...."
Give that condition a name (e.g. EXPENSE_GROUP) and then TABLE by that name first then by EXPENSE_TYPE.
e.g. TABLE FILE xxxxx SUM *VALUE*
BY EXPENSE_GROUP BY EXPENSE_TYPE END
The output will look like:
Travel AIR_FARE $500.00 TAXI $ 40.00 PARKING .
Accommodation HOTEL $160.00 MEALS $ 55.00
Unless I'm misunderstanding your issue.
WF App Studio 8.0.0.9 WF Dev Studio 8.0.0.8 WF Dev Studio 7.7
March 02, 2015, 07:04 AM
Danny-SRL
Another option is to use the FOR command of WF FML (Finacial Modeling Language):
-SET &ECHO=ALL;
DEFINE FILE CAR
AIR_FARE/D6M=SALES;
TAXI/D6M=RCOST/100;
PARKING/D6M=DCOST/100;
HOTEL/D6M=(DCOST+RCOST)/10;
MEALS/D6M=SALES/100;
END
TABLE FILE CAR
SUM AIR_FARE TAXI PARKING HOTEL MEALS
BY CAR
ON TABLE HOLD AS FOR01 FORMAT ALPHA
END
-RUN
EX -LINES 7 EDAPUT MASTER,FOR01,C,MEM
FILENAME=FOR01, SUFFIX=FIX
SEGNAME=FOR01, SEGTYPE=S0
FIELDNAME=CAR, ALIAS=CAR, USAGE=A16, ACTUAL=A16, $
SEGNAME=EXP, PARENT=FOR01, OCCURS=VARIABLE
FIELDNAME=EXPENSE, ALIAS=EXP, USAGE=D6M, ACTUAL=A6, $
FIELDNAME=EXPTYPE, ALIAS=ORDER, USAGE=I2, ACTUAL=I4,$
-RUN
DEFINE FILE FOR01
EXPNAME/A12=DECODE EXPTYPE(
1 AIR_FARE
2 TAXI
3 PARKING
4 HOTEL
5 MEALS
);
TODAY/DMYY='&DATEDMYY';
TRAVEL_DATE/DMYY WITH CAR=TODAY + 30*RDUNIF('D4.3');
END
TABLE FILE FOR01
SUM EXPENSE AS ''
BY TRAVEL_DATE NOPRINT
SUBHEAD
"<TRAVEL_DATE "
FOR EXPNAME
"Travel" OVER
AIR_FARE OVER
TAXI OVER
PARKING OVER
"Accomodation" OVER
HOTEL OVER
MEALS
ON TABLE SET LINES 999
END
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 02, 2015, 08:51 AM
djones
George, Can you DECODE the individual filed to the cooresponding category? Something like this?
-* File expense.fex
DEFINE FILE CAR
group_field/A13=DECODE CAR.COMP.CAR(
'ALFA ROMEO' 'Travel'
AUDI Accommodation
BMW Travel
DATSUN Travel
JAGUAR Travel
JENSEN Travel
MASERATI Accommodation
PEUGEOT Accommodation
TOYOTA Accommodation
TRIUMPH Travel
);
END
TABLE FILE CAR
SUM
CAR.BODY.DEALER_COST
BY HIGHEST group_field
BY CAR.COMP.CAR
ON TABLE SET PAGE-NUM NOLEAD
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT HTML
ON TABLE SET HTMLCSS ON
ON TABLE SET STYLE *
INCLUDE = endeflt,
$
ENDSTYLE
END
WebFOCUS 8.0.05 Windows- Excel, PDF
March 02, 2015, 09:19 AM
George Patton
Thank you all for your suggestions. I did take a look at FML, but still couldn't get my head around how to create the "accounts" necessary to make the FOR part work. Daniel has shown me how to do that.
I'm probably suffering from the effects of switching languages, where a solution in any of the languages supported by Lotus Notes (Formula language, LotusScript, JavaScript) was easier to get my head around.
Daniel's solution has shown me how to do exactly what I need, I think, so I'll be pursuing his approach. It's going to get a lot more complicated because I have to place comments beside some but not all of the dollar values, and join to other tables etc.
Tewy's idea of doing it all as a heading is intriguing and I'll have a look at that too, although formatting some longer text fields might be an issue. And I suspect that sooner or later the users will demand that the output be in spreadsheet format.
djones' gives me all of the values for CAR but not the word CAR itself as the variable. For his solution to work I would need CAR, BUS, TRUCK as the value, not ALFA, BMW, DATSUN etc. I think Tim's idea would end up looking similar.
The other solutions suggested are all things I thought of, but in each case they depend on the value of the field rather than the fieldname itself. Daniel's solution allows me to re-create the fieldname as a variable, which is exactly what I need.
I'll mark this closed once I verify that I have a solution.
Thanks again all ...This message has been edited. Last edited by: George Patton,
Thanks again to everyone who helped me out on this.
Here is what I have developed so far (work interrupted by some day surgery yesterday). I have taken Daniel's idea and extended it to derive the category names and from there the sort sequence so that Travel appears before Accommodation before Other. I've added a segment in the FOR02 master to allow for the fact that a sales rep can accrue expenses in more than one country on a given date.
Going this route has spared me from Financial Modelling Language, which has some nice features, but also some restrictions to what I am trying to accomplish.
The next challenge is to associate the method of payment (commented out below) and a comments field with each of the numerical values such that next to Entertainment, for example, I will show the name of the customer who was wined and dined at company expense. I suspect this will involve a parallel exercise followed by a JOIN or MATCH file. However I'm also going to take a look at creating an IBM Notes view and pulling the report from there instead of directly from the Notes document itself. That may give me greater control of the information.
APP HOLD BASEAPP
DEFINE FILE LN_EXPENSE_DAILY
AIRFARE/D16.2=AIRFAREUSD;
CARRENTAL/D16.2=CARRENTALUSD;
TAXI/D16.2=TAXIUSD;
CAREXP/D16.2=CAREXPENSEUSD;
PARKING/D16.2=PARKINGUSD;
OTHTRAVEL/D16.2=OTHTRAVELUSD;
HOTEL/D16.2=HOTELUSD;
BFAST/D16.2=BREAKFASTUSD;
LUNCH/D16.2=LUNCHUSD;
DINNER/D16.2=DINNERUSD;
ENTERTAIN/D16.2=ENTERTAINUSD;
OTHACCOM/D16.2=OTHACCOMUSD;
PURCHASE/D16.2=OTHPURCHUSD;
MISC/D16.2=OTHMISCUSD;
TIPS/D16.2=TIPSUSD;
CTRY_COUNT/A2=COUNTRY;
END
TABLE FILE LN_EXPENSE_DAILY
SUM
AIRFARE CARRENTAL TAXI CAREXP PARKING OTHTRAVEL
HOTEL BFAST LUNCH DINNER ENTERTAIN OTHACCOM
PURCHASE MISC TIPS
-*AIRFAREMETHOD CARRENTMETHOD TAXIMETHOD CAREXPMETHOD PARKINGMETHOD OTHTRAVELMETHOD
BY EXPENSEKEY
BY DOCUMENTAUTHOR
BY EXPENSEDATE
BY COUNTRY
WHERE DOCUMENTAUTHOR EQ 'Axxxx Mxxxxx'
ON TABLE HOLD AS FOR02 FORMAT ALPHA
END
-RUN
EX -LINES 11 EDAPUT MASTER,FOR02,C,MEM
FILENAME=FOR02, SUFFIX=FIX, $
SEGNAME=FOR02, SEGTYPE=S0, $
FIELDNAME=EXPENSEKEY, ALIAS=EXPENSEKEY, USAGE=A50, ACTUAL=A50, $
FIELDNAME=DOCUMENTAUTHOR, ALIAS=DOCUMENTAUTHOR, USAGE=A40, ACTUAL=A40, $
SEGNAME=EXPDT, SEGTYPE=S0, PARENT=FOR02, OCCURS=VARIABLE, $
FIELDNAME=EXPENSEDATE, ALIAS=EXPENSEDATE, USAGE=HYYMDs, ACTUAL=A17, $
FIELDNAME=COUNTRY, ALIAS=COUNTRY, USAGE=A2, ACTUAL=A2, $
SEGNAME=EXP, SEGTYPE=S0, PARENT=EXPDT, OCCURS=15, $
FIELDNAME=EXPENSE, ALIAS=EXP, USAGE=D16.2, ACTUAL=A16, $
FIELDNAME=EXPTYPE, ALIAS=ORDER, USAGE=I2, ACTUAL=I4,$
-RUN
DEFINE FILE FOR02
EXPDATE/YYMD=HDATE(EXPENSEDATE, 'YYMD');
EXPNAME/A30=DECODE EXPTYPE(
1 'Air Fare'
2 'Car Rental'
3 'Taxi'
4 'Car Expense'
5 'Parking / Tolls'
6 'Other Travel Expense'
7 'Hotel'
8 'Breakfast'
9 'Lunch'
10 'Dinner'
11 'Entertainment'
12 'Other Accommodation / Meals'
13 'Other Purchases'
14 'Miscellaneous Services'
15 'Tips'
);
EXPCATG/A30=DECODE EXPTYPE(
1 'Travel'
2 'Travel'
3 'Travel'
4 'Travel'
5 'Travel'
6 'Travel'
7 'Accommodation and Meals'
8 'Accommodation and Meals'
9 'Accommodation and Meals'
10 'Accommodation and Meals'
11 'Accommodation and Meals'
12 'Accommodation and Meals'
13 'Other Purchases / Services'
14 'Other Purchases / Services'
15 'Other Purchases / Services'
);
DISPL_ORDER/A1=DECODE EXPCATG('Travel' '1'
'Accommodation and Meals' '2'
'Other Purchases / Services' '3');
END
TABLE FILE FOR02
SUM
FOR02.EXP.EXPENSE AS 'Amount'
BY FOR02.EXPDT.EXPDATE NOPRINT
BY FOR02.EXPDT.COUNTRY NOPRINT
BY FOR02.EXP.DISPL_ORDER NOPRINT
BY FOR02.EXP.EXPCATG AS ''
BY FOR02.EXP.EXPNAME AS ''
ON FOR02.EXPDT.EXPDATE SUBHEAD
"<EXPDATE <COUNTRY "
ON FOR02.EXPDT.EXPDATE SUBFOOT
" "
ON FOR02.EXP.EXPCATG SUBTOTAL AS 'Total '
ON FOR02.EXP.EXPCATG SUBFOOT
" "
WHERE EXPENSE NE 0;
ON TABLE NOTOTAL
ON TABLE PCHOLD FORMAT EXL07
ON TABLE SET STYLE *
INCLUDE = PROBLUE,
$
TYPE=DATA,
COLUMN=N4,
SIZE=9,
STYLE=BOLD,
$
ENDSTYLE
END
Thanks again,
GeorgeThis message has been edited. Last edited by: George Patton,
Daniel In Focus since 1982 wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF
March 06, 2015, 09:28 AM
George Patton
As a final comment -thanks again Daniel for pointing me in the right direction - I'll just add these notes, in case anyone else is using the Lotus Notes connector and has a similar situation.
As I mentioned above, the remaining difficulty was to associate the method of payment (Amex, Visa, Cash) and any additional comment with each individual expense because all the expenses are on a single form and are not separate transactions as you might find in a different system.
I changed the data source from the Form (Lotus-speak for a Record) to a View that I created that contains all the fields from the form. In the view I changed all the numeric values to alpha and concatenated them with the method of payment and the associated comment.
In WF I read the entire record from the view into a hold file and then deconstruct the concatenated fields. This has the advantage of reading the source file a single time and doing all the rest of the processing in WF. Since the concatenated fields have the correct method of payment and comment associated, getting them lined up in WF is easy.