Dialogform container messaging

[Andreas Pfeiffer]

Hello Martin,

I would not open the subform if it is not necessary. That said make a simple method in your super table class $hasduplicates that receives the column name where you want to check if there are duplicate values. You can use a local list variable in your table class’ method copy $cinst and then use $removeduplicates on the local list. Then if your $linecount is different on the temp list than on $cinst, you know that there were duplicates.

Make your $event method server side. That allows you to call a method within your list. If your list is based on a table class that is inherited from your super table class you have this method available and you can check if there are any dublicates before you open the dialog form. i.e.

If iDataList.$hasduplicates()

Do $cinst.$clientcommand(“subformdialogshow”,row(‘jsDuplicate’,#NULL,’Validate duplicate’,300,300))

End if

I hope this makes sense.

Best regards,

Andreas

• This reply was modified 7 months, 2 weeks ago by  Andreas Pfeiffer.

[Obonye]

Hi Andreas,

You said I create a method in the superclass table. Does this mean entries in iDataList are already saved in the database before $dowork command, as to check for duplicates? The reason I have the subform is to display a datagrid for duplicate/missing entries. A iDuplicateList can display more than one issue with the iDataList.

Martin.

[Andreas Pfeiffer]

Hi Martin,

No – creating your own methods in a table class does not mean that you are accessing the server table. A table class simply allows you to encapsulate methods that are data related instead doing this within your UI class.

Obviously you could do this in the UI class too but I would not recommend this because you might want to re-use this method within a different context.

I hope this makes sense.

Best,

Andreas

[Obonye]

AHA! Great insight right there. Does this mean I could replace the iDataListObjRef.$duplicate with the table class when manipulating iDataList in the UI? This could be a huge redesign of my code paradigm.

[Andreas Pfeiffer]

Martin,

Yes – you can have as many methods you want in your table class and I would recommend that most of the methods supposed to be written in an abstract way and sit in the super table class. That way you would be able to re-use them for all of your lists and rows. Remember the list or row is the instance of a table class.

Attached please find your library modified so that it uses the table class. There is a method $hasduplicates that can be used to proof if there are any duplicates and also there is a method that shows the duplicate values.

Note I was using Omnis Studio 11.1.

Best, Andreas

• This reply was modified 7 months, 2 weeks ago by  Andreas Pfeiffer.

dialogform_container-2.lbs 50 KB Omnis LBS - Click to view

Options

• Copy Download Link

[Obonye]

Hi Andrea’s,

Passing the first column selects the duplicates okay. Passing the next column seems to fail. Do I need to return the duplicate list for each column passed?

Martin.

[Andreas Pfeiffer]

Martin,

I am not sure what you are trying to do here. Maybe instead deleting the lines you could make it a $smartlist=kTrue and then use the $filter method instead deleting the rows that are not duplicate? That way you could remove the filter and do another go with a different column?

Best,

Andreas

[Andreas Pfeiffer]

Martin,

I modified the $showduplicates method in the table class using the $filter instead of deleting:

Do $cinst.$smartlist.$assign(kTrue)
Do $cinst.$unfilter()
Do $cinst.$sort($ref.[pColumnName])
For $cinst.$line from 1 to $cinst.$linecount
If $cinst.[$cinst.$line+1].[pColumnName]=$cinst.[pColumnName] ## if the next line in the current instance is identical then we can mark this line as a duplicate line
Do $cinst.$selected.$assign(kTrue)
Else
Do $cinst.$selected.$assign(kFalse)
End If
End For
#Do $cinst.$remove(kListKeepSelected)
Do $cinst.$filter($ref.$selected)

Note that I also needed to use an Else in order to change the selected status from a prior call. Actually this could also be done making a one liner instead of the If – Else construct. But that might be difficult to read.

Best,

Andreas

[Obonye]

Hi Andreas,

I prefer a one-liner to an IF statement. I had this $sendallref function

Do <b style=”background-color: var(–bb-content-background-color); font-family: inherit; font-size: inherit; color: var(–bb-body-text-color);”>pDataList.$sendall($ref.$selected.$assign(kTrue),$ref.[<b style=”background-color: var(–bb-content-background-color); font-family: inherit; font-size: inherit; color: var(–bb-body-text-color);”>pColName]=<b style=”background-color: var(–bb-content-background-color); font-family: inherit; font-size: inherit; color: var(–bb-body-text-color);”>pDataList.[$ref.$line+1].[<b style=”background-color: var(–bb-content-background-color); font-family: inherit; font-size: inherit; color: var(–bb-body-text-color);”>pColName]&$ref.[<b style=”background-color: var(–bb-content-background-color); font-family: inherit; font-size: inherit; color: var(–bb-body-text-color);”>pColName]<>”)

in an object class but I’m not sure how it would work in a table class instance. I’d appreciate a one-liner with the $filter.

Martin.

[Andreas Pfeiffer]

Hi Martin,

Something went bunkers with this post. I am not able to read it. When you copy code into the forum please make sure using the preformatted tag in the editor.

Regarding the table class, it is rather simply: Imagine that your code is inside the list. That way instead using the list name you would simply use “$cinst”. For for example $cinst.$sendall() would perform a sendall on the list if this code is inside the table class that is used for this list.

Best,

Andreas

• This reply was modified 7 months, 2 weeks ago by  Andreas Pfeiffer.

[Obonye]

Hi Andreas,

I can’t seem to find the preformatted tag in the method editor. Using $OS 10.22. Where is it located? Could you confirm if a $sendallref command accepts multiple conditions for the $filter and would it also apply in the $filter command? Would make it simpler to just share the complete line for the $sendall to minimize the back and forth. Kindly.

Martin.

[Andreas Pfeiffer]

Martin,

the pre-formatted tag is in the editor of this forum. When you enter text here there is a little formatting menu underneath which starts with something like “Aa”, camera symbol, attachment symbol, smiley. Click on the “Aa” which opens the text formatting feature. This allows you to make the text bold, italic add bullet points. The one which looks like </> is the pre-formatted text that allows you to copy and paste code freely without being scared that it will look weird. I use this for the coding part.

Best,

Andreas

[Andreas Pfeiffer]

Yes – you could make your argument longer in the $sendall as well as in the $filter by using the ampercent “&” sign or the pipe symbol “|”. The ampercent is the logical “AND” and the pipe symbol is the “OR”. You can also use parentheses to group logical expressions.

That said a $sendall can become very long horizontally and therefore un-readable and hard to maintain. I would avoid too many logical arguments. What would you do if you would need to search for 100 columns?

That is the reason I broke it up into two separate methods (see the post below) but still using the $sendall to call the sub routine from the main method.

I hope this makes sense.

Best,

Andreas

[Obonye]

What I’m trying to do is find duplicate entries in iDataList with multiple columns say, lEmployeeNum,lEmployeeFullName,lEmailAddress. If any of the values in the any of the columns is duplicated or missing, then alert the user.

[Andreas Pfeiffer]

Martin,

I am not so sure if this is always what you want. For example if you are checking the “name” column and the “city” column – as per your description – it will not allow to have a record where the city is the same as in any other record. Ok – you wouldn’t probably check city. It might be a bad example then.

However I enhanced the code a little bit. As for table classes “$cinst” always refers to the list (or row) that is used with that table class. Or in other words: The list is the instance of the table class.

First you were asking for a one liner in $hidenonduplicates formerly know as $showduplicates:

Do $cinst.$sort($ref.[pColumnName]) ## sorts the list on that column
Do $cinst.$sendall($ref.$selected.$assign(kTrue),$cinst.[$cinst.$line+1].[pColumnName]=$cinst.[pColumnName])

Note, that the assignment is always true if the filter returns true. I would normally put the part that is now in the filter into the assignment but hang on, you were asking to check multiple columns. Therefore I renamed the method into $hidenonduplicates.

Now in the new $showduplicates the parameter becomes a row “pColumnNameRow”:

Do $cinst.$sendall($ref.$selected.$assign(kFalse)) ## make sure no line is selected
Do $cinst.$smartlist.$assign(kTrue) ## that allows to use the $filter
Do $cinst.$unfilter() ## just in case we run this again
Do pColumnNameRow.$cols.$sendall($cinst.$hidenonduplicates(pColumnNameRow.[$ref.$name]))
Do $cinst.$filter($ref.$selected)

<font face=”inherit”>As you see here the first line is making all lines of the list un-selected. Also this is the place to make the list becoming a </font>smart list and un-filter.

It is the using a sendall that loops through the columns of the parameter row and calls the $hidenonduplicates method with the content of each of the values in that row.

That way you can now select all possible duplicates several times – actually for each column of the parameter row.

I also changed the $hasduplicates method so that it actually uses the same approach but removes the filter before the stack ends. Only returns if there are actually duplicates or not:

Do $cinst.$showduplicates(pColumnNameRow)
Calculate lHasDuplicates as $cinst.$linecount>0
Do $cinst.$unfilter() ## we do not want to filter in this case
Quit method lHasDuplicates

lHasDuplicates is a boolean local variable.

Next you would need to change the call. So instead using a scalar parameter you would need to use the row() function for the new methods, i.e.

Do iDataList.$hasduplicates(row(‘name’,’city’))

I think there are still some caveats with this approach. What if you want to find duplicates where only two or more columns together determine if this is a double record?

Or if a record is double, as for now the selection is on the first record sorted on that column. Meaning that if you have two records with the name “Charly” it will then mark the first Charly as the double one. No clue if that is fine or if you better select the 2nd one?

Attached please find the new library.

Best,

Andreas

dialogform_container-3.lbs 50 KB Omnis LBS - Click to view

Options

• Copy Download Link

[Obonye]

Hi Andreas,

Thanks for the quick response. ‘City’ can be duplicate therefore shouldn’t be checked. There are unique fields/columns that a user will want not to duplicate like national ID, bank account, email address, mobile number, social security number ETC. Before he/she clicks save button, an alert is returned showing duplicate or missing values. This minimizes data entry errors that updates untidy/unnormalized data.

Martin.

[Andreas Pfeiffer]

Ok – so this would normally be part of the database responsibility. When you set up a unique index on that column the insert should fail and you could return a proper error message when inserting the data. But maybe you want this before hitting the “save” button I assume.

Best,

Andreas

[Obonye]

Thank you for the extra push in this. The drawback with setting up the unique column in the database is that it would mean the duplicate is returned 1 by 1. If there are many duplicates and/or missing values, (especially import of many records), the user will solve one duplicate then clicks save button hoping the duplicate is gone, then another check error is returned for the next duplicate. Some will find this an unfriendly user experience.

[Obonye]

Please find attached an excel representation of an abstract scenario for duplicate and how best I could redesign a solution in Omnis:

duplicate.xlsx 9 KB Excel Spreadsheet - Click to view

Options

• Copy Download Link

[Andreas Pfeiffer]

Martin,

after thinking about that issue when you pass in a parameter from the UI which of the columns need to be checked – it is still not OO. The task to control the business rules – in that case which columns are unique should be encapsulated in the list not in the remote form instance.

But this is easy to achieve. Each inherited table class that uses a schema class and therefore the method in the super table class can also access any of the column properties of the attached schema class.

So what you can do is to set a marker in the columns in question in the schema class. I used a “{u}” – so open curly brackets, u (unique) and close curly brackets.

Here is the change in $showduplicates

Set reference lSchemaRef to $schemas.[$cinst.$sqlclassname] ## this is the schema class that is attached to the concrete table class - remember this code would normally be in your super table class
If lSchemaRef
Do lSchemaRef.$objs.$sendall($cinst.$hidenonduplicates($ref.$name),contains($ref.$desc,'{u}'))
Calculate lHasDuplicates as $cinst.$linecount>0
Else
Send to trace log 'instance does not have a schema when running $showduplicates'
End If

So instead of using a parameter we now use the description property of the columns in the attached schema class.

And we assume that the code that we have written will be in the super table class and hence it will work on all inherited table classes.

From now on you do not even need to remember this when you design the UI part.

I hope this makes sense. Library attached.

Best,

Andreas

• This reply was modified 7 months, 2 weeks ago by  Andreas Pfeiffer.

dialogform_container-4.lbs 50 KB Omnis LBS - Click to view

Options

• Copy Download Link

[Obonye]

Hi Andreas,

I wish to report back that the solution worked well. Thank you.

Martin.

[Andreas Pfeiffer]

Martin,

You are very welcome!

Thanks for your feedback.

Have a great day.

Best,

Andreas