First look at the file dictionary to see what the fields are and how they
are defined. As you probably read, PICK uses multi-values within a field
which are separated with a delimiter. If there are related fields, they
will be defined in the file dictionary field 4 as being controlling or
dependant. An example is an invoice line which contains a part #, the
description and the price. If you have 5 lines of these, you don't want to
delete the first part number without deleting the associated description and
price. If I understand correctly, you are getting the price and quantity
fields printed when all you want is the records containing that part? If
1) SELECT FILE.NAME WITH PART = "0820"
2) SAVE-LIST (OR SL) PART.0820.LIST (or PARTS or any other unique name.
This is just a name for you to be able to bring up the same records in the
3) GET-LIST (or GL) PART.0820.LIST will activate only those records that
have part # 0820 in them
4) SORT ONLY FILE-NAME (P will print a list of those records.
If the part is one of a number of parts in the multi-value field and you
wish to print more info on those records but only print info on part 0820
then use BY-EXP as was suggested previously. For example to get a list of
customers who bought that part and what they paid and how many they bought,
you would start with the same list you saved above and type the following:
3) GET-LIST PART.0820.LIST
4) SORT FILE.NAME BY CUST.NAME BREAK-ON CUST.NAME BY DATE BY-EXP PART =
"0820" PART DESCRIPTION TOTAL QTY PRICE (P
The (P sends the report to the printer, leaving it off will send the report
to the screen. The BREAK-ON CUST.NAME will subtotal any field with a TOTAL
in front of it for that customer so you will get a report that looks like
RECORD.ID CUST.NAME PART DESCRIPTION
12566 SMITH, JOHN 0820 MAPLE CHAIR
12768 SMITH, JOHN 0820 MAPLE CHAIR
12488 TAYLOR,* 0820 MAPLE CHAIR
13252 ZABRISKI, TOM 0820 MAPLE CHAIR
13261 ZABRISKI, TOM 0820 MAPLE CHAIR
and so on.
Putting a " 'DL' " after the BREAK-ON CUST.NAME will eliminate the total
line for customers with only one invoice and will delete the space line
after the total if the report is long and you wish to conserve space.
Regarding your second problem, try WITH 17 GE 1.1.96 AND < 1.1.97 (the
second WITH screwed you up). Note that in PICK you don't need to type
"01/01/96" or 01.01.96, when 1.1.96 will suffice and if you are in the
current year then 1.1 will define to 01.01.99 by default.
I hope I haven't been too wordy. Best of luck and if you want a good
reference I have found the books by Roger Bourdon to be excellent.
>>I am a Clipperhead, who recently got a call from an ex-Boss asking if
>>I could help with their Pick system. I know nothing about it, but he
>>knows I'm ususally good at learning quickly.
>>I went there and they have an IBM Rt, running PICK on AIX?
>>No Dos, one 160K Floppy, nobody left that knows anything about Pick,
>>no idea what files are on the system or what there layouts are, but
>>the higher ups need a report with sales data from 3 years ago.
>>I hit the manual and doped enough out to be able to locate the file I
>>need, and the structure, do some basic ACCESS queries and learn the
>>editor a bit.
>>1) I need to find records with part number 0820, and can do that, BUT
>>the items are seperated by a value mark:
>> as are the prices for each item and the qty. So when I try and
>>print, I have 3 lines, 2 of which I don't want .
>>How do I get ONLY the data associated with 0820?
>>2. For the life of me, I can't seem to get records for a date range.
>>If I type WITH 17 = "10/15/96", a single date, it works perfectly.
>>But if I try something like WITH 17 > "01/01/96" AND WITH 17 <
>>"01/01/97" I get ALL records. Same thing if I try and use WITH 17
>>BEFORE etc, etc. I even tried using the date values as they are in
>>the table, ie, 10582, or whatever, but no luck.
>>The above two are critical (Well, for ME, anyway!)
>>Would appreciate any help, this group or e-mail. Thanks!
>Check out the BY-EXP (exploding) clause. That should work nicely for you.
>Various implementations of Pick work a little differently for the BY-EXP
>clause. So you will have to check the manual and experiment a bit. Good