Update SQL table

Erin Trotter · October 10, 2024

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.

David Beagan · October 10, 2024

I did something like this recently using HTML forms and MODIFY. Let me see if I can work up and example in the coming days.

Edited October 10, 2024 by David Beagan

Erin Trotter · October 10, 2024

@David Beagan , I would really appreciate that. Thank you so much!

Patrick Huebgen · October 11, 2024

@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

Erin Trotter · October 11, 2024

@Patrick Huebgen, thank you. I will review these

David Beagan · October 11, 2024

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:

The comments are stored in a separate table:

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.

Erin Trotter · October 11, 2024

@David Beagan Wow!! That is amazing.. This looks a lot like what I'm trying to do

David Beagan · October 11, 2024

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.

David Beagan · October 11, 2024

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

Erin Trotter · October 11, 2024

So, at the end of this process, the comments file could be merged into the ggsales file and become a part of the record in the database?

David Beagan · October 11, 2024

In our situation, we just decided to keep the comments data separate because it wasn't practical to change the original data table(s) that the report was based on. But if you wanted to design it from the beginning as a single table, that could work too.

Erin Trotter · October 11, 2024

Thank you so much!

Erin Trotter · October 11, 2024

Thank you @Patrick Huebgen & @David Beagan . I'm sure with both of these examples, I can get to where I need to be. This is exactly what I was looking for. I really appreciate it

Erin Trotter · October 17, 2024

@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

David Beagan · October 19, 2024

You might consider simply having a hyperlink in the report that takes the user to a page where they do the update for a single row's data. It would be easier to develop and maintain. However, if the in-report update is such a compelling feature for your users that it is worth the extra effort here are my responses to the points you raised.

Would the DIGITS() functionbe simpler for converting current fy fields to alpha? You could even have DEFINEs in the Metadata that provides alpha versions of the fy fields.
You say that you have to convert the alphas back to numeric. What are you converting back? The variable's value in the update_test.fex ?

You would need a submit button for each form, however, I think you should be able to make it work with one form block.

When you click the submit button and something opens in the tiny box (an iframe), remember the iframe would be for normal operations when you would want a short comfirm or reject message to appear in the box for end user feedback. During development, you can use target=_blank to have it open in a new browser tab so you can see it better.

You might find this helpful:
https://www.w3schools.com/html/html_forms.asp

Erin Trotter · October 24, 2024

The way my current application works is how you suggested with a separate page to edit 1 line of data. This is too much for the users. They are looking for something where they can just tab through the fields and update them as they go along. I had never heard of the digits function. Thanks

David Beagan · October 25, 2024

I created another example that has a few inputs on each line.

I entered some new values for SEQ_NO 9 and clicked the Update. In the browser window you can see the values get passed to the fex which would then do the update.

Here is the code, using just a single <form>. When run it has one <form> for each line of output.

 SET TITLE=OFF,PAGE=NOLEAD,STYLE=WARM
 TABLE FILE GGSALES
 PRINT SEQ_NO PCD CATEGORY CITY 
 COMPUTE I/I4 = I + 1;	 NOPRINT
-* COMPUTE frame/A10 = 'frame'|EDIT(I); NOPRINT
 COMPUTE frame/A10 = '_blank'; NOPRINT
 UNITS
 COMPUTE Code/A999 = 
    '<form name=uomform action="/ibi_apps/run.bip" method="get" target="'| frame |'" 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_inputs_update.fex">'           
   |   '<input type="hidden" name="SEQ_NO"           value="' | TRIM('L', EDIT(SEQ_NO), 100, '0', 1, 'A10') || '">'                     
   |   '<div style="display:flex;flex-wrap:nowrap;top:0px;">'
   |   '<input name="UNITS"  value="'| TRIM('L', EDIT(UNITS), 100, '0', 1, 'A10') | '" size=10 maxlength=10 style="font-size:11px;height:11px;">' 
   ; AS ''
 BUDUNITS
 COMPUTE Code/A999 = 
      '<input name="BUDUNITS"  value="'| TRIM('L', EDIT(BUDUNITS), 100, '0', 1, 'A10') | '" size=10 maxlength=10 style="font-size:11px;height:11px;">' 
  ; AS ''
 DOLLARS
 COMPUTE Code/A999 = 
      '<input name="DOLLARS"  value="'| TRIM('L', EDIT(DOLLARS), 100, '0', 1, 'A10') | '" size=10 maxlength=10 style="font-size:11px;height:11px;">' 
  |   '<input type="submit"   value="Update" style="font-size:12px;height:18px;">'
  |   '<iframe name=frame'| frame |' frameborder=0 height=16 width=99 scrolling="no"></iframe>'
  |   '</div>'
  |'</form>'
  ; AS ''
 WHERE SEQ_NO LE 10
 END

Erin Trotter · October 31, 2024

This is perfect! I was trying to figure out how to have them all in one form while still having fields between them and this is exactly that! Thanks a bunch.

Solutions

Featured Topic

ibi^™ Mainframe

ibi^™ Analytics

ibi^™ Data Intelligence

Get help

Get involved

Company

Partners

Sign In

Update SQL table

Recommended Posts

Erin Trotter

David Beagan

Erin Trotter

Patrick Huebgen

Erin Trotter

David Beagan

Erin Trotter

David Beagan

David Beagan

Erin Trotter

David Beagan

Erin Trotter

Erin Trotter

Erin Trotter

David Beagan

Erin Trotter

David Beagan

Erin Trotter

Create an account or sign in to comment

Create an account

Sign in

Activity

Solutions

Featured Topic

ibi™ Mainframe

ibi™ Analytics

ibi™ Data Intelligence

Get help

Get involved

Company

Partners

Update SQL table

Recommended Posts

Create an account or sign in to comment

Create an account

Sign in

ibi^™ Mainframe

ibi^™ Analytics

ibi^™ Data Intelligence