Focal Point
[SOLVED] MODIFY FILE ON MATCH nulls

This topic can be found at:
https://forums.informationbuilders.com/eve/forums/a/tpc/f/7971057331/m/3897040726

December 11, 2012, 05:15 PM
BobV
[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
December 12, 2012, 02:32 AM
GamP
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
December 12, 2012, 08:19 AM
Danny-SRL
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

December 12, 2012, 08:45 AM
jgelona
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.
December 12, 2012, 10:34 AM
BobV
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
December 12, 2012, 11:01 AM
BobV
SOLVED


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