I am being tasked with creating a report - based off of a SQL database, that contains static columns and then 1 comment column (that the user needs to be able to update) from within the report. This update would need to then be written into our underlying SQL table. We do not have a license for MAINTAIN, so that's not an option.

This message has been edited. Last edited by: ELockett,

One alternative is to use MODIFY language but I'm not sure how you'd prompt for them to type stuff in. You could write this into a file and run a batch job in MODIFY I guess.

Alternatively, (and I haven't done this before and I'm thinking high level) you could create a stored procedure in your RDBMS that accepts a parameter and updates a row in the table. Run a standard report to let users select the row they want to update with a drill down. Then prompt them for the text they have to type and pass the value of the &VARIABLE in the prompt to the stored procedure to update based on the key.

John Johnson at Lender Processing Services gave a great presentation at Summit 2012 on this topic.

The presentation was called 'Database Updates Made Easy in WebFOCUS'.

I was able to easily reproduce what he shows in the .pptx and then adapted it to my requirements.

In a nutshell the presentation showed how to make your reports interactive and allow real time database updates.

Perhaps someone at your local IB office can forward the .pptx to you?

Yes, I've been trying to find info on the MODIFY language... haven't found the information yet in the technical library.

BabakNYC,
The idea about the drill down is interesting... I never thought about looking at it like that.

David,
Thanks for the tip. I will see if someone can forward me the .pptx. That sounds interesting and useful for future development also.

MODIFY syntax isn't fully documented anymore but the language still supports it. If you have a very simple use case, I'd say this'll do the job. But if you're going to do hard core transaction processing, I'd go the MAINTAIN way because that's what's fully supported and documented. Otherwise, use Stored Procs.

Open this Doc and search for MODIFY FILE in it. You'll see examples of it. http://documentation.informati...p77/mf_develop77.pdf

the basic syntax is
MODIFY FILE FILENAME
MATCH key field
ON MATCH UPDATE
ON NOMATCH REJECT
DATA
END

You'll have to define where the data is coming from. In your case most likely it's a FIXFORM FROM HOLD. It's been a very long time since I wrote MODIFY code so I can't really help you with it much, but the examples they have should give you a basic understanding of the syntax for a very simplistic update example.

I was able to solve this by creating a stored procedure to update my field and creating a report based on that with nothing but a Define field confirmation message. I created a html page with an input box for the user to input the comment and when they click the link on the main report, it brings up the HTML. From there, the user enters the comment and clicks submit (which is actually running the stored procedure report) and then the report runs a confirmation in the same window. I also added a refresh to just the panel of the main report so that the user can see the updated comments field.