I have created a Travel Expense application in Lotus IBM Notes.

Each record (or "Document" in Lotus-speak) has a number of the fields that you would expect in such an application such as:

AIR_FARE
TAXI
PARKING
HOTEL
MEALS
etc.

My problem is that I want to have output that looks something like this:

Travel
          AIR_FARE     $500.00
          TAXI         $ 40.00
          PARKING            .

Accommodation
          HOTEL        $160.00
          MEALS        $ 55.00

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,

Would something like this work:

TABLE FILE CAR
HEADING
"Fields1:"
"DEALER_COST : <TOT.DEALER_COST"
"WEIGHT : <TOT.WEIGHT"
" "
"Fields2:"
"RETAIL_COST: <TOT.RETAIL_COST"
"SALES: <TOT.SALES"
END  

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.

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

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

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,

George

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

Nice going, George!

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.

George