Queries and Can-Find

[RealHeavyDude]

I've been able to find the code sample - and - yesterday I forgot that the columns get removed from the browse widget when you set it's query attribute to NULL ...

Here's a working code sample (this time it's syntax checked ...):

&SCOPED-DEFINE QUERY-WHERE 'FOR EACH cfr_InstrumentId NO-LOCK, FIRST cfr_InstrumentIdScheme NO-LOCK WHERE cfr_InstrumentIdScheme.instrumentIdScheme_obj = cfr_InstrumentId.instrumentIdScheme_obj INDEXED-REPOSITION':U

DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO.
DEFINE VARIABLE hQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE hInstrumentId AS HANDLE NO-UNDO.
DEFINE VARIABLE hInstrumentIdScheme AS HANDLE NO-UNDO.
DEFINE VARIABLE hField AS HANDLE NO-UNDO.
DEFINE VARIABLE cFieldList AS CHARACTER NO-UNDO.
DEFINE VARIABLE cFieldName AS CHARACTER NO-UNDO.
DEFINE VARIABLE cTableName AS CHARACTER NO-UNDO.
DEFINE VARIABLE iField AS INTEGER NO-UNDO.
DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.

/* Assign the variables we need later */
ASSIGN hBrowse = BROWSE {&BROWSE-NAME}:HANDLE
hInstrumentId = BUFFER cfr_InstrumentId:HANDLE
hInstrumentIdScheme = BUFFER cfr_InstrumentIdScheme:HANDLE
cFieldList = '{&FIELDS-IN-QUERY-{&BROWSE-NAME}}'. /* Pre-Processor containing the fields */

/* Eventually clean-up the dynamic query */
IF VALID-HANDLE ( ghQuery ) THEN DO:
IF ghQuery:IS-OPEN THEN
ghQuery:QUERY-CLOSE ( ).
DELETE OBJECT ghQuery.
ASSIGN ghQuery = ?.
END.

/* Create the dynamic query */
CREATE QUERY ghQuery.
ghQuery:SET-BUFFERS ( hInstrumentId, hInstrumentIdScheme ).
ASSIGN lOkay = ghQuery:QUERY-PREPARE ( {&QUERY-WHERE} ) NO-ERROR.

IF lOkay THEN DO:

/* Close the query that is currently attached to the browse widget when it is valid */
ASSIGN hQuery = hBrowse:QUERY.
IF VALID-HANDLE ( hQuery ) THEN
hQuery:QUERY-CLOSE ( ).

/* Switch the queries */
ASSIGN hBrowse:QUERY = ?.
ASSIGN hBrowse:QUERY = ghQuery.

/* Need to set the expandable attribute of the browse to FALSE, otherwise the first added column will fill the widget */
ASSIGN hBrowse:EXPANDABLE = FALSE.

/* Populate the browse widget with the fields as they were */
DO iField = 1 TO NUM-ENTRIES ( cFieldList, ' ':U /* List is seperated by blanks */ ):

/* Extract the field and table names from the list */
ASSIGN cFieldName = ENTRY ( 2, ENTRY ( iField, cFieldList, ' ':U ), '.':U )
cTableName = ENTRY ( 1, ENTRY ( iField, cFieldList, ' ':U ), '.':U ).
/* Grab the handle to the buffer field from the corresponding buffer */
IF cTableName = 'cfr_InstrumentId':U THEN
ASSIGN hField = hInstrumentId:BUFFER-FIELD ( cFieldName ).
ELSE IF cTableName = 'cfr_InstrumentIdScheme':U THEN
ASSIGN hField = hInstrumentIdScheme:BUFFER-FIELD ( cFieldName ).

/* Now add the field to the browse widget */
IF VALID-HANDLE ( hField ) THEN
hBrowse:ADD-LIKE-COLUMN ( hField ).

END. /* Populate the browse widget with the fields as they were */

/* Open the query */
ASSIGN hBrowse:EXPANDABLE = TRUE.
ghQuery:QUERY-OPEN ( ).

END.

&UNDEFINE QUERY-WHERE

HTH, RealHeavyDude.

 

[MauditOstie]

Oh! That looks just about right!!!

I'll try it later today and let you know!
Tanks a lot RHD!

 

[MauditOstie]

Another Step closer!
But no cigars... again hahaha
I'm trying to fix my problem!
It's coming from the fact that i have a calculated field in my columns!
So since they are seperated by Space, this causes me a problem.

GetStatusDesc(nss-employee.status-code) @ v-statusdesc

Any other way we can get the list then the pre-proc so i can add comas or semi-columns instead of spaces?

 

[RealHeavyDude]

Now you got me with my pants down ... :awink:

The logic I provided you with was never designed to handle calculated fields - personally I try to avoid them as much as possible.


As soon as I have an idea how to handle calculated fields I will let you know.

Regards, RealHeavyDude.

 

[RealHeavyDude]

I think I've found the root of all evil:

/* Switch the queries */
/* ASSIGN hBrowse:QUERY = ?. */
ASSIGN hBrowse:QUERY = ghQuery.

From what I've seen, if you comment out this line and the section where the columns get added to the browse widget you should be done - my fault, it's been a long time since I revisited that logic ...

Regards, RealHeavyDude.

 

[RealHeavyDude]

Probably now's the time to use a dynamic browse ...

Definition section:

DEFINE VARIABLE ghQuery AS HANDLE NO-UNDO.
DEFINE VARIABLE ghBrowse AS HANDLE NO-UNDO.
DEFINE VARIABLE ghInstrumentId AS HANDLE NO-UNDO.
DEFINE VARIABLE ghInstrumentIdScheme AS HANDLE NO-UNDO.
DEFINE VARIABLE ghField AS HANDLE NO-UNDO.
DEFINE VARIABLE gcInstrumentName AS CHARACTER LABEL "Instrument name" FORMAT "x(50)" NO-UNDO.


/* Assign the variables we need later */
ASSIGN ghInstrumentId = BUFFER cfr_InstrumentId:HANDLE
ghInstrumentIdScheme = BUFFER cfr_InstrumentIdScheme:HANDLE.

Function to create the dynamic browse:

FUNCTION createBrowse RETURNS HANDLE
( INPUT phQuery AS HANDLE ) :
/*------------------------------------------------------------------------------
Purpose:
Notes:
------------------------------------------------------------------------------*/

DEFINE VARIABLE hBrowse AS HANDLE NO-UNDO.

CREATE BROWSE hBrowse
ASSIGN FRAME = FRAME {&FRAME-NAME}:HANDLE
QUERY = phQuery
COLUMN = 2
ROW = 1.24
WIDTH = 129
HEIGHT = 16.91
SEPARATORS = TRUE
ROW-MARKERS = FALSE
VISIBLE = TRUE
SENSITIVE = TRUE
TRIGGERS:
ON ROW-DISPLAY PERSISTENT RUN rowDisplay ( hBrowse ).
END TRIGGERS.


/* Add the columns */
hBrowse:ADD-LIKE-COLUMN ( 'cfr_InstrumentIdScheme.schemeSymbol':U, 1 ).
hBrowse:ADD-LIKE-COLUMN ( 'cfr_InstrumentId.idSymbol':U, 2 ).
hBrowse:ADD-CALC-COLUMN ( 'CHARACTER':U, 'x(50)':U, '':U, 'Instrument name':U, 3 ).
hBrowse:ADD-LIKE-COLUMN ( 'cfr_InstrumentId.logicDeletionType':U, 4 ).
hBrowse:ADD-LIKE-COLUMN ( 'cfr_InstrumentId.validFromDate':U, 5 ).
hBrowse:ADD-LIKE-COLUMN ( 'cfr_InstrumentId.validUntilDate':U ).

ASSIGN ghField = hBrowse:GET-BROWSE-COLUMN ( 3 ).

RETURN hBrowse. /* Function return value. */

END FUNCTION.

Procedure that fires for the ROW-DISPLAY trigger:

PROCEDURE rowDisplay :
/*------------------------------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
------------------------------------------------------------------------------*/

DEFINE INPUT PARAMETER phBrowse AS HANDLE NO-UNDO.

DEFINE BUFFER b_cfr_inst FOR cfr_Instrument.

/* Insrument identification must be available */
IF ghInstrumentId:AVAILABLE THEN DO:

FIND b_cfr_inst NO-LOCK WHERE b_cfr_inst.instrument_obj = ghInstrumentId::instrument_obj NO-ERROR.
IF AVAILABLE b_cfr_inst THEN
ASSIGN ghField:SCREEN-VALUE = b_cfr_inst.shortName_E.

END.

END PROCEDURE.

Last, but not least, a procedure to create the dynamic query:

PROCEDURE switchQuery :
/*------------------------------------------------------------------------------
Purpose:
Parameters: <none>
Notes:
------------------------------------------------------------------------------*/

&SCOPED-DEFINE QUERY-WHERE 'FOR EACH cfr_InstrumentIdScheme NO-LOCK, FIRST cfr_InstrumentId NO-LOCK WHERE cfr_InstrumentId.instrumentIdScheme_obj = cfr_InstrumentIdScheme.instrumentIdScheme_obj INDEXED-REPOSITION':U

DEFINE VARIABLE lOkay AS LOGICAL NO-UNDO.

/* Eventually clean-up the dynamic objects when they're already existing */
IF VALID-HANDLE ( ghQuery ) THEN DO:
IF ghQuery:IS-OPEN THEN
ghQuery:QUERY-CLOSE ( ).
DELETE OBJECT ghQuery.
ASSIGN ghQuery = ?.
END.
IF VALID-HANDLE ( ghBrowse ) THEN DO:
DELETE OBJECT ghBrowse.
ASSIGN ghBrowse = ?.
END.

/* Create the dynamic query */
CREATE QUERY ghQuery.
ghQuery:SET-BUFFERS ( ghInstrumentIdScheme, ghInstrumentId ).
ASSIGN lOkay = ghQuery:QUERY-PREPARE ( {&QUERY-WHERE} ) NO-ERROR.

IF lOkay THEN DO:

/* Create the dymamic browse widget */
ASSIGN ghBrowse = createBrowse ( INPUT ghQuery ).

/* Open the query */
ghQuery:QUERY-OPEN ( ).

END.

&UNDEFINE QUERY-WHERE

END PROCEDURE.

HTH, RealHeavyDude.

 

[MauditOstie]

As soon as i assign the new query to the browser, all the fields are removed

Dammit... I'll have to go with the Dynamic fields as well...
Might take a while but i'll get back to you on it

Thanks a lot!

 

[MauditOstie]

Alright i started playing with the thing...
Do i need to initialise the browser ? Can i use my current browser called br_table and therefore change all the ghBrowse?

I tried it using this in the local-initialize and well, didn't work. It actually crashed my App builder completely hahahaha

 

[MauditOstie]

Here's what i got, it's giving me (at compile) that it can't find nss-employee.SSN in the Browser. This is from my Local-initialize:

 /* Include file required at the beginning of each internal procedure  */
  DEFINE VARIABLE cQueryWHere AS CHAR NO-UNDO.
  {system/method/proc.i}
  {ns/system/std.i}
  /* Dispatch standard ADM method.                             */

  RUN dispatch IN THIS-PROCEDURE ( INPUT 'initialize':U ) .
  ASSIGN cQueryWhere = SUBSTITUTE('FOR EACH nss-employee NO-LOCK  WHERE nss-employee.status-code <= 6, ~
                                      FIRST nss-employment WHERE ~
                                            nss-employment.SSR = nss-employment.SSR AND ~
                                            nss-employment.SSN EQ nss-employee.SSN AND ~
                                            nss-employment.Start-date LE &1 AND ~
                                            (nss-employment.End-Date GE &1 OR nss-employment.End-Date EQ ?)~
                                      INDEXED-REPOSITION':U,
                                 gdLoginDate). 
                                  
  RUN SwitchQuery ( cQueryWhere ).
/**** THE ERROR POPS DURING THE NEXT STATEMENT *****/
  ASSIGN
      nss-employee.SSN:LABEL-FONT IN BROWSE {&BROWSE-NAME}        = 9
      nss-employee.surname:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.birth-certif-no:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.passport-no:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.driver-licence-no:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.national-id:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.first-name:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9
      nss-employee.birth-date:LABEL-FONT IN BROWSE {&BROWSE-NAME} = 9.