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...
NewbieGirl777

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.

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

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:

• http://techsupport.informationbuilders.com
  (From the top navigation bar, click Publications. Then select i-Base: Tech Doc Library.)
  
• www.informationbuilders.com
  (From the top navigation bar, click Services & Support. Then select Documentation.)
  
• www.iwaysoftware.com
  (From the top navigation bar, click Services & Support. Then select Bookstore.)
  

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

Hope this helps.

Regards,
Jennifer

Thanks, Jennifer. That helps a lot.

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