Checking whether a value exists in an option field

[rajeev108]

I have an option (code, description), I want to check whether a particular code exists in the option's codes without setting it. What is the syntax? LISTFIND?

[steve]

Several ways - none very elegant but give you ideas for the future I hope:

method 1) - backup-overwrite-restore
store previous option field value in a holding variable. temporarily set the option field code to the value you are testing against. See if it returns a valid Description (you may have to create an additional column that always contains data). Return option field code back to its original state after testing

Code: Select all

#store original option code 
v-original = {option field.Code}
#store value to test:
put ("123", {option field.Code})
#see if this returns a valid description
if {option field.Description} = "" or {option field.Description} = ? then
#option code doesn't exist
else
#option code does exist
end
#restore our option field
put (v-original, {option field.Code})
#don't do any UPDATES and you should be OK if the code crashes as nothing is committed back to the database.

method 2) Abuse the FETCH command

Code: Select all

#================# 
#casetype code (from Casetype maintenance) 
v-casetype = 205 
#================# 
#option field name 
v-fieldname = "option1" 
#================# 
#option value (code) to search for 
QUESTION( "code to search for? " , "ALPHA" ) 
v-code = return-value 

#================# 
# FETCH the database field_id for the option field name and casetype
# or hard-code if already known for speed 
#================# 
v-id = FETCH( "CONTENT" , "field_type" , "field_id" , "FOR FIRST field_type NO-LOCK WHERE  field_type.case_type = '" + v-casetype + "' AND field_type.field_desc = '" + v-fieldname + "'" ) 
#================# 
MESSAGE( "Field name " , v-fieldname , NEWLINE , "Field ID: " v-id ) 
#================# 
#fetch the number of rows in option detail table that contain our given Code 
#================# 
v-count = FETCH( "COUNT" , "field_options" , "option_detail" , "For EACH field_options NO-LOCK WHERE field_options.field_id='" v-id + "' AND FIELD_OPTIONS.option_value = '" + v-code + "'" ) 
#================# 
MESSAGE( "Number of Matching rows: " , v-count ) 
IF v-count = 0 THEN 
    MESSAGE( "Option Code " , v-code , " does  NOT exist" ) 

ELSE 
    MESSAGE( "Option Code " , v-code , " EXISTS" )  
END 
#extend the idea to FETCH CONTENT of the row and use LISTFIND with semicolon to get the option Detail or other columns  if required

method 3) MIWH and EXEC-SP
sync the option field to the MI warehouse. Write a stored procedure in your MSSQL database to return e.g. TRUE or FALSE if a given input code exists in the option table. Call the Stored Procedure from within Proclaim maths using the EXEC-SP command.

[rajeev108]

Thanks,Steve! Method 1 is what I have always used but felt that there must be a better solution. I will try method 2.

[iysdenpastie2016]

Hi there,

We have come across this post when trying to PUT into an option field, based on an value in our description, rather than the code.

Is there a way to find if the value exists in the description column and then pull out the code for that item using the FETCH command?

Any help on this would be fantastic!

[steve]

Hi,
Traditionally this is done by a QUESTION that is prompted to a user, so that the user chooses the DESCRIPTION they want, and the underlying CODE is returned to Proclaim maths, for use in a PUT statement:

Code: Select all

#ask the user to choose a DESCRIPTION for an option field - not the first column
QUESTION ("Please choose the description:","OPTION",{my option field.Description})
#the users choice is returned not as the DESCRIPTION, but the CODE (or more strictly, the first column in the option field, whatever that is)
PUT(return-value, {my option field.Code}

The danger of using FETCH is that several unique CODES can have the same DESCRIPTION, so a search for a CODE based on on a DESCRIPTION might have multiple results. (database speak: your first column in Option maintenance - traditionally called CODE - is the primary key and therefore unique. There are no other restrictions on other columns, so non-unique values in DESCRIPTION are allowed and a search against a given DESCRIPTION may yield multiple matching rows.

I believe a FETCH returning an option_detail column from field_options table will fail in Proclaim Maths as the result is an array, which I haven't figured out how to get Proclaim Maths to handle yet - these are all a bit of an abuse of the FETCH command so use at your own risk!

Method 3) from my first post would work if you have MIWH

[iysdenpastie2016]

Hi Steve,

The particular option list I am referring to has five columns and the column contains unique values. Is there a way to specify column we return or check against?

Thanks again,

Iysden

[steve]

Hi Iysden,
are you proposing to use the QUESTION method? In which case, you just specify your desired Column in the Question

Code: Select all

QUESTION ("Please choose an option:","OPTION",{my option field.wheveverthecolumnheadingis})

Using FETCH, I've had a play around but haven't been able to get FETCH to return a single column from the option_detail (the 2nd and subsequent option columns) array.

[revellbikes]

I've had a play around with the FETCH command in the past and it's quite addictive!

I'm not 100% sure this works, but I believe the below code will tell you whether the name you've entered in the QUESTION prompt is in the Fee Earner.First Name column, which is column 5 on our FE Option list.

I changed it to a 'COUNT' as opposed to 'CONTENT' as we want to know whether something exists or not. Anything above 0 means it exists. Secondly, I changed it to pull back 'option_detail' rather than 'option_value'.

Code: Select all

# v-id is the Database Field code you're testing
v-id = 1234 
QUESTION( "Enter Fee Earner Name" , "ALPHA" ) 
v-search = return-value 
# 
vcode = FETCH( "COUNT" , "field_options" , "option_detail" , "For FIRST field_options NO-LOCK WHERE field_options.field_id='" v-id + "' AND FIELD_OPTIONS.option_detail[5] = '" + v-search + "'" ) 
# 
MESSAGE( vcode ) 

My only concern is it only ever returns '0' or '1' even if more than one entry exists. I've tested about 10 different names, they all bring back '1' and tested several false names and they all bring back '0'.

Amend your code steve and see if you can get it working on your system.

[steve]

A neat trick,
I only guessed that FIELD_OPTIONS.option_detail[5] would be the required syntax - not sure it is, but if you have working code then ....

[edit to remove guesswork! - see below]

This is a good trick for working out whether exists or not as a Description/non-PK column in an Option field, but it would be good to get the Code given a Description somehow.

[revellbikes]

****update****

I've just noticed in the above code it's using 'For FIRST' as opposed to 'For EACH' which is why it was only ever return 1 or 0. If you change to 'For Each' that will give you a true COUNT. Although for the purposes of this exercise a Boolean may actually be beneficial.

Updated code to give you a true 'count' as opposed to 1 or 0 as an output:

Code: Select all

# v-id is the Database Field code you're testing
v-id = 1234 
QUESTION( "Enter Fee Earner Name" , "ALPHA" ) 
v-search = return-value 
# 
vcode = FETCH( "COUNT" , "field_options" , "option_detail" , "For EACH field_options NO-LOCK WHERE field_options.field_id='" v-id + "' AND FIELD_OPTIONS.option_detail[5] = '" + v-search + "'" ) 
# 
MESSAGE( vcode )