Questions and discussion about developing processes and programming in PHP, JavaScript, web services & REST API.

Moderators: ArturoRobles, ArturoRobles

Forum rules: Please search to see if a question has already asked before creating a new topic. Please don't post the same question in multiple forums.
By clintjb
#794009
Hi All,
I’m really struggling to understand how to define a trigger so am hoping someone can help out.

The situation is as follows - I have an external SQL database already linked via the database connections and everything’s green - the DB is called "xx9000" with a table called "extract", inside that table are the following columns:
PNR|Type|Valid_until|Valid_from|Cost|Currency
The process itself has the following variables defined:
Part_Number|Part_Cost|Part_Exists
I than have a process which is requesting a user to enter a part number and storing it in the process variable called "Part_Number"

I want a trigger to occur after this process step where it queries the SQL database for this "Part_Number" against "PNR" - if it exists than it takes the "Cost" and adds it to the process variable "Part_Cost" and sets a process boolean (Part_Exists) to 1. If the part number doesn’t exist then it set the boolean to 0.

This is one of a few different kinds of queries I have to do, but I think once I understand this logic than the rest will be clear.

Hopefully someone can help me - thanks very much in advanced.
Cheers
By stevensi1018
#794010
Insert the trigger after the dynaform that you have and since you said that your DB is already linked, no need to do anything else than write this code:
the executeQuery() method is as follow: variant executeQuery(string SQLStatement, string DBConnectionUID='workflow')
You can then search for your DBConnectionUID in the 'Database Connections' and replace it in the function
Code: Select all
$Part_Number = @@Part_Number;
$query = "SELECT * FROM extract WHERE PNR='$Part_Number'";
$result = executeQuery($query); //insert your DBConnectionUID as 2nd argument

if(!empty($result) && count($result) > 0)
{
	@@Part_Cost = $result[1]['Cost'];
	@@Part_Exists = 1;
}
else
{
	@@Part_Exists = 0;
}
I'm not sure if it's what you're looking for but I usually do my queries to my DB like that
By clintjb
#794011
Thanks very much for that,
Honestly Im sorry for the stupid questions but if the ID from my connected DB is 4217224125970ede59b9507004923923

How exactly does that link into the code below? And secondly more as a general understanding but that connceted DB is only the DB do you somehow need to reference the table with the information in it?

Thanks again,
By stevensi1018
#794012
clintjb wrote:Thanks very much for that,
Honestly Im sorry for the stupid questions but if the ID from my connected DB is 4217224125970ede59b9507004923923

How exactly does that link into the code below? And secondly more as a general understanding but that connceted DB is only the DB do you somehow need to reference the table with the information in it?

Thanks again,
Your question is totally valid. from the code below, just add replace the first executeQuery by this:
Code: Select all
executeQuery($query,"4217224125970ede59b9507004923923")

Since the function executeQuery has 2 parameters. 1)The SQL query, 2)The ID of the DBConnection, if not specified, it will be on workflow by default

I don't work for Processmaker so I won't go into details but since you already link the ID of the DB, you won't need to reference the table, it already knows that this table name exists.

You're welcome ;)
By clintjb
#794013
Thanks for that....

Perfectly clear and working - hopefully the rest will come a bit easier now with that basis.

Genuinely appreciate the help.

Cheers!
By clintjb
#794025
Was a little too quick to celebrate....
I have a strange problem now so hopefully someone can help me out - the solution above was/is still working fine. The SQL DB though was a small dummy one with 20 entries.

Today Ive linked it to the true DB with 208k entries and now its no longer working. Even if I enter a fake part number its not changing the Part_Exists to zero so it seems like the trigger itself isnt running....

Could this have something to do now with the size of the DB, as in I need to run a delay or something before it triggers the next process so it has time to query or this shouldnt be an issue? Little bit lost as to what could be the issue.

Cheers
_____

UPDATE - OK managed to get it working as I simply recreated the DB again re-linked and everything seems OK.... Not sure what could be casuing the issue.

Just for my own understanding if I have a numerical value in the SQL database and try to add it to a text variable in process maker can this cause issues? Or as an additional example if I have a numerical type with decimals adding to an integer variable in process maker? Just trying to work out if there was an issue from something like this or not?

Thanks

The 500 Internal Server Error is the general catch[…]

Really very nice blog information for this one and[…]

This is a long, lengthy and cumbersome process. T[…]

Great read for when on the road. The book provides[…]