This example script updates fields in a table ONLY IF the source field is not blank/null. That way I can update the target if the user provided some fresh information, but not blank out the target if the user left some field blank on the intake form.
The arguments (e.g., :first_name) always return empty string if they are blank or null, so the NULLIF() forces them to be NULL if they are blank. The COALESCE() function returns the 'first non-null value'.
So for example the line...
gender = COALESCE(NULLIF(:gender,''),gender)
gender (left most) is the target field we want to update
COALESCE() returns the first non-null value from the list, which happens to contain :gender and gender.
NULLIF() forces the argument :gender to be NULL if it is an empty string (COALESCE works only on nulls).
:gender is the value from the source, set in args.add() section below.
gender (right most) contains the target field value.
This will return the Source gender field value if not null, but return the Target gender field value if the Source was null. Of course they could both be null.
FYI "errorout()" used below is my own user defined function that spits out error information if necessary.
The arguments (e.g., :first_name) always return empty string if they are blank or null, so the NULLIF() forces them to be NULL if they are blank. The COALESCE() function returns the 'first non-null value'.
So for example the line...
gender = COALESCE(NULLIF(:gender,''),gender)
gender (left most) is the target field we want to update
COALESCE() returns the first non-null value from the list, which happens to contain :gender and gender.
NULLIF() forces the argument :gender to be NULL if it is an empty string (COALESCE works only on nulls).
:gender is the value from the source, set in args.add() section below.
gender (right most) contains the target field value.
This will return the Source gender field value if not null, but return the Target gender field value if the Source was null. Of course they could both be null.
FYI "errorout()" used below is my own user defined function that spits out error information if necessary.
Code:
'==session.req_id is set to the request number somewhere above this line vSelect = "SELECT * FROM a_request WHERE id = {session.req_id}" vSelect = evaluate_string(vSelect) if cn.execute(vSelect)=.f. errorout(vSelect + "|"+ cn.callresult.text) end if vfirst_name = cn.resultset.data("first_name") vlast_name = cn.resultset.data("last_name") vdob = cn.resultset.data("dob") vage = cn.resultset.data("age") vgender = cn.resultset.data("gender") vrace = cn.resultset.data("race") vaddress = cn.resultset.data("address") vcity = cn.resultset.data("city") vstate = cn.resultset.data("state") vzipcode = cn.resultset.data("zipcode") vlanguage_spoken_home = cn.resultset.data("language_spoken_home") vschool = cn.resultset.data("school") vSelect = <<%longstring% UPDATE a_student SET first_name = COALESCE(NULLIF(:first_name,''),first_name), last_name = COALESCE(NULLIF(:last_name,''),last_name), dob = COALESCE(NULLIF(:dob,''),dob), age = COALESCE(NULLIF(:age,''),age), gender = COALESCE(NULLIF(:gender,''),gender), race = COALESCE(NULLIF(:race,''),race), address = COALESCE(NULLIF(:address,''),address), city = COALESCE(NULLIF(:city,''),city), state = COALESCE(NULLIF(:state,''),state), zipcode = COALESCE(NULLIF(:zipcode,''),zipcode), language_spoken_home = COALESCE(NULLIF(:language_spoken_home,''),language_spoken_home), school = COALESCE(NULLIF(:school,''),school) %longstring% args.add("first_name", vfirst_name) args.add("last_name", vlast_name) args.add("dob", vdob) args.add("age", vage) args.add("gender", vgender) args.add("race", vrace) args.add("address", vaddress) args.add("city", vcity) args.add("state", vstate) args.add("zipcode", vzipcode) args.add("language_spoken_home",vlanguage_spoken_home) args.add("school", vschool) if cn.execute(vSelect,args)=.f. errorout(vSelect + "|"+ cn.callresult.text) end if