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

Moderator: amosbatto

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 HeshanKaru1994
#825759
I have a dynaform A which is used for 2 different processes.(Process A and Process B)

If process A is ongoing suggest control with sql query is used.
But if process B is ongoing all the details are autofilled from the database.

When you persist data to database using process A the key will be stored and when you query and autofill from process B the value is filled and when you persist that data to database(the originally autofilled value will be persisted rather than the key itself)
User avatar
By amosbatto
#825774
If a suggest box doesn't have an SQL query (or array in a variable) that contains the selected option, then it will save the label as the value. To fix this problem, you need to add the SQL query to the second suggest box in order to preserve its original value.

For example, if your first suggest box has the SQL query:
SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS
Then, you need to add the same SQL query to the second suggest box so it can find the option:
SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS

The other option is to create a trigger that saves the original value in a separate variable, so that you don't lose it:
@@saveSuggestValue = @@mySuggestBox;
#825776
It doesnt have two suggest box. Two different flows use the same dynaform where first scenario we type and search from suggest box while in the second scenario all the data are autofilled and non editable. But in both scenarios all the values are persisted to the database
User avatar
By amosbatto
#825778
The suggest box has to have the value in its list of options or it will save its label as the value.

To get around this problem, create a trigger like this:
Code: Select all
@@mySuggestBox = "value1";

@=optionsList = array(
   array("value1", "label1")
);
Where "mySuggestBox" is the name of the suggest box's variable and its datasource is "array variable" and its data variable is set to: @@optionsList
suggestProperties.png
suggestProperties.png (31.63 KiB) Viewed 161 times
User avatar
By amosbatto
#825895
HeshanKaru1994 wrote: Thu Aug 08, 2019 1:39 am Can the same thing be done inside a grid (suggest control is inside the grid)
You can create an array with all the selected values and their labels, but my recommendation is to simply use the same SQL query for the suggest boxes in both grids.
For example, if your SQL query is:
SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS

Then if the value of the suggest box in row 1 of the grid is 284375, then ProcessMaker will automatically find the matching CLIENT_NAME in the CLIENTS table and display it as the label. It will do do this for all the rows in the grid.
User avatar
By amosbatto
#825989
HeshanKaru1994 wrote: Wed Aug 14, 2019 1:35 am Do you have a working example
Why can't you use the same SQL query in both select fields?

In the first grid, you have:
editableSelect.png
editableSelect.png (12.01 KiB) Viewed 56 times
In the second grid, you have:
read-onlySelect.png
read-onlySelect.png (12.31 KiB) Viewed 56 times
(26.55 KiB) Downloaded 1 time
#825998
Thats not what I want.
Dynaform data loaded from sql trigger and set to suggest control(since suggest is key value paired when the form is saved only value persist to the database - the key is not saved)
#826002
Imagine suggest box is autofilled from trigger before dynaform. That suggest box has an sql query.When the record is saved to the database the value is saved rather than the key itself. I want to save the key to the database.
User avatar
By amosbatto
#826013
Let's say that you have a suggest box associated with the "selectClient" variable and your SQL query to populate the list of options in the suggest box is:
SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS

and it returns the following records:
Code: Select all
1   | John Smith
2   | Sally Baker
3   | Harold White
4   | Sarah Moore
5   | Mary Deere
Then, you can set the following variable in the trigger before the Dynaform:
Code: Select all
@@selectClient = 4;
And the suggest box will display "Sarah Moore" as the selected option when the Dynaform is loaded.

Then, you can set the following trigger to save to the database after the Dynaform:
Code: Select all
$db = '1234567890abcdef1234567890abcdef'; //ID of database connection
$name = addslashes(@@selectClient_label);
$id = @%selectClient; //@% converts from string to integer
$sql = "INSERT INTO SELECTED_CLIENT (CLIENT_ID, CLIENT_NAME) VALUES ($id, '$name')"; 
executeQuery($sql, $db);
------------------------------------------

If you want to populate a grid from a database query, then you need to create a variable that holds all the options that could appear in the suggest box in any row in the grid.
For example:
Code: Select all
$db = '1234567890abcdef1234567890abcdef'; //ID of database connection
//set the list of options in the suggest box in grid:
@=suggestOptions = array();
$aClients = executeQuery("SELECT CLIENT_ID, CLIENT_NAME FROM CLIENTS", $db);
foreach ($aClients as $aClient) {
    //CLIENT_ID is the key and CLIENT_NAME is the label for each option in the suggest box:
    @=suggestOptions[] = array( $aClient['CLIENT_ID'], $aClient['CLIENT_NAME'] );  
}

//set values in grid:
@=clientsGrid = array(
    1 => array( 
        'field1' => 'value1A',
        'field2' => 'value2A', 
        'selectClient' => 3   //label of suggest box in row 1 will be "Harold White"
    ),
    2 => array( 
        'field1' => 'value1B',
        'field2' => 'value2B', 
        'selectClient' => 1   //label  of suggest box in row 2 will be "John Smith"
    ),
    3 => array( 
        'field1' => 'value1C',
        'field2' => 'value2C', 
        'selectClient' => 4   //label of suggest box in row 3 will be "Sarah Moore"
    )
);
Then set the data variable property of the suggest box in the grid to: @@suggestOptions

Hi Support, I think this is good. I too have fixe[…]

Hi, I'm facing the same error and when I run : […]

Routing Logic

Hi All I am doing a process, where i have 3 tasks […]

Hi @amosbatto, thanks a lot. at least the message[…]