v9 - Multiple selects and logic within list options

Harry Mayeux · June 8, 2011

I am trying to pull different things in my "List Options" for a v9 dropdown box, but I have been unsuccessful in getting my information to pull properly.

In version 7, I could have a simple "%SelectSQL" on the first line and a conditional action on the second line.

In version 9 it seems that it only wants me to put one "SelectSql". Is there another way to implement this in v9 like the way I have it implemented in v7?

Thanks

Jerome_Pearce · June 9, 2011

You can pretty much build anything you like, but you need to use ListItems() to build your option lists. You can include any amount of conditional ststements in the arguments for this function in order to build a list.

See a ce of simple examples here:

http://metastorm.processmapping.com.au/post?id=5343659

Harry Mayeux · June 9, 2011

Thanks Jerome. I will give this a try.

Neil_Hudspeth · June 9, 2011

Are you looking for something like this?

Harry Mayeux · June 9, 2011

What I am really trying to accomplish is this:

if the user selected ADD and did not enter an ID, pull a list of data

Otherwise, pull an alternate list of data

I tried the following, but it fails on deployment....

If(Data1.rbtnAddDel.ToString()=="ADD"&&(Data1.txtRecMainID==""), ListItems(SelectSql(new MetastormDefault(),"SELECT DISTINCT application_code+' - '+upper(application_desc) FROM vw_map where system_code <> 'MFR'").List), ListItems(SelectSql(new MetastormDefault(),"SELECT DISTINCT application_code+' - '+upper(application_desc) FROM vw_map ").List))

I have also tried to put the if conditions within the ListItems and that did not work either.

Thanks,

Harry

Brian Mellert · June 9, 2011

Or perhaps rewrite the query so the "if" logic is in a where clause? May not be right, but should be close.

SelectSql("SELECT DISTINCT application_code+' - '+upper(application_desc) FROM vw_map WHERE (@pAddDel = 'ADD' AND pRecMainID = '' AND system_code <> 'MFR') OR (@pAddDEL <> '' OR pRecMainID <> '')",SQLArg("@PAddDel",Data1.rbtnAddDel),SQLArg("@pRecMainID",Data1.txtRecMainID).List

Jerome_Pearce · June 9, 2011

No, ListItems() is notr what you are after in this case.

If(Data1.rbtnAddDel.ToString()=="ADD"&&(Data1.txtR

ecMainID==""), SelectSql(new MetastormDefault(),"SELECT DISTINCT application_code+' - '+upper(application_desc) FROM vw_map where system_code <> 'MFR'").List, SelectSql(new MetastormDefault(),"SELECT DISTINCT application_code+' - '+upper(application_desc) FROM vw_map ").List)

should work, I would think?

Neil_Hudspeth · June 10, 2011

I know it's not quite as complex as what you are trying to do but this worked for me in the List Options:

If(VariSelectData1.Size==

"Large", SelectSql(new MetastormDefault(),"SELECT eMapName FROM eMap"), SelectSql(new MetastormDefault(),"SELECT eUserName FROM eUser"))

Milosz Pawlicki · June 13, 2011

be carefull using Metastorm If METHOD(better create c# function with c# if inside).

I know that it should be obvious, but i believe that it is not for all.

Metastorm "If" method is same method as you write in your c# code (or other language), in parameters you pass objects (not delegates), which means that if you pass method (in your case SelectSql) as a parameter, this "parameter method" will run before If method (because If method gets concrete values, not points to methods which will return those values).

I can imagine that this function looks like this

public static object If(Check check, object o1, object o2)
{

if(check.ToBoolean())

{

return o1;
}

else

{

return o2;
}

}

so in this example:

If(VariSelectData1.Size==

"Large", SelectSql(new MetastormDefault(),"SELECT eMapName FROM eMap"), SelectSql(new MetastormDefault(),"SELECT eUserName FROM eUser"))

both selects will be executed, working with large and heavyload database can cause big performance problems.

Jerome_Pearce · June 13, 2011

There are also other problems with teh IF() function and certain types, documented elsewhere here and on our own forums.

Personally I would not go to the length of creating a dedicated C# function unless it was a complex statement or condition, or if it was ever going to be reused.

For fairly simple conditions, I find ternary operator a ? b : c (if a is true, then execute b, otherwise execute c). I believe this performs 'lazy' or 'deferred' execution (ie the invalid condition is never evaluated at run time).

You are now a semi-colonist, whether you like it or not!

Harry Mayeux · June 14, 2011

I have found that being a "semi-colonist" is much harder in the v9 world.

My if condition is fairly complex, but I am using SQLARGs - which I believe is making it much more complex. I also like to explicitly state my conditions.

my conditions are as follows:
if a then execute b, otherwise if c then execute d, otherwise if e then execute f, otherwise if g execute h, otherwise nothing

I think the use of sqlarg along with the complex if structure is getting misinterpreted by the "builder" and never validates.

Because of the complexity, it seems it may just be easier to write a C# function.

Jerome_Pearce · June 14, 2011

I agree. With that level of condional complexity (I like that term!) you are definitely better off with a function.

In recent months I have come to the conclusion that just about every single lookup, even simple ones, may be best in a function. Business objects are a pain because there are too many, although there are times when they are better (eg individual record access). If we embed SQL in fields themselves, they become unmaintainable very quickly, and, as in this case, anything complex is difficult to manage.

Typically we have a separate code file named something like 'Lookups" to manage this. If we ever need to change the data source, we can just change it oin one place. This may be the main driver for this design decision, as we find it always changes, it is just a matter of time....

v9 - Multiple selects and logic within list options

Comments

Categories