Product Configurator: gettabledata.p

In case anyone else ever needs to do anything like this here are the changes I made to the gettabledata.p file to get this to work.

/*------------------------------------------------------------------------

        
  Input Parameters:     tablename             
  ALL CHARACTER VALUES  key field
                        description field
                        where statement
  
  Output Parameters:
        opListItems     A comma separated list of selection items
        opInitialVal    The default value to use for the control

  Created: 09/12/03
  
  History:
  09/20/04  SCR8293 - Fixed an error where the query and buffer object were not deleted
            - Added to standard code per Doug Williams
  09/16/05 - DJP SCR 24986 - Change error message to be standard PublishEx
  02/06/06  bpm  scr28068 - If only 1 value is returned from the query then assign this 
                            value to opInitialVal so the value is defaulted in the input.
  9/24/12 - Bob added in a section to replace commas with spaces so that when displaying 
       descriptions the commas are not seen as separators and everything after the 
   comma shows as a new option in the drop down list.
  2/8/13 - JP added line to set initial value of drop down list if there are more than 1 items in the list.  Before this it only set the initial value if only one item was returned.
  6/25/15 - JP added in the variable ipc-defaultValue.  This is so that I can specify what part should appear in the list in the combo box first as the query pulls in 
all values in alphabetical order from a-z.  Call this program like the following: 
'part','partnum','partdescription','defualtValue',configinputname1,configinputname2,'part.company = "vn10t" and part.partnum begins "dcd-200-ml" and part.class = <var1>')
ex. "Part","PartNum","PartDescription","zzz ; test part","","","Part.PartNum matches 'A101' or Part.ProdCode matches 'FH_68'"
------------------------------------------------------------------------*/

/* ROB here is an example line of how you would call this thing:
  'part','partnum','partdescription',configinputname1,configinputname2,'part.company = "vn10t" and part.partnum begins "dcd-200-ml" and
   part.class = <var1>').
  IF you use a description field (3rd parameter) it will produce <keyfield>;<description>
  eg. keyfield data value followed by description field data value seperated by a ;
  IF you use a config input name (1 or 2) this will be inserted into the query string wherever <var1> and <var2>
  (corresponding to the configinputname1 or 2) is found.
*/

/********** This is required by the configurator code. ****************/
{ud/dynlist.i}
{lib/text.i}
/******************* Input Parameter Definitions **********************/
DEF INPUT PARAM ipc-table     AS CHAR NO-UNDO.
DEF INPUT PARAM ipc-keyfield  AS CHAR NO-UNDO.
DEF INPUT PARAM ipc-descfield AS CHAR NO-UNDO.
DEF INPUT PARAM ipc-defaultValue AS CHAR NO-UNDO.  /* New input var JP */
DEF INPUT PARAM ipc-param1    AS CHAR NO-UNDO.
DEF INPUT PARAM ipc-param2    AS CHAR NO-UNDO.
DEF INPUT PARAM ipc-where     AS CHAR NO-UNDO.

def shared var CUR-COMP  as char format 'x(8)' no-undo.
def var errMessage as char format 'x(60)' no-undo.

DEFINE VARIABLE hQuery  AS HANDLE NO-UNDO. /* dynamic query */
DEFINE VARIABLE hBuffer AS HANDLE NO-UNDO. /* table buffer */
DEFINE VARIABLE hKey    AS HANDLE NO-UNDO. /* buffer field name */
DEFINE VARIABLE hDesc   AS HANDLE NO-UNDO. /* buffer field name */

/******* Assign the dynamic list items and the initial value **********/
IF ipc-table = "" OR ipc-keyfield = '' THEN 
DO:
   /* MESSAGE 'Blank Table or Key Field Name.'. */
    assign errMessage = xlate('Blank Table or Key Field Name.').
    {lib/PublishEx.i
                &ExMsg = "errMessage"
                &ExTable = "'':U"
                &ExField = "'':U"
                &ExRowID = "'':U"
    }
    ASSIGN opListItems = "".
    return "":U.
END.

CREATE QUERY hQuery.
CREATE BUFFER hBuffer FOR TABLE ipc-table NO-ERROR.

IF ERROR-STATUS:ERROR THEN
DO:
    assign errMessage = xlate('Invalid Table Name.').
    {lib/PublishEx.i
                &ExMsg = "errMessage"
                &ExTable = "'':U"
                &ExField = "'':U"
                &ExRowID = "'':U"
    }
            
   ASSIGN oplistitems = "".
   RETURN "".
END.
 
IF INDEX(ipc-where,"'") > 0 THEN
ASSIGN ipc-where = REPLACE(ipc-where,"'",'"').

IF INDEX(ipc-where,"<var1>") > 0 THEN
ASSIGN ipc-where = REPLACE(ipc-where,"<var1>",'"' + ipc-param1 + '"').

IF INDEX(ipc-where,"<var2>") > 0 THEN
ASSIGN ipc-where = REPLACE(ipc-where,"<var2>",'"' + ipc-param2 + '"').


IF ipc-where = ? THEN
DO:
   ASSIGN oplistitems = "".
   RETURN "".
END.

hQuery:SET-BUFFERS(hBuffer).
hQuery:QUERY-PREPARE('for each ' + trim(ipc-table) + ' where ' + trim(ipc-table) + '.company = "' +
                      cur-comp + '" and ' + trim(ipc-where) + ' no-lock') NO-ERROR.
IF ERROR-STATUS:GET-MESSAGE(1) <> '' THEN
DO:
   assign errMessage = xlate('Invalid Where Statement: ') + 'for each ':U + trim(ipc-table) + ' where ':U + trim(ipc-table) + '.company = "':U +
           cur-comp + '" and ':U + trim(ipc-where) + ' no-lock.':U + xlate(' Error = ') + ERROR-STATUS:GET-MESSAGE(1).
   {lib/PublishEx.i
               &ExMsg = "errMessage"
               &ExTable = "'':U"
               &ExField = "'':U"
               &ExRowID = "'':U"
   }

   ASSIGN oplistitems = "".
   RETURN "".
END.

hQuery:QUERY-OPEN().
hQuery:GET-FIRST().

ASSIGN hKey  = hBuffer:BUFFER-FIELD(ipc-keyfield).

IF NOT VALID-HANDLE(hkey) THEN
DO:
    assign errMessage = 'Invalid Key Field.'.
    {lib/PublishEx.i
                &ExMsg = "errMessage"
                &ExTable = "'':U"
                &ExField = "'':U"
                &ExRowID = "'':U"
    }
   ASSIGN opListItems = "".
   return "":U.
END.
     
DO WHILE hBuffer:AVAILABLE:
     
     ASSIGN hKey  = hBuffer:BUFFER-FIELD(ipc-keyfield).
     
     IF ipc-descfield <> '' THEN
     ASSIGN hDesc = hBuffer:BUFFER-FIELD(ipc-descfield) NO-ERROR.

     IF VALID-HANDLE(hdesc) THEN
     ASSIGN oplistitems = oplistitems + replace(hkey:BUFFER-VALUE, ",", " ") + ' ; ' + replace(hdesc:BUFFER-VALUE, ",", " ") + ','.
     ELSE
     ASSIGN oplistitems = oplistitems + replace(hkey:BUFFER-VALUE, ",", " ") + ','.

     hQuery:GET-NEXT().

END.

/* added in the addition of ipc-defaultValue to get the specified value at the top of the combo list box.  If that input var is
   empty then just return the list as an empty var put a blank value at the top of the returned list again.  -JP  */
IF oplistitems <> '' THEN DO:
  IF ipc-defaultValue <> '' Then
    ASSIGN oplistitems = ipc-defaultValue + "," + RIGHT-TRIM(oplistitems,',').
  ELSE
    ASSIGN oplistitems = RIGHT-TRIM(oplistitems,',').
END.

/* JP added to set initial value of drop down list if there are more than 1 items in the list */
if oplistitems <> '' then
  assign opInitialVal = trim(entry(1,oplistitems,",")).

if num-entries(opListitems) = 1 then   
     assign opInitialVal = opListItems. 
/* scr8293 - Delete the query/buffer objects */
DELETE OBJECT hQuery NO-ERROR.
DELETE OBJECT hBuffer NO-ERROR.

RETURN.


I was wondering if anyone has been able to set an initial value in a combo box using the gettabledata.p program in the configurator.  Currently we are using it to populate a combo box of parts.  It brings all the parts into it in alphabetical order.  I'd like to set the initial value and then have all the rest of the values brought in after that.  We are on progress 702A.  Here is the current call to the .p program.


"Part","PartNum","PartDescription","","","Part.ProdCode matches 'FH_68'"


That routine will set the return value if there is only one value.  You can modify it so that it sets the value all the time.

Jim Kinneman
Encompass Solutions, Inc
I was able to modify it so that I always get a value even if there is more than one value returned.  But even by doing this they are still returned in alphabetical order.  I'd like to really call this in the following manner: 

"Part","PartNum","PartDescription","Z101","","","Part.ProdCode matches 'FH_68'"

So my returned list of parts in the combo box would start with Z101 and then all the others that match my prodcode are returned in the list after that.  Any idea if that's possible?
If I understand you have 

Part1, Part2, Part3, Part4, Part5 in a table.  You want to always have Part3 pre-filled in and on the top of the list.  If that is the case put Part3 on the list first before getting records from the table.  Then filter the table to exclude Part3 so that it doesn't show up twice.

The default pick could be passed over as a new parameter to avoid hard coding it in the .p file.

You could put the default pick in a table that you would look up and use it instead of hard coding it or passing it over.

Jim Kinneman
Encompass Solutions, Inc.