Hi,

I'm trying to update a row in a FOC file.
Below is my code:

TABLE FILE rv_getequipmentcommentsasobjectarray_ws
PRINT
LINECODE
EQUIPMENTGROUPCODE
UNITNUMBER
STARTDATETIME/HYYMDS
UPDATEDATETIME
COMMENTS
SYMPTOM
PROBLEM
ACTION
SOLUTION
BY
LINECODE NOPRINT
BY
EQUIPMENTGROUPCODE NOPRINT
BY
UNITNUMBER NOPRINT
BY
STARTDATETIME NOPRINT

&WHR_TODATE1
ON TABLE HOLD AS EQPCMT
END

USE
D:\ibi-qa\ibi\apps_srv\sample\focfile.foc
END


MODIFY FILE focfile
FIXFORM FROM EQPCMT
MATCH LINECODE EQUIPMENTGROUPCODE UNITNUMBER STARTDATETIME
ON MATCH UPDATE COMMENTS SYMPTOM PROBLEM ACTION SOLUTION
DATA ON EQPCMT
END
-RUN

If I output the data on the EQPCMT hold file, I get data that looks okay. See sample below:

Linecode: F2
Equipmentgroupcode: APT
Unitnumber: 108
Startdatetime: 2006/12/25 06:30:05

When I run the code in full (including the update portion), it errors out. See sample message below:

TRANS 1 REJECTED NOMATCH SEG01
F2 APT 108 2 ) ÁaH 2 ) tež
E tool needs muffle etch........
. -ABC, DEF

The linecode, equipmentgroupcode and unitnumber are readable but the startdatetime looks garbled. I have 181 rows to update and all of them got REJECTED. Is there something I missed?

Thanks for the help.

Try:

ON TABLE HOLD AS EQPCMT FORMAT ALPHA

i concur with Melbourne. that seems to be the clearest thing missing from your code;

You're reformating on the field. By default, the hold file will contain both the datetime field as it existed before, and reformated.

Reformat you STARTDATETIME field in a define or add the following to you focexec to prevent the origional format of STARTDATETIME from being held.

ON TABLE SET HOLDLIST PRINTONLY

Hi Piipster & susannah,

I tried your suggestion but it did not work. It still rejected all the rows. I'm expecting the exact number of rows to be modified since the information is already in the file.

It just occured to me that focfile is actually an XFOCUS file. We need that to be due to the data that we need. The output size for focfile is 2.91 GB.

Below is the MAS file for focfile:

FILENAME=focfile, SUFFIX=XFOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST , ALIAS=E01, USAGE=I9, $
FIELDNAME=CUTOFFDATE, ALIAS=E02, USAGE=I8YYMD, $
FIELDNAME=LINECODE, ALIAS=E03, USAGE=A2, FIELDTYPE=I, $
FIELDNAME=EQUIPMENTGROUPCODE, ALIAS=E04, USAGE=A3, $
FIELDNAME=UNITNUMBER, ALIAS=E05, USAGE=I6,
MISSING=ON, $
FIELDNAME=STARTDATETIME, ALIAS=E06, USAGE=HYYMDS,
MISSING=ON, $
FIELDNAME=ENDDATETIME, ALIAS=E07, USAGE=HYYMDS,
MISSING=ON, $
FIELDNAME=STATUS, ALIAS=E08, USAGE=I6,
MISSING=ON, $
FIELDNAME=STATUSDESCRIPTION, ALIAS=E09, USAGE=A30, $
FIELDNAME=CONDITIONCODE, ALIAS=E10, USAGE=I6,
MISSING=ON, $
FIELDNAME=CONDITIONTYPE, ALIAS=E11, USAGE=A1, $
FIELDNAME=FAILURECODE, ALIAS=E12, USAGE=I6,
MISSING=ON, $
FIELDNAME=FAILURECODEDESCRIPTION, ALIAS=E13, USAGE=A20, $
FIELDNAME=EMPLOYEENUMBER, ALIAS=E14, USAGE=I11,
MISSING=ON, $
FIELDNAME=EMPLOYEENAME, ALIAS=E15, USAGE=A30, $
FIELDNAME=LOTNUMBER, ALIAS=E16, USAGE=A12, FIELDTYPE=I, $
FIELDNAME=WAFERQUANTITY, ALIAS=E17, USAGE=I4, $
FIELDNAME=DIEQUANTITY, ALIAS=E18, USAGE=I11,
MISSING=ON, $
FIELDNAME=QUANTITYTYPE, ALIAS=E19, USAGE=A1, $
FIELDNAME=MACHINESTATUS, ALIAS=E20, USAGE=A6, $
FIELDNAME=PRODCUCTIONCOMMENT, ALIAS=E21, USAGE=A1, $
FIELDNAME=LINEMAINTENANCECOMMENT, ALIAS=E22, USAGE=A1, $
FIELDNAME=STEPCODE, ALIAS=E23, USAGE=A6, $
FIELDNAME=CONDITIONKEY, ALIAS=E24, USAGE=A4, $
FIELDNAME=BASEPRODUCT, ALIAS=E25, USAGE=A30, $
FIELDNAME=UPDATEDATETIME, ALIAS=E26, USAGE=HYYMDS,
MISSING=ON, $
FIELDNAME=COMMENTS, ALIAS=E27, USAGE=A512V, $
FIELDNAME=SYMPTOM, ALIAS=E28, USAGE=A512V, $
FIELDNAME=PROBLEM, ALIAS=E29, USAGE=A512V, $
FIELDNAME=ACTION, ALIAS=E30, USAGE=A512V, $
FIELDNAME=SOLUTION, ALIAS=E31, USAGE=A512V, $

Below is the MAS file for rv_getequipmentcommentsasobjectarray_ws (source of hold file EQPCMT:

FILENAME=M6ILO, SUFFIX=SOAP , $
SEGMENT=GETEQUIPMENTCOMMENTSASOBJECTARRAY, SEGTYPE=S0, $
GROUP=GETEQUIPMENTCOMMENTSASOBJECTARRAY, ALIAS=GetEquipmentCommentsAsObjectArray, USAGE=A60, ACTUAL=A60, $
FIELDNAME=STRFROMDATE, ALIAS=strFromDate, USAGE=A30, ACTUAL=A30, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=STRTODATE, ALIAS=strToDate, USAGE=A30, ACTUAL=A30, ACCESS_PROPERTY=(NEED_VALUE), $
FIELDNAME=__RESPONSE, USAGE=TX80, ACTUAL=TX, ACCESS_PROPERTY=(INTERNAL), $
SEGMENT=RESPONSE, SEGTYPE=S0, SEGSUF=XML , PARENT=GETEQUIPMENTCOMMENTSASOBJECTARRAY, POSITION=__RESPONSE, $
FIELDNAME=RESPONSE, ALIAS=GetEquipmentCommentsAsObjectArrayResponse, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL), $
FIELDNAME=GETEQUIPMENTCOMMENTSASOBJECTARRAYRESULT, ALIAS=GetEquipmentCommentsAsObjectArrayResult, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
REFERENCE=RESPONSE, PROPERTY=ELEMENT, $
SEGMENT=EQUIPMENTCOMMENTSRESPONSE, SEGTYPE=S0, PARENT=RESPONSE, $
FIELDNAME=EQUIPMENTCOMMENTSRESPONSE, ALIAS=EquipmentCommentsResponse, USAGE=A1, ACTUAL=A1, ACCESS_PROPERTY=(INTERNAL),
REFERENCE=GETEQUIPMENTCOMMENTSASOBJECTARRAYRESULT, PROPERTY=ELEMENT, $
FIELDNAME=LINECODE, ALIAS=LineCode, USAGE=A2, ACTUAL=A2,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=EQUIPMENTGROUPCODE, ALIAS=EquipmentGroupCode, USAGE=A3, ACTUAL=A3,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=UNITNUMBER, ALIAS=UnitNumber, USAGE=A3, ACTUAL=A3,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=STARTDATETIME, ALIAS=StartDateTime, USAGE=HYYMDS, ACTUAL=A35,
MISSING=ON,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=UPDATEDATETIME, ALIAS=UpdateDateTime, USAGE=HYYMDS, ACTUAL=A35,
MISSING=ON,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=COMMENTS, ALIAS=Comments, USAGE=A512V, ACTUAL=A512V,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=SYMPTOM, ALIAS=Symptom, USAGE=A512V, ACTUAL=A512V,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=PROBLEM, ALIAS=Problem, USAGE=A512V, ACTUAL=A512V,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=ACTION, ALIAS=Action, USAGE=A512V, ACTUAL=A512V,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $
FIELDNAME=SOLUTION, ALIAS=Solution, USAGE=A512V, ACTUAL=A512V,
REFERENCE=EQUIPMENTCOMMENTSRESPONSE, PROPERTY=ELEMENT, $

It looks like your focus database that you're modifying is a result of a HOLD FORMAT FOCUS request and not a real Focus Database that was created with the CREATE command. It has a field called FOCLIST which is created with the HOLD FORMAT FOCUS file. If you need to make this a real Focus Database, eliminate FOCLIST from the master and CREATE your file with CREATE FILE FILENAME (back up your file first)

Then try to modify.

Good Luck!

Hi Carol,

Actually the file was initially created using a HOLD FORMAT XFOCUS request.

Let me try your suggestion and I will let you know.

Thanks,

Hi Carol,

I tried your suggestion and it still did not work. I need FOCLIST (as a key) to delete records later in the code. We are doing implementing FIFO on this object. The requirement is to keep 35 days of data. Since it takes 1+ hour to run, we initialize it with 35 days and have a daily job that gets daily data and appends to the file and delete the oldest data from the file (36th day).

I used that CAR file in this example to show the code. What am I doing wrong? I simply want to change the bodytype and seats for ENGLAND TRIUMPH TR7 HARDTOP 2 to SOFTTOP 5. Here's the code and the MAS file for the foc file.

MAS File: Note the format is XFOCUS
FILENAME=CAR_FOC , SUFFIX=XFOC , $
SEGMENT=SEG01, SEGTYPE=S1, $
FIELDNAME=FOCLIST , ALIAS=E01, USAGE=I5, $
FIELDNAME=COUNTRY, ALIAS=E02, USAGE=A10, $
FIELDNAME=CAR, ALIAS=E03, USAGE=A16, $
FIELDNAME=MODEL, ALIAS=E04, USAGE=A24, $
FIELDNAME=BODYTYPE, ALIAS=E05, USAGE=A12, $
FIELDNAME=SEATS, ALIAS=E06, USAGE=I3, $

FEX:
-* File test_carfoc.fex

-SET ECHO=ALL;

-SET &MAXLIST = 0;

TABLE FILE CAR
PRINT
COMPUTE CNT/I5 = CNT + 1 ;
COMPUTE FOCLIST/I9=&MAXLIST + CNT ;
COUNTRY
CAR
MODEL
BODYTYPE
SEATS
ON TABLE HOLD AS HOLD1
END


TABLE FILE HOLD1
PRINT
FOCLIST
COUNTRY
CAR
MODEL
BODYTYPE
SEATS
ON TABLE HOLD AS HOLD2
END


USE
D:\ibi-dev\ibi\apps_srv\dan_test\car_foc.foc NEW
END

CREATE FILE car_foc

MODIFY FILE car_foc
FIXFORM FROM HOLD2
DATA ON HOLD2
END

TABLE FILE CAR_FOC
PRINT *
END


DEFINE FILE CAR
DEF_BT/A12 = 'SOFTTOP';
DEF_S/I3 = 5;
END

TABLE FILE CAR
PRINT
COUNTRY
CAR
MODEL
DEF_BT
DEF_S
WHERE COUNTRY EQ 'ENGLAND';
WHERE CAR EQ 'TRIUMPH';
WHERE MODEL EQ 'TR7';
ON TABLE HOLD AS HOLD3 FORMAT ALPHA
END


USE
D:\ibi-dev\ibi\apps_srv\dan_test\car_foc.foc
END


MODIFY FILE car_foc
FIXFORM COUNTRY/10 CAR/16 MODEL/24
FIXFORM BODYTYPE/12 SEATS/3
MATCH COUNTRY CAR MODEL
ON MATCH UPDATE BODYTYPE SEATS
DATA ON HOLD3
END
-RUN

TABLE FILE CAR_FOC
PRINT *
END

-EXIT

Thanks,

Here's a snippet of the source from the above code:

0 NUMBER OF RECORDS IN TABLE= 1 LINES= 1
0 ERROR AT OR NEAR LINE 82 IN PROCEDURE ADHOCRQ FOCEXEC *
(FOC439) WARNING. A MATCH CONDITION HAS BEEN ASSUMED FOR: FOCLIST
(FOC415)TRANS 1 REJECTED NOMATCH SEG01
ENGLAND TRIUMPH TR7 SOFTTOP 5
0 TRANSACTIONS: TOTAL = 1 ACCEPTED= 0 REJECTED= 1
SEGMENTS: INPUT = 0 UPDATED = 0 DELETED = 0
TABLE FILE CAR_FOC
PRINT *
END
-EXIT
0 NUMBER OF RECORDS IN TABLE= 18 LINES= 18

You are getting the error because you have forgotten that FOCLIST is your Key Field. You need to have a value for and then MATCH on FOCLIST in order to update the record.

Your code could also be much simpler but that is a separate issue.

OPALTOSH,

Is it because FOCLIST is before the other fields, therefore makes it a key field?
Can't I use any field/s to do my MATCH?

I've updated the MAS file to make it a FOCUS format and removed the key on FOCLIST, no other keys. I ran the FEX and it worked. The row got updated.

Is there a limitation with the XFOCUS format. I did the same this with a XFOCUS format and no keys? It did not update the row. I need to use XFOCUS since the data we are keeping is close to 3GB.

The number of keys is determined by the SEGTYPE parameter. e.g SEGTYPE=S! means the first field named in the segment is the key, S2 means the first 2 fields named make up the key and so on.
You can create the segment with as many key fields as you need to create a unique identifier by setting the SEGTYPE to the correct value and putting the key fields first in the MAS file.

OPALTOSH,

Thanks for the note. I created a case with IBI yesterday on updating XFOCUS files. I was able to re-create the issue with the CAR file and hopefully can get a response from them soon.

Thanks,

dballest,

Just an FYI....

If you need FOCSORT for the update and your file is an S1 (like OPALTOSH explained)
One little trick you can try is to match on the non key fields also.


Your code should say

MATCH FOCSORT
ON MATCH UPDATE whatever

or....

MATCH FOCSORT nonkey1 nonkey2 nonkey3
ON MATCH update whatever

You were trying to match on the non key fields and omitted the key field from the request.

Good Luck!

in my experience, FOCLIST field is created not because of HOLD FORMAT FOCUS but because of the use of the PRINT verb rather than a SUM verb when tableing to make that HOLD FORMAT FOCUS.
just fyi.

Thank you for the responses while I was away. I'll give them a try and will let you know if it works.

Thanks to all who replied. IBI techsupport helped me resolved it. Here's the code I got from them:

-SET &MAXLIST = 0;

TABLE FILE CAR
PRINT
COMPUTE CNT/I5 = CNT + 1 ;
COMPUTE FOCLIST/I9=&MAXLIST + CNT ;
COUNTRY CAR MODEL BODYTYPE SEATS
ON TABLE HOLD AS HOLD1
END
-RUN

TABLE FILE HOLD1
PRINT FOCLIST COUNTRY CAR MODEL BODYTYPE SEATS
ON TABLE HOLD AS HOLD2
END
CREATE FILE CARFOC
-RUN

MODIFY FILE CARFOC
FIXFORM FROM HOLD2
DATA ON HOLD2
END
-RUN

TABLE FILE CARFOC
PRINT *
END
-RUN

DEFINE FILE CARFOC
DEF_BT/A12 = 'SOFTTOP';
DEF_S/I3 = 5;
END
TABLE FILE CARFOC
PRINT FOCLIST COUNTRY CAR MODEL DEF_BT DEF_S
WHERE COUNTRY EQ 'ENGLAND';
WHERE CAR EQ 'TRIUMPH';
WHERE MODEL EQ 'TR7';
ON TABLE SAVE AS SAVE03
END
-RUN

MODIFY FILE CARFOC
FIXFORM FOCLIST/5 COUNTRY/10 CAR/16 MODEL/24
FIXFORM BODYTYPE/12 SEATS/3
MATCH FOCLIST
ON NOMATCH REJECT
ON MATCH UPDATE BODYTYPE SEATS
DATA ON SAVE03
END
-RUN

TABLE FILE CARFOC
PRINT *
END

We did a diffent approach to use FOCLIST as the key when we update and was able to hurdle the problem with are having with the MATCH. After running the MATCH command, it says it update X number of rows. When we do a TABLE file for that row, it was indeed updated but prefixed with a 6 digit random (not sure it it is) number.

For example, before the MATCH, the comments field for FOCLIST 1 is "This is just a test". After the MATCH, it became "000074This is just a test". Fields that are blank have random 6 digit number and originally blank fields have been updated to 000512

Hi All,

Pls. disregard my earlier post. The problem has been resolve. We have a field that is of format A512V. This caused our MATCH process to slow down and append the random number in front of the text. We created a define field with format A512 and assigned the field (A512V). The update worked fine!

Thanks for all your help!