Inputbox values inserted into Inputfield via Macro

[beckerv]

Dear all,

I am currently working on an application that requires entering comments.

I have tried different approaches regarding inputbox and inputfield but somehow the outcome is not quite what I expect.

APPROACH No 1 - Usage of Inputfield:

I first tried the usage of inputfield only (see script below, unique key is created, no problem with duplicate keys, tablebox using inputfield allows entries as expected).

Basisdata:

LOAD

        (Date, Company, Account, Costcentre) as Comment_Key,

        *

FROM Trialbalance.qvd

INPUTFIELD Comments;

Comments:

LOAD                 

                 (Date, Company, Account, Costcentre) as Comment_Key,

                  'n.a.' as Comment

RESIDENT Basisdata;

I can then enter comments into the created inputfield, but as this application is used on the server, I can only see user-specific comments.

But one needs to see ALL comments entered by EVERY user.

That´s why I want to store the comments into a separate file (excel, csv, qvd....), preferably via a button (macro action) including timestamp and UserID.

APPROACH No 2 - Usage of Inputbox and Macros:

I found this example in the community (thanks to Stephen Charles for posting it) and included it in my application (see attachment QlikViewExcel.zip).

http://community.qlik.com/message/40351#40351

It works as follows:

• Create an inputbox using a variable (in this case "vTextComments")
• Create a button and assign macro action to it ("execute macro")
• Use macro (again, thanks Stephen Charles, see attachment Macro ExcelAddComment.docx) to
  • store entered text from Inputbox into excelfile (including Username)
  • reload script (to display newly entered comment in tablebox)
  • clear inputbox

This approach also works fine, what I especially like is that I can define certain settings that must be fulfilled before the user can store the comment (e.g. a unique data record must be chosen, error message if comment is already available for data record etc.).

BUT the biggest problem I have is, that in order to display the entered comment to the user (in tablebox), the script must be reloaded.

As I am talking about millions of data records in this application, a reload simply does not make sense.

So my goal would be approach No 3.

APPROACH No 3 - Combination of inputbox and inputfield:

I have now defined (as in approach no 1) an inputfield AND am using the inputbox idea from approach no 2.

However this does not quite work because:

• when entering comments into the inputBOX (and pushing the macro button), these comments are "only" stored in the excel file, but I do not see them in my tablebox (unless I reload the script, what I do not want).
• when entering comments into the inputFIELD, I immediately see the comment in the tablebox, but when pushing the macro button, an empty comment field is stored in the excel file (as no text is entered in the inputBOX)

What I would need is the following:

• enter comment into inputBOX
• macro that
  • stores comment from inputBOX into excelfile (already available)
  • AND stores comment from inputBOX into corresponding inputFIELD

This way I have the comment displayed immediately as well as saved in an excelfile that makes the comments available for all users (I will then reload the application on a regular basis e.g. once a day).

I have to admit that I am not a big VBgeek, so any support is highly appreciated!

I would be grateful for your help/experience regarding this issue as I did not find an example like this in the community so far.

Thanks and best regards,

Viktoria

P.S.: In case approach no 3 can be executed, I would of course create a document, providing a short .qvw example

[#2]

Hi,

I have done a similar thing, and been struggling with this also. Have you found a solution already?

Here are my thoughts about this. If your comment field is an inputfield, you could store the comment from the variable to your field via a hidden table box, using this in your macro:

     set obj = ActiveDocument.GetSheetObject("TB01")
     obj.SetInputFieldCell 0,0,vTextComments

This stores the comment in the first cell, so this works if only one comment is selected, otherwise you need to specify the row using the parameters.

But, if your comment is an inputfield, then if you load the comments into the application from the excel file, do you manage to get them displayed to the user or are they still seeing only what they personally inserted? Maybe you can reset inputfields on load, or use a combination of two fields (the loaded comments as an ordinary field, and the inserted comments as an inputfield) to display comments to the user.

A different way could be the Dynamic Update action. Are you familiar with this, and could it be used in your case? If not, take a look at it. I have not tested it via access point myself, but it seems to be very useful!