How to check for dupes in PowerBuilder DataWindow

Question

I have this table from my data source Person with multiple contact numbers:

+----------+------+------+------+------+
|   NAME   | con1 | con2 | con3 | con4 |
+----------+------+------+------+------+
|   Jack   |  123 | 3214 | 7458 | 454  |
+----------+------+------+------+------+
|   Mack   |  113 | 3224 | 9458 | 954  | 
+----------+------+------+------+------+
|   Mary   |  133 | 3215 | 3458 | 054  |  
+----------+------+------+------+------+

How do I validate if the string from a SLE object is already in my table?

For example: The user enters 9458 in the SLE object and clicks a button named Save the App then tells the user that the contact number 9458 is already in the database.

I want to do this to avoid duplicates.

Answer 1

Solution 1: Find Duplicate Rows in Datawindow - by Expression (No SQL)

The concept is to sort data by column(s) being compared and highlighting duplicate rows using a datawindow expression (or filter). For example set the background color of a column to red on duplicate row by adding expression to the column or computed column on the background color attribute.

This is done by referring to the column name (in expression) and comparing it to the same column name with [-1] meaning the row before. Be sure to sort the datawindow first either via code or menu.

PowerScript code

// First sort the data using PowerScript
dw_1.setsort('your_pk_or_composite_key')
dw_1.sort()

Expression code

// datawindow column expression on background color for column or computed field
if (primary_or_concat_key = primary_or_concat_key[-1]), rgb(255,0,0), rgb(255,255,255))

Solution 2: Find Duplicate Rows in Datawindow - by Filter (No SQL)

This is same concept as the datawindow column expression but using a datawindow filter. This makes finding the duplicates easier if there are a lot of rows to compare. Once the code is run, only rows having a duplicate will be visible all others will be filtered out of the primary datawindow buffer.

If you wanted this as part of a validation process you could put it in an event/function and prevent the save on finding duplicates.

PowerScript code

// sort by key or composite columns concatenated
dw_1.SetSort ('key_or_concatenated_cols A')
dw_1.Sort()

// now run the filter, any rows remaining are duplicates
dw_1.SetFilter ('key_or_concatenated_cols = key_or_concatenated_cols [-1]')
dw_1.Filter()

// number of duplicates stored in local variable
integer li_count
li_count = dw_1.RowCount() 


// reset the datawindow filter
dw_1.SetFilter('')
dw_1.Filter()

Hint: It's always a good idea to check for nulls in columns when using expressions in PowerBuilder as comparing/concatenating null values can result in unpredictable behavior.

Matt Balent's solution is perfectly fine if you want to use the database to find duplicates.

Answer 2

Many ways to do this.

If you are entering a single contract value at a time (say the value is '123') you could create an INSERT statement into your table with the condition of

AND contract_num <> '123'.

This would fail if the id is already present in the table. You check the success/failure of the SQL and then inform the user.