Documentation.digital-crew.com

The Problem (adopted from support email):

In a select text option box in UDI, we have a problem with the code that I'm hoping you have a solution to:

We have a SQL field that cannot be null and defaults to 0

We use select text with a query option that queries a bunch of project numbers.

The problem is, the field isn' t required and when not selected, it needs to write a ' 0' to the database.  There is no 0 number in the query string, and they' re all cross linked reference key so I can' t edit the data

If we allow the null option, the data doesn't submit.

Then as a last resort, setting the default to 0 doesn 't save either.

The Solution:

You can either modify the query so it includes the zero option using something like:

SELECT projectId as ValueCol, project AS displayCol, 2 AS sortOrder
FROM projects
UNION
SELECT 0 as ValueCol, 'No Project' AS displayCol, 1 AS sortOrder
ORDER BY sortOrder, project

This works perfectly in mySQL but you may need to do some tweaking to
get it working for MSSQL 0 Use Query Analyser to test this first.

The second option is to do the query before you call UDI and
build two lists. One of project Id's. The other of project names.
<cfset projectIdList = ListAppend( "0", ValueList( getProject.projectId ) )>
<cfset projectList = "No Project">
<cfloop list="getProjects">
    <cfset projectList = ListAppend( projectList, replace( project, ",", " ", "ALL") )>
</cfloop>

then in UDI pass in valueList="#projectIdList#" displayList="#projectList#"... instead of query=....