[SOLVED] MODIFY FILE ON MATCH nulls

I have a web form where users can update data that is in a SQL table. Periodically, I plan to take an extract of that updated data. If someone changes the data, my update process works except if the data is removed (changed to null or ' ').
Here's the code I am using to update my table NE16UPDT:

MODIFY FILE NE16UPDT
FIXFORM FROM NE16FINA
MATCH LP_ID
ON MATCH UPDATE WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD
ON NOMATCH INCLUDE
DATA ON NE16FINA
END

Once any one of these attributes has a value in the source, it updates NE16UPDT unless the value in the source (NE16FINA) is removed.

I was reading in a prior post that I might need to have MISSING=ON in my MFD for each attribute. Or perhaps there's some code or set commands in my fex that is needed.

thanks all

Bob V

This message has been edited. Last edited by: BobV,

WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8

Posts: 93 | Registered: February 20, 2008

GamP

Virtuoso

posted

Hide Post

Bob,
MODIFY is a thing from the past with sometimes an unexpected behaviour.
As in this case, when the source values are removed.
I am assuming the key value for LP_ID is still there in the file.
What happens is:
- the fixform reads the data values from the source
- key value LP_ID is present and it matches that value in the database
- there is a match, so the other fields need to be updated
- but there is no value for any one of those fields, so nothing happens.
You'll have to force a value to be present for the modify to update.
Point is, that the fixform command is a so-called 'conditional' read.
Meaning that the field gets activated only if a non-zero (or null) value is present.
You could try to give the command
ACTIVATE RETAIN WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD
to force these field sto be activated. Or, if that does not have the desired result, you'll have to resort to using the compute command, thus activating the field(s).

GamP

- Using AS 8.2.01 on Windows 10 - IE11.
	in Focus since 1988

Posts: 1961 | Location: Netherlands | Registered: September 25, 2007

Ignored post by GamP posted

Show Post

Danny-SRL

Virtuoso

posted

Hide Post

Bob,
True, MODIFY, has been around for a long time and it has its quirks. Nonetheless, it is very useful.
For your problem, I would suggest you use an explicit FIXFORM:

MODIFY FILE NE16UPDT
FIXFORM LP_ID/fmt WRKSHP_DT_YY/fmt WRKSHP_DT_MM/fmt ...
MATCH LP_ID 
ON MATCH UPDATE WRKSHP_DT_YY WRKSHP_DT_MM WRKSHP_DT_DD APPT_DT_YY APPT_DT_MM APPT_DT_DD
ON NOMATCH INCLUDE
DATA ON NE16FINA
END

where /fmt are the ACTUAL formats of each field.

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, 2006

Ignored post by Danny-SRL posted

Show Post

jgelona

Master

posted

Hide Post

Bob,

To get the answer you need, you will need to post the masters for NE16UPDT and NE16FINA.

MISSING is the same as NULL and a field attribute of MISSING=ON says NULL are allowed. If MISSING=ON is not present, the value will be zero or blank depending of the field format if no values are present.

We use Oracle and I don't have any problem using MODIFY to update Oracle fields where NULL is allowed with NULL.

In FOCUS since 1985. Prod WF 8.0.08 (z90/Suse Linux) DB (Oracle 11g), Self Serv, Report Caster, WebServer Intel/Linux.

Posts: 975 | Location: Oklahoma City | Registered: October 27, 2006

Ignored post by jgelona posted

Show Post

BobV

Gold member

posted

Hide Post

Thanks to all who replied with advice.

GamP, I took your suggestion of using COMPUTE... in the MODIFY and searched Focal Point to find where to place ACTIVATE RETAIN (rather than bother you). I found a post with a reply from Francis Mariani the gave sample code for the compute that looked like this:

MODIFY FILE NE16UPDT
FIXFORM FROM NE16FINA
MATCH LP_ID
ON MATCH COMPUTE WRKSHP_DT_YY = IF WRKSHP_DT_YY EQ '' THEN '' ELSE WRKSHP_DT_YY;
ON NOMATCH INCLUDE
DATA ON NE16FINA
END

this worked. I also tested to see if MISSING would work and it did:
ON MATCH COMPUTE WRKSHP_DT_YY MISSING ON = IF WRKSHP_DT_YY EQ MISSING THEN MISSING ELSE WRKSHP_DT_YY;

I did an ON MATCH COMPUTE for all 6 of the attributes I need to update. Not sure if I need to use ON MATCH before each COMPUTE, but it works w/o errors.

Here is the post:
http://forums.informationbuild...871088331#2871088331

Finally, the first thing I did was edit the MFD to MISSING=ON for each attribute. Alone this did not work, but perhaps it's needed in addition to the COMPUTE code I am now using.

thanks again to all.

BobV

This message has been edited. Last edited by: BobV,

WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8

Posts: 93 | Registered: February 20, 2008

Ignored post by BobV posted

Show Post

BobV

Gold member

posted

Hide Post

SOLVED

WF (App Studio) 8.2.01m / Windows
Mainframe FOCUS 8

Posts: 93 | Registered: February 20, 2008