Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #1 
In Metastorm BPM 9 we have a problem that you cannot safely define a query for a Business Object with an 'IN' clause. This makes some dynamic query forms, such as search forms, impossible to write if multiple options can be selected form a list.

One approach we have come across that gets around this issue is to create some SQL that inserts ID values (in this and most cases, a Folder ID) into a table. The resulting BO need only join to that table, and all linked records can be listed.

There are many approaches to cleaning up the table to avoid old records remaining. One is to delete on commit (although you cannot guarantee a commit). Another is to add a time stamp and delete all records over 24 hours old. We use that in the second example.

In some cases you may need to de-fragment the indexes and/or rebuild the table over time.

 
Attached Files
zip Demo_In_Clause_Workaround.zip (21.88 KB, 21 views)
zip Demo_In_Clause_Workaround2.zip (22.16 KB, 11 views)


__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.

0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #2 
too lazy too start up my (very old) v9
how do you keep the linked records apart (if 6 users use the same page)
add a on the fly guid

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.

PS that's for V6/7 for V9 the problem is V9 at the moment ;)
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #3 
filtered by the current folder id

__________________
Post an example, and we will have a much better idea what the problem is. In about 90% of posts, the problem is one of communication. Examples bridge that gap.
0
philt

New Member
Registered:
Posts: 1
Reply with quote  #4 
Another way to do this is to use Metastorm's built in function [pudf_stringlist_to_table] which converts a string to a table of values using a given separator. In a list of multiple values, metastorm splits them with a tab.

An example use is -

SELECT eFolderID
FROM eFolder a WITH (NOLOCK)
WHERE (ISNULL(@parStageName,'') = ''
OR eStageName IN (SELECT Item FROM dbo.[pudf_stringlist_to_table](@parStageName,CHAR(9))))

The ISNULL(@parStageName,'') = '' allows you to view all records if nothing is selected.
0
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!