E9.2 Set selection append flag not working on a Find button on a subform

[Vidushi]

Hello,

I have created a subform on a powerform. In that subform there is a Grid, a Form control and a Find Button. The Business view is a combination of F0101, F0116 and F03012. That Form control is State (F0116.ADDS) and after selecting the state one will click "Find" button and the records should be shown in the Grid. But I have to put more filters like
F0101.AT1 = C
F03012.BADT = S , X
F03012.CO = 00000
which i have written one the Find Button "Button Clicked" event like
Clear Selection(FC Grid)
Set Selection Append Flag(FC Grid, <Yes>)
Set Selection(FC Grid, "F0101", "AT1", <Equal To>, "C", <And>)
Set Selection(FC Grid, "F0116", "ADDS", <Equal To>, FC State, <And>)
Set Selection(FC Grid, F03012, "CO", <Equal To>, "00000", <And>)
Set Selection(FC Grid, F03012, "BADT", <Equal To>, "S", <And>)
//
Set Selection(FC Grid, F0101, "AT1", <Equal To>, "C", <Or>)
Set Selection(FC Grid, "F0116", "ADDS", <Equal To>, FC State, <And>)
Set Selection(FC Grid, F03012, "CO", <Equal To>, "00000", <And>)
Set Selection(FC Grid, F03012, "BADT", <Equal To>, "X", <And>)

Its working perfectly fine when i am selecting a State value and clicking Find. But now i want to put Address Number value in the QBE of the Grid and will Click find and it should find records accordingly(means along with the above filters show address number value) but that is not happening.

I tried all sorts of combination but its not happening means I tried putting one more selection with
Set Selection(FC Grid, F0101, "AN8", <Equal To>, QC AddressNumber, <And>) -- Not working

and i tried giving address number as a Form Control same as State and then putting one more selection like
Set Selection(FC Grid, F0101, "AN8", <Equal To>, FC Address_Number, <And>) -- Still not working

suggest me what i am doing wrong???

basically I want to search with State and Address Number value along with above filters.

Thanks in advance

 

[JMR]

Can you be more specific when you say "Not working"?

Are you getting any results?
Have you looked at the generated SQL statements to make sure those are what you're expecting?

There should be no reason to add the AN8 QC column to your selection. The forms engine will add it automatically assuming it is a BSVW column. And unless you add it to both sides of your 'OR' Set Selections, it won't produce the results I think you're expecting.

I am not sure how the Clear Selection system function works with filter fields and QC values, but I assume it will be fine since you say it works fine until you add the QC value.

Try removing any Set Selection for QC values and look at your generated SQL statements (jas.log or perhaps jdedebug.log)

If that doesn't work, try moving the Clear Selection to the Post Button Press of the Find button.

 

[BOster]

I can't tell you exactly what is going wrong. Only give a tip or an approach that may help??????? Your problem may be

Set Selection(FC Grid, "F0116", "ADDS", <Equal To>, FC State, <And>)

If FC State is already a filter field then this would be redundant so try removing that along with the "Clear Selection(FC Grid)" call.

1. In general I try to stay way from the "Set Selection" system function in APPLs given the dynamic nature of user defined queries in APPLs - especially if they venture off the form filter and QBE fields and set up a more complex query using the Query Manager. Sometimes its unavoidable (if you need some type of OR clause for example) so this is just a general approach. My *general* approach for things like this is to create hidden FC filter fields and set those instead. So instead of

Set Selection(FC Grid, "F0101", "AT1", <Equal To>, "C", <And>)
...
Set Selection(FC Grid, F03012, "CO", <Equal To>, "00000", <And>)

Create hidden FC filter fields and simply set their values:

FC szSearchTypeFilter = "C"
...
FC szCompanyCodeFilter = '00000'

I feel like this makes it easier to do ranges, !=, etc. and it feels like it is more like if the user actually put those values in and so maybe it will work better with the Query Manager????

So in your case, since you do have an OR condition, maybe try using FC filter fields for everything except BADT and then use the system function "Set Selection" for just that one field and remove the "Clear Selection" system call. And, as Jeremy said, pulling the generated SQL is usually the first step in solving query issues like this, so turn on the JAS logging and pull the query and review what its doing.


2. Power Forms. I have a love/hate relationship with Power Forms. I have used them exclusively almost since they were introduced but THEY. ARE. FLAKY/FINICKY/BUGGY. ESPECIALLY with what I call "data bound controls" where there is a business view tied to the form, and then FC, Grid, etc. controls tied to the business view. I still don't understand them completely and they drive me insane. Exponentially so if it is an Edit PowerForm and you are trying to edit values in a grid. I still can not tell you how to correctly edit values in a grid in a PowerForm attached to a business view... its a lot of trial and error every time for me. They work MUCH, MUCH better when the form is NOT attached to a business view and data is backed by other things like jdeCache, etc. All this too say it may just be some weird power form quirk that once you solve it you still won't know why it works or what you did to fix it.

 

[JMR]

Just another thought... When you get a look at your SQL statement, make sure the generated SQL has the following form (note use of parens):
(FC Filter fields and/or QC fields) and/or (Set Selections)

I know UBE's separate out Version data selection from ER Set Selections by use of parens. For some reason, I have a vague memory that APPL's do not separate the Filters/QC from the set selections. If that is the case, then because you have an -OR- condition, you will be forced you to code all of your selections via Set Selections.

However, if the generated SQL does indeed separate the Filters from the Set Selections by use of parens, then the only Set Selections you should have to code are the BADT ones (S or X). And then use Brian's idea to use hidden filter fields for all the other data items you need.

 

[Vidushi]

Reason for putting "Clear selection" - I am finding a State value for the first time, it is showing correct results but the second time onwards its not showing correct results. So, i thought the first selection is hampering the second selection results and onwards, So i tried clear selection before the selections and it worked.

Reason for putting "Set selection" for State though it is present as a FC control and i set the Filter as "=" in the properties - It is not working means not giving desired output that's why I put that too as a Set selection and it starts working.

I am applying all of your suggestions and will let you know. Thanks. @BOster @JMR

 

[JMR]

Without any Clear Selection and the use of Append Flag(<yes>), every time you 'Find', you will be incrementally adding each Set Selection to the previous. So a Clear Selection is necessary.

However, I always put it after the engine has performed the query (Post Button event of the Find button). This ensures any filter fields and/or QC fields are not cleared prior to generating the SQL.

If your ADDS Filter was not working without the Set Selection, something else is wrong. It may be related to the Clear Selection prior to the set selections... try moving it to the Post Button event.

 

[BOster]

However, I always put it after the engine has performed the query (Post Button event of the Find button). This ensures any filter fields and/or QC fields are not cleared prior to generating the SQL.

That's a great tip. This alone may fix the problem.

 

[BOster]

I usually try to avoid "Set Selection" on APPLs as much as possible. Seems like I always fight it.

Provided you have total control over objects involved there is another strategy (w/ two variants) you could use to avoid "Set Selection".

1a. If the values for BADT are fixed, you could put those in a "Constants" table and simply include the constants table in your Business View with a inner join on the BADT field.

1b. If the values for BADT are not know until runtime (i.e. dynamic), you can create a Work Table with BADT and a GUID as primary key, include the work table in the Business View, join on BADT, when user presses find (or when the values for BADT are determined), write the BADT values to the work table along with the GUID, set a hidden filter with the GUID value in the APPL like you would for the other fields, when values for BADT change, user exits form, presses find, etc. delete the values from the work table using GUID as selection. Works the same as in 1a but each user/instance of the APPL gets its own unique list of BADT (via the GUID field).