Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create group field to describe several different individual fields

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] Create group field to describe several different individual fields
 Login/Join
 
Master
posted
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,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Platinum Member
posted Hide Post
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  


WF 7.6.11
Output: HTML, PDF, Excel
 
Posts: 123 | Location: UK | Registered: October 09, 2003Report This Post
Member
posted Hide Post
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
 
Posts: 5 | Registered: November 26, 2014Report This Post
Virtuoso
posted Hide Post
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

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Member
posted Hide Post
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
 
Posts: 21 | Location: Crystal City, VA & Kalamazoo, MI | Registered: October 11, 2010Report This Post
Master
posted Hide Post
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,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Master
posted Hide Post
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,


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
Virtuoso
posted Hide Post
Nice going, George!
Good One


Daniel
In Focus since 1982
wf 8.202M/Win10/IIS/SSA - WrapApp Front End for WF

 
Posts: 1980 | Location: Tel Aviv, Israel | Registered: March 23, 2006Report This Post
Master
posted Hide Post
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


WebFOCUS 7.7.05 Windows, Linux, DB2, IBM Lotus Notes, Firebird, Lotus Symphony/OpenOffice. Outputs PDF, Excel 2007 (for OpenOffice integration), WP
 
Posts: 674 | Location: Guelph, Ontario, Canada ... In Focus since 1985 | Registered: September 28, 2010Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] Create group field to describe several different individual fields

Copyright © 1996-2020 Information Builders