My App runs on a MS SQL DB. Amongst other I have two tables that related:
Profiles (With a unique, autonumber Key for SN)
The Profile_Addresses, also With a unique, autonumber Key for SN, and another foreign key for a field called P_SN.
In the Clarion Dictionary the relationship is set as follows Profiles.SN = Profile_Address.P_SN (1 to many)
This work 100% when adding addresses, the P_SN is set to the Profiles.SN
The problem is this:
When I call the addresses procedure it is filtered by File_relationship. This correctly only display the addresses of the record selected in the Profiles browse.
But, it duplicates the one record.
For example: I have a profile with SN = 1. This profile has 2 addresses.
When I open the addresses browse, it shows the first address in row 1, the second address in row 2 and again the first address in row 3.
And further to that, when I then edit one of the addresses and the focus returns to the browse, only the address I edited is shown in the list.
This of course still works, but it looks absolutely un-professional.
Thanks guys
I managed to fix it now, although I must confess I don’t understand why it is fixed.
The primary key definition is 100% correct, and I previously added the additional sort field
The following seems to have fixed it:
I added a local variable (Loc:P_SN) and after opening the files I assigned this the value from the main file .Then I added this as a display only on the browse screen, under the browse list. And it worked…
I will play around more to try and understand what exactly is happening and post the result here in case someone else have the same issue.
Make sure the SORT order being used in your browse’s VIEW is unique. Often adding the unique identifiers for the tables to the end of the sort order does the trick.
This issue is the browse/view class uses RESET on saved POSITION information for the rows and then does NEXT/PREVIOUS commands to fill the list. If the RESET(position) command isn’t guaranteed to go to one and only one row in the view, then you’ll see duplicate lines as you move up and down in the list.
While non-unique browse sort order is the most common cause of duplicated rows, there might be another cause of duplicates or filtered out records when using MS SQL. It is well described in Clarion documentation for CHECKFORNULL:
When browsing through a table, it is sometimes necessary for the driver to request all rows that are at, or before, the current row. It does this by generating a WHERE clause. For example: WHERE (field1 <= value) AND (field1 < value OR field2 <= value2)
The above example is for a two component key. For more components, the WHERE clause gets longer, and this will work well in most cases. However, in SQL, if a field has a NULL value, then field < value is false, field = value is false, and field > value is also false.
SQL table field may be NULL-able itself. Besides, a JOIN-ed table may not have a match for the primary table record retrieved. INNER JOIN will exclude such a tuple completely, while Clarion default LEFT OUTER JOIN will retrieve all the secondary table fields as NULL values.
An attempt to set browse sort using a field containing a mix of NULL and non-NULL values will fail to display such a record set correctly. This may become an issue using predefined SET(key) browse sort order or whatever else method to set BrowseView{Prop:Order}, especially when using ABC Browse “Sort Header” feature.
I have come across such an issue quite recently. And from my experience, CHECKFORNULL field modifier does not help to solve this common problem completely. I have got an idea and created a template extension to fix Browse “Sort Header” behavior for fields having NULL values retrieved from SQL database. So far so good, new template with accompanying class seems working well. And probably I will make it available among other my third-party products if anyone interested.
