Records disappear and reappear

Classic Servoy
1

Hi all

I have an odd problem with a few of my solutions

I have a parent ‘sale’ record, which has no relation to a tab with ‘line items’.

When the parent sale record is selected, it does a sql query on the table with line items, and returns all of the items belonging to that ‘sale’. The reason I don’t use a relation is that I want the line items to be selectable by group (show this group of line items for this sale, or that group for this same sale, etc.).

My code to retrieve the dataset onRecordSelection of the ‘sale’ is:

	var sql_query = "SELECT line_itemsid, sales_id, hidden_from_estimate "
    + "FROM line_items "
    + "WHERE sales_id = ? AND hidden_from_estimate != 1"
    var vDataSet = databaseManager.getDataSetByQuery(databaseManager.getDataSourceServerName(controller.getDataSource()), sql_query , [forms.sale_master.salesid],-1)	
	
	forms.line_items_complex_master.controller.loadRecords(vDataSet)

When I do a search for a particular sale (specifically nr. 169) in a live system, it shows only 6 line items. However when I go to print that sale, it reloads the dataset (to eliminate the group you may have been on), loads the line_items records for that sale into a print form, and prints, and always loads the correct number of lines (26 lines).

If it is ‘stuck’ on 6 lines, and I go to a form that has nothing but line items, and search manually for sale id 169, it shows only 6 records, however, if after trying to print I do the same thing, it shows 26 lines.

There some tableFilterParams, and foundsetFilterParams in various methods, but they are all commented out, and there are no calls to clear the filters anywhere (so if it is that, how do my 26 records get restored after trying to search for the items as part of a print?)

It is repeatable and consistent, and always initially shows 6 lines for that one sale, but all other sales are fine, and always prevents me from finding the full 26 if I search a form with just line items on it.

If I load all lines by trying to print, and then quit / restart client, I get the problem back.

Anyone seen anything like this before?

:)

Bevil

2

Are you loadding maybe data into a related foundset?? Try to make the form a separate foundset. And look into the servoy-admin page what the differences are in the created sql queries what Servoy fires

3

Hey Harjo, thanks for answering.

The tabpanel where the missing data should be is an unrelated tab. I do the sql query onload on the parent form and loadRecords(dataset) into the form in the tab onRecordSelection.

No relations at all, by design.

Also, even if I forget the main form, and go to a completely separate form with just the line items on it, and then CMD F - type 169 into salesid field, and press return, it only shows 6 records when it should show 26. It behaves as though there is a foundsetFilterParam or tableFilterParam. However I don’t ever clear the filter to find the missing records again, I just have to initiate a print which loads the same sql query data into a different form (also based on the line items table.), and voila, the records are back.

4

Look at your servoy-admin page for differences in queries. That should point you in some direction

5

Hi Bevil,

Does the dataset (vDataSet) also return 6 rows or is it only when you load the dataset in the controller?

6

Hi Robert

Both a manual find on the form, and the sql query return 6 rows.

Until I do a print - whose sql query is the same as an the onRecordSelection method, after that, it shows 26 records.

While it is stuck on 6, I can go to another form entirely, which based on the line_items table (no relations, no tabpanels, nothing but a straight table view form of line_items), do CMD-F, type my weird sales id (169), press return, and still get just 6 records.

After I clear the problem (by initiating a print), and do the same search, on the same form, I get 26 records.

This issue doesn’t seem to affect any other saleid, just this one particular one.

7

Bevil, read my lips… :)
What does the servoy-admin page (the performance tab) tell’s you in those two different results…

tip: clear it first, before you start monitoring

8

Hi Bevil,

Like Harjo says also check what actual SQL is send to the database.

And another quick test to rule out that the issue is in the DB (like a corrupt index) can you run the following query in a query editor and see if you get your 26 rows or only 6:

SELECT line_itemsid, sales_id, hidden_from_estimate 
FROM line_items 
WHERE sales_id = 169 AND hidden_from_estimate != 1

Do you have an index on this sales_id column ?

9

Hey Harjo and Robert.

I have now had a chance to do the SQL check on another solution - where one is a much later version of the other, and they BOTH display this problem. One is Sybase, the other Postgres, so I guess it must be something in my coding…

In the Sybase version, the direct lines search is:

00:00:092 1 00:00:092 Find select line_itemsid from line_items where sales_id = ? order by line_itemsid asc

and in the sale record selection sql, the query is:

00:00:114 2 00:00:057 Custom SELECT line_itemsid FROM line_items WHERE sales_id = ?

I don’t know how a sort would affect it, but it is definitely behaving as though I have done a foundset or table filter parameter, and I have definitely not…

I have a screengrab video of the behaviour. If I search in all line items, some of the records just are not there, even if I search for their PK directly, it says no records found… Log out and log back in, and they are back…

VERY strange