List Creation Fails Due to list_permissions Constraint Conflict​

In Control Room versions prior to 4.2.108, list creation may fail due to conflicting foreign key constraints on the list_permissions table. Specifically, the presence of two additional constraints fk_list_permissions_lists_list_id and fk_list_permissions_users_user_id can cause constraint violations during list creation.

This issue was addressed in version 4.2.108 by retaining only the essential primary key constraint, thereby resolving the conflict.

If your system is running any version older than 4.2.108.0 and you cannot upgrade immediately, a manual workaround is available. You can remove the conflicting constraints using pgAdmin (for PostgreSQL) or SQL Server Management Studio (for MSSQL). However, upgrading to version 4.2.108.0 or later is strongly recommended to avoid encountering this issue in the future.

Applies To​

This issue affects Control Room version 4.2.107, specifically when using PostgreSQL or Microsoft SQL Server as the database.

Symptoms​

• Unable to create a new list from the Control Room.
• Application logs show constraint violations or foreign key errors related to the list_permissions table.
• List creation consistently fails when saving the new list.

Root Cause​

In CR version 4.2.107, the list_permissions table includes the following constraints:

• pk_list_permissions_list_perm_id (Primary Key)
• fk_list_permissions_lists_list_id (Foreign Key)
• fk_list_permissions_users_user_id (Foreign Key)

These foreign key constraints conflict with list creation operations, resulting in violations. In version 4.2.108, the schema was updated to retain only the primary key constraint, which resolves this issue.

Resolution (Workaround for 4.2.107)​

Manual Resolution for Version 4.2.107, follow the steps below based on your database:

For PostgreSQL​

1. Open pgAdmin.

2. Navigate to the active database.

3. Expand Schemas → public → Tables → list_permissions.

4. Expand the Constraints section.

5. Delete the following foreign key constraints:

   • fk_list_permissions_lists_list_id
   • fk_list_permissions_users_user_id
     (Right-click each constraint → Click Delete)

6. Ensure that the primary key pk_list_permissions_list_perm_id still exists.

7. If it does, rename it to pk_list_permissions:
   (Right-click → Properties → Change Name → Click Save)

   note

   This renaming ensures consistency with the updated schema in v4.2.108

For Microsoft SQL Server (MSSQL)​

1. Open SQL Server Management Studio (SSMS).

2. Expand the Databases section.

3. Navigate to the active database.

4. Go to Tables → ListPermissions.

5. Expand the Keys section.

6. Delete the following foreign key constraints:

   • fk_list_permissions_lists_list_id
   • fk_list_permissions_users_user_id
     (Right-click each constraint → Click Delete)

7. Ensure that the primary key pk_list_permissions_list_perm_id still exists.

8. If it does, rename it to PK_ListPermissions:
   (Right-click → Rename → Enter PK_ListPermissions)

   note

   This renaming ensures consistency with the updated schema in v4.2.108

Recommendation​

To permanently resolve this issue, upgrade to Control Room version 4.2.108 or later, where the conflicting constraints have been removed.

Trouble setting the dropdown value for an Angular mat-select element​

When trying to set the dropdown field value of a mat-select element from Angular Framework using Set Dropdown Field Value activity under the Web Automation category, it fails. This issue is because of the lack of a JavaScript-based method for setting the value in Angular.

Here is the example of the mat-select element used in one of the web pages:

To address this issue, you can follow these steps as a workaround:​

1. Use Click Element activity to click dropdown. It will display a pop-up with available options.

   

2. Again, use Click Element activity to click on desired options from the pop-up.

   

Excel activities provide various properties that users can configure to optimize their tasks. This guide details how to set these properties within Excel activities effectively:

• Sheet - Index and Name
• Column - Index and Letter
• Row - Index
• Cell
• Range
• Converting numeric index to letter

Sheet Index and Name​

You can specify the Sheet Index or Name in a workbook containing multiple sheets. By default, the active Sheet is considered if neither is specified.

Workbook sheets are indexed starting from zero, moving left to right. For illustration, consider a workbook with three sheets depicted in the image below:

The index mapping for the sheets is as follows:

Sheet Name	Sheet Index
Sheet1	0
Sheet2	1
Sheet3	2

Column Index and Letter​

The Column Index denotes a specific column within a sheet, beginning from zero (the leftmost column) and extending rightwards.

In Excel, columns are typically identified by letters. Below are examples demonstrating the correlation between numeric indices and their corresponding column letters.

Column Letter	Column Index
A	0
B	1
C	2
...	...
Z	25

Row Index​

The Row Index refers to a row in a sheet, starting from zero at the top and incrementing downwards.

Cell​

A cell in a sheet is a unique location determined by combining a column letter and row index.

The following table provides examples of cell addresses for better understanding:

Cell Address	Description
"A1"	First cell in the sheet, Column A, Row Index 0
"D55"	Cell with Column D and Row Index 54

Range​

The term Range describes a selection of cells within a spreadsheet. It is represented as a string and can take various formats, as exemplified below:

• Start Cell:End Cell
• Start Cell

For example: "A1:B10", "A1"

Converting numeric index to letter​

In process automation scenarios, you may need to convert a column's numeric index into a letter-based index. IntelliBuddies Excel utilities facilitate this conversion seamlessly.

To convert a letter-based column index to a numeric one, use the following expression:

IntelliBuddies.Activities.Excel.ExcelExtensions.ToIndex ("A")

We all know that Languare INtegrated Query (LINQ) is a technology that integrates query capabilities directly into programming languages. We also know that C# supports LINQ. LINQ enables you to write queries against strongly typed collections of objects by using language keywords and familiar operators.

IntelliBuddies process definition supports C# as part of the Expression dialog and thus enabling the designers to utilize LINQ and solve complex query problems with simple statements.

This blog talks about some of the typical scenarios where LINQ would make your jobs easy and there by making your process simple and easily manageable.

DataTable is one of the mostly utilized Data object when it comes to process flow. Here, you will see how effectively you could solve the most appearing problems on DataTable using LINQ.

Filtering DataTable Rows​

Updating a specific row inside a DataTable​

Deleting a row from DataTable​

Using Send Keys​

Desktop automation activities sometimes fail to perform their tasks due to the unavailability of a UI element at runtime. When you encounter such scenarios, you can work around and perform automation using Send Keys activity. This blog explains multiple scenarios where Send Keys could be an alternative solution.

1. Selecting menu options​

Typically, we select menu options using the Mouse Click activity. In case of unavailability of the element, Mouse Click fails to operate. In these scenarios, you can use the Send Keys activity with the '[DownArrow]...[Enter]' key to select the menu option. 'Send Keys' does not depend on the UI element to be available, ensuring that the actual mouse click activity is replaced with key-based menu operations.

2. Selecting the Radio Button​

Typically, we use Select Radio Button to automate operations on Radio Button UI elements. However, when the activity fails due to the unavailability of the UI element, we could try to achieve the automation using Mouse Click activity. If this fails, then we can try with the Send Keys activity with the '[Space]' key to select the radio button.

3. Selecting the Checkbox​

Typically, we use Set Checkbox State to automate Checkbox UI elements. If it fails, we can try to achieve the automation of Checkbox using Mouse Click activity. If this also fails, then an alternate way to automate is using Send Keys activity with the '[Space]' key to toggle the Checkbox state.

4. Populating Textbox​

Typically, we use the Populate Text Field activity to fill in text inside a field. If it fails, another way to work is using Send Keys activity with the text data which writes inside the textbox.

Mouse Click and Mouse Move activity with X & Y Adjustments​

When the Mouse Click or Mouse Move activities are unable to find a particular element(A), we can indicate the nearby element(B) and make the X & Y adjustment in the activity to reach that element(A).

Find the unavailable element location manually by adjusting the X, Y, or both values. Provide the extracted value to X Adjustment and Y Adjustment property of Mouse Click or Mouse Move activities

Starting 01st of June, 2022, Gmail has completely removed unsecured access to their Mailboxes. Hence, you will not be able to access your Gmail using your Gmail user credentials from third-party applications. Instead, they recommend users enable two-step verification for their account and register the third-party application that you would let access to your Gmail with your consent. On successful registration of the third-party application, Gmail will provide you with App Password to be used to access your Mailbox from third-party applications.

Use the app passcode in following Email activities to input secured password for password field:

• Get IMAP Mail Messages
• Get POP3 Mail Messages
• Send SMTP Mail Message
• Move IMAP Mail

Generating App Password​

Generating App Password is a two step process as stated below:

• Turn on 2-Step Verification
• Generate 16-digit App Password

Turn on 2-Step Verification​

To turn ON two-step verification for your Gmail account, follow the steps given below:

• Open and login to Google Account.

  OR

• Click on Google Account in the right top corner. Click on Manage Your Google Account.

• In the left navigation pane, click on Security

• Scroll down to the Signing into Google section and select the 2-Step Verification

  

• Click on GET STARTED button.

  

• Enter the password and G-Code sent to your mobile by clicking on the Text in verification page. - On successful validation, open the 2-Step Verification page.

• Click on TURN ON

Generate 16-digit App Password​

• Go back or Navigate to the Security tab

• Scroll down to the Signing into Google section and select the App passwords

  

• In the App Passwords page, click on the Select app dropdown and choose Other (Custom name)

• Enter an App name as IntelliBuddies and click on GENERATE

  

• A 16-digit App Password will be generated and shown on the page.

• Copy the 16-digit passcode and use this in Activity to provide a secure password.