Contact us
Jump to content

Update SQL table

Erin Trotter

By Erin Trotter
in WebFOCUS

 Share
Go to solution Solved by David Beagan ,

Recommended Posts

Erin Trotter Enthusiast

Erin Trotter

Posted
Posted

Hello all,

    I am trying to find a better solution for an application that I built in WebFocus.  It allows the users to update a record in a sql database using a combination of fex's and stored procedures.  However, it only allows the user to update 1 line at a time.  I'm looking for a way to have my entire data set load in a grid-view with 2 of the fields in edit modes in each line allowing the user to edit as many lines as possible and then click submit at the end of it all to write all of the updates back to the database.  I have not been able to find an example of something like this, although I remember seeing something years ago that I can no longer find.  I believe the solution was using MODIFY.  I looked at the manual, but although it explains the commands, what I'm trying to do is more custom and it doesn't really help me with that grid-view.  I put in a ticket to support.  They directed me to a row delete example, but it does not contain a gridview and it is only for deleting all records it finds with a match in a database.  We are not licensed for MAINTAIN, so I know that is not an option for us.

Link to comment
Share on other sites
Patrick Huebgen Community Regular

Patrick Huebgen

Posted
Posted

@Erin Trotter I created a video on MODIFY a while back - sorry one of the few German Videos - but autogenerated subtitles might help even if some of the translations are "funny"

I did something with multicell update as well in the past but seems to have lost it.

It was something that created a dynamic HTML form with many fields.

I create my form via repeat and !IBI.AMP.XXXX; technique and create dynamic MODIFY based on this form - attached two videos to start with MODIFY and IBI.AMP

Here's the modify

 

Here's the IBI.AMP video

 

  • Like 2
Link to comment
Share on other sites
  • Solution
David Beagan Community Regular

David Beagan

Posted
  • Solution
Posted

In our situation we had a report and that user's wanted to have comments that they could add or change on each line of the report. I had made an example using a report based on the ggsales file.   There is one row on the report for each distinct combination of CATEGORY and STCD with a potential comment about each of these. I run the report, change a comment and click the Update button for that row and is shows "Updated" like this:

image.png.5fffc4229f3002a62ad749979352a4ee.png

The comments are stored in a separate table:

image.png.8c041515659c6fb4de0e8c3200a06cea.png 

FILENAME=GG_REPORT_COMMENTS, SUFFIX=FOC,
 DATASET=baseapp/gg_report_comments.foc, $
  SEGMENT=SEG01, SEGTYPE=S2, $
    FIELDNAME=CATEGORY, ALIAS=E01, USAGE=A11, FIELDTYPE=I, $
    FIELDNAME=STCD,     ALIAS=E02, USAGE=A05, FIELDTYPE=I, $
    FIELDNAME=COMMENT,  ALIAS=E03, USAGE=A50V,             $
    FIELDNAME=WFUSER,   ALIAS=E04, USAGE=A16,              $
    FIELDNAME=WFDTTIME, ALIAS=E05, USAGE=HYYMDS,           $

Yours would be in a sql data table. 

  • Like 4
Link to comment
Share on other sites
David Beagan Community Regular

David Beagan

Posted
Posted

The code to present the report with the comments input: 

 SET PAGE=NOLEAD,LINES=998,STYLE=WARM
 
 MATCH FILE GGSALES
 SUM REGION ST CITY STCD UNITS
 BY CATEGORY BY STCD
 RUN 
 FILE GG_REPORT_COMMENTS 
 SUM COMMENT
 BY CATEGORY BY STCD
 AFTER MATCH HOLD AS GGDATA OLD-OR-NEW 
 END
 
 TABLE FILE GGDATA
     SUM CATEGORY	
         REGION	
         ST	
         CITY	
         STCD	
         UNITS 
 COMPUTE I/I4 = I + 1;	 NOPRINT
 COMPUTE N/A4 = EDIT(I); NOPRINT
 COMPUTE Code/A999 = 
    '<form name=uomform action="/ibi_apps/run.bip" method="get" target="frame'| N |'" style="height:10px;top:0px;">'
   |   '<input type="hidden" name="BIP_REQUEST_TYPE" value="BIP_RUN">'                                       
   |   '<input type="hidden" name="BIP_folder"       value="IBFS:/WFC/Repository/Training_WF8/examples/report_comments">' 
   |   '<input type="hidden" name="BIP_item"         value="/gg_report_comments_update.fex">'           
   |   '<input type="hidden" name="CATEGORY"         value="' | CATEGORY || '">'                     
   |   '<input type="hidden" name="STCD"             value="' | STCD || '">'                     
   |   '<div style="display:flex;flex-wrap:nowrap;top:0px;">'
   |   '<input name="COMMENT"  value="'| COMMENT | '" size=50 maxlength=50 style="font-size:11px;height:11px;">' 
   |   '<input type="submit"   value="Update" style="font-size:12px;height:18px;">'
   |   '<iframe name=frame'| N |' frameborder=0 height=18 width=99 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS 'Comments'
  BY CATEGORY NOPRINT
  BY STCD	  NOPRINT
 END

Note that the BIP_folder and BIP_item would reference your code for the fex that does the data update.

Link to comment
Share on other sites
David Beagan Community Regular

David Beagan

Posted
Posted

And the code for the gg_report_comments_update.fex: 

-DEFAULTH &CATEGORY = 'test';   
-DEFAULTH &STCD     = 'test';  
-DEFAULTH &COMMENT  = 'Lorem';   
-SET      &WFUSER   = '&IBIMR_user.EVAL';
-DEFAULTH &WFDTTIME = '&DATEH14.EVAL' ;
-SET      &COMMENT  = LJUST(25,&COMMENT,'A25');

 MODIFY FILE gg_report_comments
 MATCH CATEGORY STCD 
 ON MATCH UPDATE COMMENT WFUSER WFDTTIME
 ON NOMATCH INCLUDE
 DATA
 '&CATEGORY','&STCD','&COMMENT','&WFUSER','&WFDTTIME',$
 END
-RUN
 
-SET &RESULT = IF &INPUT EQ 1 THEN 'Added' ELSE IF &CHNGD EQ 1 THEN 'Updated' ELSE 'Rejected';
-HTMLFORM BEGIN
<span style="position:absolute;top:0;left:4;">!IBI.AMP.RESULT;</div>
-HTMLFORM END

 

Link to comment
Share on other sites
Erin Trotter Enthusiast

Erin Trotter

Posted
  • Author
Posted

@David Beagan  @Patrick Huebgen I've been playing around with this concept a bit and I'm running into some trouble.  The first issue is that the fields that need to accept the input need to both display what the current input is and allow the user to edit it and then update it.  The fields are numeric... so I found that having them display in the box is difficult without converting it to text and then converting it back. The second issue is that I need editable fields to be mingled between un-editable fields and I can get this to show up, but I'm not sure if each field needs to have it's own form block like you have there.  Third issue I'm running into is since the fields are intermingled, the submit button is not on the same form with all of the fields...  and when I click it, it is opening a tiny box within one of the editable field forms and I can't tell what the tiny box is or why it is opening over there.   I'm sure a lot of this that I'm putting here is not supposed to be here.    I'm not very familiar with HTML in WebFocus reports.

 

Below is an example of what I have here: 

TABLE FILE BUDGETPROCESS/BUDGET_VW_PS_REVENUE
SUM BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ACTUAL_PREVIOUS_FY3 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ACTUAL_PREVIOUS_FY2 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ACTUAL_PREVIOUS_FY1 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.CURRENT_FY_ACTUAL 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.CURRENT_FY_ANNUALIZED 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.DEPT_EST_CURRENT_FY 
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.AVERAGE_OF_AVERAGES
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.LEAST_SQ_FORECAST_NEXT_FY
 COMPUTE A/I4 = A + 1;	 NOPRINT
 COMPUTE B/A4 = EDIT(A); NOPRINT
COMPUTE AUDITOR_EST_CURRENT_FY1/A1000V =
    '<form name=uomform >'
   |   '<input type="hidden" name="ORIG_COMMENTS"  value "' |AUDITOR_COMMENTS| '">'
   |   '<div style="display:flex;flex-wrap:wrap;top:0px;">'
   |'<input name="AUDITOR_EST_CURRENT_FY"  value="'|  TRIM('L', EDIT(AUDITOR_EST_CURRENT_FY), 100, '0', 1, 'A100') | '"  size=15 maxlength=20 style="font-size:14px;height:11px;">' 
   |   '<iframe name=frame'| B |' frameborder=0 height=18 width=20 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS 'Auditor Est Current FY'
       COMPUTE C/I4 = C + 1;	 NOPRINT
 COMPUTE D/A4 = EDIT(C); NOPRINT
COMPUTE AUDITOR_EST_NEXT_FY1/A1000V =
    '<form name=uomform1 action="/ibi_apps/run.bip" method="get" target="frame'| D |'" style="height:10px;top:0px;">'
   |   '<input type="hidden" name="BIP_REQUEST_TYPE" value="BIP_RUN">'                                       
   |   '<input type="hidden" name="BIP_folder"       value="IBFS:/WFC/Repository/update_example">' 
   |   '<input type="hidden" name="BIP_item"         value="/update_test.fex">'              
   |   '<input type="hidden" name="ID"         value="' | ID || '">'                
   |   '<div style="display:flex;flex-wrap:wrap;top:0px;">'
   |   '<input name="AUDITOR_EST_NEXT_FY1"  value="'|  TRIM('L', EDIT(AUDITOR_EST_NEXT_FY), 100, '0', 1, 'A100') | '"  size=15 maxlength=20 style="font-size:14px;height:11px;">' 
   |   '<iframe name=frame'| D |' frameborder=0 height=18 width=20 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS 'Auditor Est Next FY'
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.DEPT_EST_NEXT_FY
    COMPUTE E/I4 = E + 1;	 NOPRINT
 COMPUTE F/A4 = EDIT(E); NOPRINT
COMPUTE AUDITOR_COMMENTS1/A1000V =
    '<form name=uomform2 action="/ibi_apps/run.bip" method="get" target="frame'| F |'" style="height:10px;top:0px;">'
   |   '<input type="hidden" name="BIP_REQUEST_TYPE" value="BIP_RUN">'                                       
   |   '<input type="hidden" name="BIP_folder"       value="IBFS:/WFC/Repository/update_example">' 
   |   '<input type="hidden" name="BIP_item"         value="/update_test.fex">'                
   |   '<input type="hidden" name="ID"         value="' | ID || '">'                
   |   '<div style="display:flex;flex-wrap:wrap;top:0px;">'
   |   '<input name="AUDITOR_COMMENTS1"  value="'| ''  | '"  size=15 maxlength=20 style="font-size:14px;height:11px;">' 
   |   '<iframe name=frame'| F |' frameborder=0 height=18 width=20 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS 'Auditor Comments'
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.DEPT_COMMENTS
BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ID
COMPUTE I/I4 = I + 1;	 NOPRINT
COMPUTE J/A4 = EDIT(I); NOPRINT
COMPUTE SUBMITDATA/A1000V =
    '<form name=uomform3 action="/ibi_apps/run.bip" method="get" target="frame'| J |'" style="height:10px;top:0px;">'
   |   '<input type="hidden" name="BIP_REQUEST_TYPE" value="BIP_RUN">'                                       
   |   '<input type="hidden" name="BIP_folder"       value="IBFS:/WFC/Repository/update_example">' 
   |   '<input type="hidden" name="BIP_item"         value="/update_test.fex">'                
   |   '<input type="submit" value="Update"style="font-size:12px;height:18px;">'               
   |   '<iframe name=frame'| J |' frameborder=0 height=18 width=20 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS ''
COMPUTE G/I4 = G + 1;	 NOPRINT
COMPUTE H/A4 = EDIT(G); NOPRINT
COMPUTE UPDATESTATUS/A1000V =
    '<form name=uomform4 action="/ibi_apps/run.bip" method="get" target="frame'| H |'" style="height:10px;top:0px;">'
   |   '<input type="hidden" name="BIP_REQUEST_TYPE" value="BIP_RUN">'                                       
   |   '<input type="hidden" name="BIP_folder"       value="IBFS:/WFC/Repository/update_example">' 
   |   '<input type="hidden" name="BIP_item"         value="/update_test.fex">'                
   |   '<input type="hidden" name="ID"         value="' | ID || '">'                
   |   '<div style="display:flex;flex-wrap:wrap;top:0px;">'
   |   '<input name="UPDATESTATUS"  value="'| ''  | '" disabled size=15 maxlength=20 style="font-size:14px;height:11px;">' 
   |   '<iframe name=frame'| H |' frameborder=0 height=18 width=20 scrolling="no"></iframe>'
   |   '</div>'
   |'</form>'; AS ''
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.PS_BUS_UNIT
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.FUND_CODE
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.DEPARTMENT
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.SUB_DIVISION
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ACCOUNT
BY BUDGET_VW_PS_REVENUE.BUDGET_VW_PS_REVENUE.ACCT_DESCR

 

Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share
Go to topic listing
×
×
  • Create New...
© 2024 Cloud Software Group, Inc. All rights reserved.