As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only. Moving forward, myibi is our community platform to learn, share, and collaborate. We have the same Focal Point forum categories in myibi, so you can continue to have all new conversations there. If you need access to myibi, contact us at email@example.com and provide your corporate email address, company, and name.
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.
Bob VThis message has been edited. Last edited by: BobV,
WF (App Studio) 8.2.01m / Windows Mainframe FOCUS 8
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).
- Using AS 8.2.01 on Windows 10 - IE11.
in Focus since 1988
Posts: 1961 | Location: Netherlands | Registered: September 25, 2007
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 ...
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
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
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
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.