Oops sorry... The only MS SQL I have to use here is inside Access and it uses a function called nz. All my databases here are Oracle and it uses nvl.
Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of J.T. Shyman Sent: Monday, December 10, 2007 12:55 PM To: arslist@ARSLIST.ORG Subject: Re: Push fields only if not $NULL$ The SQL command is ISNULL and not nz. J.T. Shyman Column Technologies -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of Grooms, Frederick W Sent: Monday, December 10, 2007 1:41 PM To: arslist@ARSLIST.ORG Subject: Re: Push fields only if not $NULL$ I might reverse your logic. How about updating the staging form filling in the $NULL$ values. You can do this using SQL Set fields. Depending on your database you will use the database's special null functions. For Oracle it is: nvl(checkValue, ifNullValue) For MSSQL it is: nz(checkValue, ifNullValue) With 82 fields to check I would say you could do the Set in 1 Filter with 10 Set fields actions (9 fields per action). Do Something like: Set Field Read Value from SQL. Select nvl('$stagingField1$',dbPeopleField1), nvl('$stagingField2$',dnPeopleField2), ... From dbPeople Where PeopleLogin='$stagingLogin$' Set stagingField1 = $1$, StagingField2 = $2$, ... This way if $stagingField1$ is null it will get the value in the People form. dbPeople = the database view name for your People form and dbPeopleField1, ... are the database view names for your fields. I would probably only run the filter on records that already exist in People. Fred -----Original Message----- From: Action Request System discussion list(ARSList) [mailto:[EMAIL PROTECTED] On Behalf Of J.T. Shyman Sent: Monday, December 10, 2007 12:02 PM To: arslist@ARSLIST.ORG Subject: Push fields only if not $NULL$ ARS 7.0.1 We have a staging form with People information. We are performing a check to see if a user matching the staged data already exists. If so, we want to push only the fields on the staging form that are not $NULL$. My thought is to do this with a filter guide and a separate filter for each field...but there are 82 of them. That's a lot of workflow. Is there another way to do this? J.T. Shyman Remedy Consultant Column Technologies [EMAIL PROTECTED] AIM: JToddShyman Y!IM: MCIQuincy _______________________________________________________________________________ UNSUBSCRIBE or access ARSlist Archives at www.arslist.org Platinum Sponsor: www.rmsportal.com ARSlist: "Where the Answers Are"
