Grid Peek / Cancel Find

Gary P

Active Member
Hey guys,

We have a custom application on power forms called Part Inquiry. In part inquiry, the user enters in a part number/branch and hits find. They can then see sales orders, purchase orders, and all kinds of information on a part. It is one of our most popular applications. I want to make it faster.

The power form has seven subforms that each have a grid on them. To save space, each subform is shown in a tab; meaning the user only sees one of these subforms at a time. When the user hits find on the parent, each subform runs a find on the first page of its grid and returns the number of records it found to the parent. If records were found, I show a notification to the user. If there are no records, I hide the notification. This way, the user can see instantly if there are sales orders, purchase orders, quotes, etc for a part without having to look at each tab.

The problem with this is that you can imagine doing seven finds takes more time than doing one, especially since the user is only viewing one grid at a time. However, the user needs to see that notification, and doesn't like looking at each tab to see if its grid is populated.

What I want to do is when the user clicks find, get that notification to the user in the fastest way possible, and only load in the grid they are looking at. Because there are numerous filter fields and QBE columns on each subform, the only reasonable way I can think of to do this reliably is to just check and see if even one record will populate into the grid. Then use that to show or hide the notification. Then I can only populate the grid the user is looking at, and populate each grid when the user selects that tab.

I have a way of telling the subform whether it is loading its grid or only checking for the presence of one record. The difficulty I have is in finding a way to tell the grid to stop running the find routine when a record has been found. The Suppress Find function only appears to work before the Find has begun processing. Once the find process has begun, I can't find a way to get out of it until the full page has been populated.

Any insights?
 
It's hard to say since we don't know what the app really requires as far as those filters.
This is how I would change it:
First, only notify the child that is presently 'showing' when the 'find' is pressed. This will mean only 1 grid gets populated. You could write some kind of 'tab selected' logic to press other child 'find' buttons as users move about.

Finding records on hidden tabs is a waste of time. 7 grids that return 10 records each (at a minimum) will make you wait for 70 fetches, not including formatting (or whatever else your grids do).

As for the notification for related tables, I would write a BSFN that returns True/False for each type of record searched for. Give it the minimum requirements and let it look for 'one' record in the related tables. Call it from the find button and report on the 'True/False' flags that it returns.
 
I agree that finding records on hidden tabs is a waste of time. The problem is I seem to only be able to do either a full find or no find at all. I would greatly welcome an alternative.

Since a business function or custom business view can't take into account any QBEs that the user may have, I don't think I could use a BSFN or NER without using gigantic if statements to account for every possible combination of values, so that's why I like how the grid will generate a SQL statement based on which fields are populated and which are not. I need a way to only have a grid load 1 record and then stop. I also would need to be able to change whether it only loads one record or a full grid page programmatically.

My first thought was to write some ER on the find button clicked event to grab the first record then cancel the find if I'm in check only mode. I couldn't find anything. My second thought was to put code in the "Grid Record is Fetched" event. If the grid was in checking mode, it would set a flag and then cancel the find. Suppress Find cannot be done in that event and suppressing the grid line just makes the fetch go on as though it never found the previous record.

Hence, I'm stumped.
 
If you just need to check if anything exists that meets the criteria I think you need to code a select/fetch statment that in effect would replicate the find query but only for one record/row. I think you knew that but wanted to find an easy way instead, right?
 
Close, but not quite.

A user could enter in just the item number, or could enter the item number, branch, and company, or every combination between. Additionally, the user may decide to see only open orders, meaning I have to add statuses to the select statement. Also, the user may want to see this for a specific customer, so I would need to consider that. Even before you factor in the possibility of other QBE columns the user may need to filter their results, the number of combinations is very large - too large to use ER/C IF statements for every possible way the user may want to view data. Adding in the additional overhead should I add a new column or search mode, and you can see this getting out of hand.

I want to capitalize on the functionality JDE uses to generate the SQL statement dynamically based on which form controls and QBE columns are populated. It appears as though there is no way to dynamically generate a SQL statement without using the grid, and once a grid begins filling itself, it keeps going until either it has filled a full page or found all the records that match its criteria. I sure hope I'm wrong.

Thank you for your help,
Gary
 
Hello there,

I didn't want to "revive" an old thread like this one, but I have a similar problem and couldn't find an answer in this thread.
Imagine you have P4210 and a lot of additionally joined tables. The main table contains round about 30,000,000 records. Due to the large amount of records, searches without an appropriate filter selection by the user could take up to 3 minutes which is very frustrating.
That's why we need a function to CANCEL the find process of the grid.
Like you hit the find button, notice after some seconds that you forgot to set two FCs to constrain the result and want to stop the "Find" as you know that it could last several minutes.

Is there any way to interrupt?
 
Last edited:
Maik,

Sure there's a way to cancel a Find - if you're on a recent release. What's yours?
 
Sorry, forgot the info text in the signature :).
We're on EnterpriseOne Tools Release: 9.1.4.

I know that you can cancel the Find after pressing the "Go to end" button by "Stop and view results", but we need to cancel the initial Find.
If I remember correctly, in XE, there was a button at the buttom on the right with a police man and a whistle to stop the Find. Did he retire? :)
 
Hi MaikS90,

There are several forms out there that monitor if any of the filter fields have been set. The action is, if Find is pressed and no filter fields have been populated, cancel the Find before starting. Try using this logic. or, You could put logic in all of your filter fields on Exit that "counts" the field as populated and only allow the Find to process if more than 0 filter fields are populated. Or you could use the 'counting' and a Message form (with the OK and Cancel buttons) and ask the user if they would like to exit and populate a filter field (Cancel) or proceed with the Find (OK).

One other option would be to disable the Find button and enable a message that says "Enter filter criteria to enable the Find Button" and add the necessary logic. Then if they populate a filter and then clear it again - that's on them.

Ben again,
 
Hi Ben,

thank you for your advice.
This is a solution we've already discussed with our customers and they do not really like it because they do not want to be restricted by this "duty".
Additionally, it is almost impossible to decide which filters are necessary. With some filter combinations you don't need that filter X or Y, with others it seems beneficial. How to decide this and set up "ruies" with over 100 QBE columns?

Another big disadvantage is that there are many saved queries for this application and if you use these queries, none of the filter columns (nor FCs or QBE columns) is populated so i'm not able to check if they are populated.
Because of the complexity of the saved queries, you don't not need other filter fields most often.


Maik
 
Hi Maik,

Then I would do the following.

Add a checkbox called Override Find which will Enable the Find Button when checked (I would actually just enable when clicked and not care whether checked or not).

Disable the Find button until either....
the user populates a filter field or checks the checkbox - It's one click. It's easy. Now they have acknowledged (by clicking the checkbox) that they are aware that it may take 3 minutes to populate the grid, and if they choose to do this without adding filter values then they've only wasted one click.

If you want to come back with "that's one click too many" then you'll just have to design a neural transmitter for them.

Ben again,
 
Perhaps the answer(s) aren't in changing the Find behavior but rather improving performance so its not such a big issue.
30,000,000 IS a lot of rows - but it can be manageable.

You mention "... a lot of additionally joined tables". Is this done in a JDE view? A database view (represented as a JDE virtual table)? Or a simple JDE View augmented by multiple Selects/Fetches in the Grid Row Fetched (?) event?

Has analysis been done on the queries/SQL triggered by a find that indicate what table(s) / views have the long waits?

Are the databases indices optimal for the types of queries performed?

Is the Database (If custom tables) and/or code design sub-optimal? Perhaps you need someone with a different perspective to look at the design.

All the above assumes of course that the hardware is up to snuff.
 
Hello again,

If you want to come back with "that's one click too many" then you'll just have to design a neural transmitter for them.
Ben again,
Exactly this.
Silly as it sounds because it's just one click, but that's it. Someone is very sensitive on that score.

The tables are both joined with a database view via SQL (CREATE VIEW...) and a JDE View.
So there are 2 database views (prefix "FV"):
- FV57421T (F4211 joined by 4 tables UNION F42119 joined by the same 4 tables)
I got the question here, if it might be better (performance) to do the UNION with F4211 and F42119 first and then add the 4 join tables?
E.g.: (A joined by B,C,D,E) UNION (A9 joined by B,C,D,E) versus (A UNION A9) joined by B,C,D,E

- FV574201 (F4201 UNION F42019) (You could do this also with a JDE view.)

These 2 database views are joined with a JDE view V57CSI2B (FV57421T joined by FV574201 and F03012). Maybe the complexity of the view is the reason. Is it better to do the joins in one single database view?

Your CNC support team already inspected the most common query patterns and created some suggested indices. I got the impression that some queries are faster than before.
But depending on the total number of records as search result, the seek time varies very much from seconds to minutes. Don't take the numbers below too seriously. It's just an illustration as I don't know the exact numbers. The grid displays 50 rows after the "Find".
E.g.: AN8 = 123456 --> total result 2,000 records, seek time: 8s
AN8 = 556677 --> total result 20,000 records, seek time: 2-3 min

Nevertheless we created a "light" version of this application by using only the first database view. This copied view also contains only 2 joins, but still includes the 30 mio records from F4211 and F42119.
After performance tests, we came to the conclusion that it's much faster on average than the original application but still shows seek time peaks.
I suppose the biggest problem here is the huge number of rows.
 
Back
Top