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     Data manipulation after table request

Read-Only Read-Only Topic
Go
Search
Notify
Tools
Data manipulation after table request
 Login/Join
 
NewbieGirl777
Member
posted
Hi,

I'm a newbie and have been assigned FOCUS responsibilities last week. I have an old manual, but that's about it. we're using FOCUS 6.8 on a UNIX mainframe system.

I need to generate a report that retrieves records from a Purchasing database, but in the case of duplicate records, I have to choose a specific one to print.

The problem I'm having is that I don't know how to move data around, or loop through the data using certain key fields in order to zero in on the records I need. The manual only gives examples of doing ths through Dialogue Manager when the user is prompted and supplies a value.

The focexec I'd be using is one that would just run from the command line in native FOCUS,using ex
Here is an example of records, using the Product Code, Requisition #, Requisition Date, Dept. Requesting Item, Quantity, and Unit Cost:

PCode Req# ReqDt Dept Qty Cost
----- ---- ----- ---- --- ----
P1111 110 060123 702 43 26.05
P2222 307 060202 512 33 27.10
P3333 417 061221 310 125 27.50
P3333 417 061221 501 115 27.50
P3333 417 061221 122 30 28.20
P4444 207 060420 310 79 26.05

What I need to retrieve from the DB is one line for each Product Code. The record contain the Product Code, the Req# with the latest ReqDt. If there are multiple records with the same Req# and same ReqDt, the next selection criteria would be to choose the one with the lowest Unit Cost. If there are still multiple records, the next selection criteria would be to choose the one with the lowest Qty.

The report should look like this:

PCode Req# ReqDt Dept Qty Cost
----- ---- ----- ---- --- ----
P1111 110 060123 702 43 26.05
P2222 307 060202 512 33 27.10
P3333 417 061221 122 115 27.50
P4444 207 060420 310 79 26.05

There are other fields that would appear on the report, interspersed between the fields I've listed.

I've tried using MIN and MAX, but they sum all the costs and put the total on the record. Can someone help?

Thanks... Confused
NewbieGirl777
 
Posts: 9 | Location: Orlando, FL | Registered: December 11, 2006Report This Post
Francis Mariani
Expert
posted Hide Post
To help you get off the ground, the following code should select only the rows you want in your report

TABLE FILE PROD_FILE
PRINT
many fields
BY PCODE
BY LOWEST 1 COST
BY LOWEST 1 QTY
END

"BY HIGHEST/LOWEST n sortfield" restricts how many rows are included in the report. Unfortunately, according to the manual,
quote:
HIGHEST/LOWEST n refers to the number of sort field values, not the number of report rows. If several records have the same sort field value that satisfies the HIGHEST/LOWEST n criteria, all of them are included in the report
but this should help.

By the way, if you are registered with IBI Support, you can download free documentation in Windows Help or PDF format. You do need to know exactly which version you have.


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
NewbieGirl777
Member
posted Hide Post
Thanks, Francis! That helped. Regarding the free documentation, I believe we are registered with IBI support, but I couldn't find any free documentation--only documentation to be purchased. Do you have a link to the page that has the free downloads?

Thanks,
NewbieGirl777
 
Posts: 9 | Location: Orlando, FL | Registered: December 11, 2006Report This Post
<DocServices>
posted
Hi NewbieGirl77,


You can view and/or download manuals from the i-Base: The Technical Documentation Library.

If you have an InfoResponse ID, log on to the Tech Support Web site and then access i-Base. By logging in first, you can download the PDF file and/or view the HTMLHelp version.

You can access i-Base via the following Web sites:

You can also access the following reference guide which demonstrates the features of i-Base: i-Base Brochure

Hope this helps.

Regards,
Jennifer
 
Report This Post
NewbieGirl777
Member
posted Hide Post
Thanks, Jennifer. That helps a lot. Smiler

I'm a little confused as to how to process records once they are retrieved from the database. For example, if I want to select some records, then use one field on each of those records to do perform some process such as looping or a performing a case statement, how do I incorporate that type of code (loop, case, etc.) into a focexec? If the "table file production" is for report generation, what type of focexec is used to just perform processes on data, not for reporting purposes? Are there two parts to FOCUS--a reporting part and another part that allows you to run batch process, assign values to variables, loop, update files, etc.?

Thanks,
NewbieGirl777
 
Posts: 9 | Location: Orlando, FL | Registered: December 11, 2006Report 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     Data manipulation after table request

Copyright © 1996-2020 Information Builders