Process Mapping Logo

Process Mapping - Forums

Sharing 19 years of knowledge and experience

 
Metastorm BPM forums
Sign up Latest Topics
 
 
 


Reply
  Author   Comment  
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #1 
Hi,

I want to clone part of the data from one existing folder to an other existing folder and i just cant find the sql syntax for it.

Basically what i want is this, but well thats not sql syntax
UPDATE Master SET ( $$$FIELDLIST$$$ ) = 
       ( SELECT $$$FIELDLIST$$$ FROM master WHERE efolderid = @FromID )
WHERE efolderid = @ToID

Any suggestion for my soggy brain are welcome :)

__________________
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
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #2 
Unsogged my brain and googled some more
Quote:

UPDATE Master SET $$$FIELDLIST$$$
FROM Master CROSS JOIN Master AS Master_1
WHERE (Master.EFOLDERID = @ToID) AND (Master_1.EFOLDERID = @FromID)

Where $$$FIELDLIST$$$ =
tField1 = Master_1.tField1, tField2 = Master_1.tField2, ......



__________________
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
toddgreve

Avatar / Picture

Senior Veteran
Registered:
Posts: 313
Reply with quote  #3 
Not certain what you were trying to accomplish, but if I understood correctly you were attempting to update one existing folder with data from another existing folder. Just in case I felt I should add - "It is not recommended, and not supported, to update the Metastorm process table - table of custom variables". The engine could have a lock on this table which will make what you do fail, or what you do may make Metastorm fail.

If you are doing this either use flags to pass the data, or the process that is being updated to retrieve the data into a text field and assign the elements to the local custom variables.

If you weren't going to be updating the process tables, please disregard my blurb ... ;)


__________________
Sometimes I make sense, and sometimes I know what I am talking about, sometimes ... ;)

These comments are provided of my own volition, expresses only my viewpoint, and does not represent Metastorm's “official” position.
0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #4 

But i am updating the tables :D

It fusks up when you write data in fields that are also updated by the action.
Problem is that it's 394 fields i need to copy!
Not something easy to do and potential delimiter hell as well (there may be well basically any char in there that i may pick as delimiter)
I now call this script (use at own risk etc etc)

Quote:
        public static function CopyMaster2Master ( ework: SyncProcessData, args: Object[] ) : Object
        {
            // Copy some folderdata from one folder to new folder
            // args[0] = FromID
            // args[1] = ToID
            // args[2] = Account name
            //
            var DebuG : String  = "";
            try {
                var FromID : String  = args[0];
                var ToID : String = args[1];
                var Account : String = args[2];
                var list : Array;
                var lx : String = "";
                var fName : String = "";
                var AddComma : boolean = false;
                var SQ : System.Text.StringBuilder = new System.Text.StringBuilder();
                var FL : System.Text.StringBuilder = new System.Text.StringBuilder();
                // Create copy statement
                SQ.Append("UPDATE Master SET $$$FIELDLIST$$$ ");
                SQ.Append("FROM Master CROSS JOIN Master AS Master_1 ");
                SQ.Append("WHERE (Master.EFOLDERID = '" + ToID + "') AND (Master_1.EFOLDERID = '" +FromID + "')");
                // create fieldlist based on table
                lx = ework.SelectSQL("SELECT tField FROM [TBL_MasterCopy] WHERE tType = 'Fields" + Account + "'",,,"|");
                // split into seperate items
                list = lx.split("|");
                // create the set list for all the fields
                for(var i = 0; i<list.length; i++)
                   if(list[i]!=undefined) {
                      fName = list[i].ToString();
                      if (AddComma)
                         FL.Append(", ");
                      else
                         AddComma = true;
                      FL.Append(fName);
                      FL.Append(" = Relo_Master_1.");
                      FL.Append(fName);
                   }
                // create the SQL with fields
                SQ.Replace("$$$FIELDLIST$$$",FL.ToString());
                DebuG += SQ.ToString();
                ework.ExecSQL(SQ.ToString());
            } catch (e) {
                DebuG += "CopyMaster error\r\n" + e.description;
                ework.Email("@@@emailTO@@@","","CopyMaster error",DebuG,","@@@emailFrom@@@);
            }
            ework.CustomVariable("memDebug",DebuG);
            return true;
        }


TBL_MasterCopy holds the fieldnames to copy



__________________
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
LJ

Avatar / Picture

Senior Veteran
Registered:
Posts: 452
Reply with quote  #5 
"It is not recommended, and not supported, to update the Metastorm process table - table of custom variables".

So run the update at midnight when the users are gone, unless you system is international in which case leave it well alone. I have a similar plan to run priority updates from a single map (less entries in the tables).
 
LJ.

__________________
LJ
0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #6 
:)

It's run in the flagged action that starts the folder at the start of the todo list. It works but does fail when updating a field that is then also updated in the action.

I know unsupported / risky

Updating at night is not an option as we only have 24 hours to work on the folder ;)

__________________
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
KarlD

Avatar / Picture

Guru
Registered:
Posts: 113
Reply with quote  #7 
why not have the parent write out the data to a new table and then generate the new folder. then the new folder can pick the data from the new table and all are happy and you have a record of the state of the variables at the point of creation too!

just a thought.
0
LJ

Avatar / Picture

Senior Veteran
Registered:
Posts: 452
Reply with quote  #8 
Sounds good to me...
 
LJ.

__________________
LJ
0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #9 
Quote:
Originally Posted by KarlD
.... then the new folder can pick the data from the new table and all are happy ....

Problem is that the only quick way to copy the 400 fields is via direct sql.
How would you pick the data up?

__________________
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
Jas

Senior Veteran
Registered:
Posts: 429
Reply with quote  #10 

Not quite awake yet but think you could use %GetFolder

0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #11 
Well i would end up with 400 Getfolders and 400 roundtrips to the sql server. That's the bit i want to avoid.

Lets refrase the question. How would i pick up the data in one go?

__________________
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
Richard

Member
Registered:
Posts: 16
Reply with quote  #12 
What about using a Jscript.net script?

You could store the names of the custom variables that you want to send across in a comma separated list or in a table.
Then loop thorugh this list setting each variable using
ework.CustomVariable(<varname>,<value>);

You could even read all the values into an array in one go to cut down the number of SELECTs from the original folder record.




__________________
processtrack.com
0
Nappy

Avatar / Picture

Guru
Registered:
Posts: 1,087
Reply with quote  #13 
That was my initial though but i have the problem that i have no safe delimiter to get the data over and split it up.
I have memo fields that can contain tabs and they might even have the | char in there (you will not believe what users sometimes do with copy pasting ;)

So how do i get the 400 fields out of SQL into an array?
I want to use the ework.selectsql if at all possible. I can use true .net but then i have to be very carefull with getting the data out of sql and security etc (i think)

__________________
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
LJ

Avatar / Picture

Senior Veteran
Registered:
Posts: 452
Reply with quote  #14 
I've just hit this and need to read about six values into an array in order to cut down on the database access costs.

Anyone got some generalised code that can be used in many places?
Possibly feeding in SQL and then returning a string of results I can delve through with string handlers.

I'll check the Metastorm forums as well.

__________________
LJ
0
Jerome

Avatar / Picture

Guru
Registered:
Posts: 5,507
Reply with quote  #15 
Yes, I had to do a lot of these for one migration, and built a class to do it. it is in our library (attached).

Example:
Quote:
Metastorm.Runtime.Models.ProcessMappingLibrary.PMSingleDataRow sdr = new Metastorm.Runtime.Models.ProcessMappingLibrary.PMSingleDataRow(
    null, "SELECT SURNAME, FIRST_NAME, PHYL_NAME FROM SMS_ALL_STAFF WHERE SMS_ALL_STAFF.EMPLOYEE_ID = '" + Alter_Seniority_CodeData1.EmployeeId + "'");

Alter_Seniority_CodeData1.SURNAME =  sdr.GetTextElement(0);
Alter_Seniority_CodeData1.FIRSTNAME =  sdr.GetTextElement(1);
Alter_Seniority_CodeData1.PHYSLOC =  sdr.GetTextElement(2);


You can have parameters just like as SelectSql() call, and you need to call the correct function based on field type for each field (working it out on the fly is no good as the return then must be cast anyway).

 
Attached Files
zip Process_Mapping_Library.zip (214.29 KB, 2 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
Previous Topic | Next Topic
Print
Reply

Quick Navigation:


Create your own forum with Website Toolbox!