4 Replies Latest reply: Jun 14, 2012 5:37 AM by Mirco Andreon RSS

    DynamicUpdateCommand fails with multiple tables

    Mirco Andreon

      Hi, I was using DynamicUpdateCommand this way:

      dim stmt
      stmt = "UPDATE * SET CHOOSE = 'S' WHERE TIPDOC='o' and NUMORD=123456 and NUMCAR='987654'
      set result = ActiveDocument.DynamicUpdateCommand(stmt)
       if result = false then
        MsgBox "Error with DynamicUpdateCommand. " & result.ErrorMessage
      end if
      

      It always worked when I had only one table (ORDINE), with some lookup tables.

      But when I added more tables it started to fail (result is false and unfortunately result.ErrorMessage is empty, so no clue is given).

      I attach my Table Viewer, with some explanation.

      I someone can help me, I would appreciate so much, because I can found very few docs about DynamicUpdateCommand in the net.

        • Re: DynamicUpdateCommand fails with multiple tables
          Jonathan Dienst

          Hi

           

          I suspect that the problem is related to the fact that TIPDOC, NUMCAR, NUMORD etc are now key fields (actually part of the Syn Key).

           

          I suggest that you make aliases for those fields that exist only in table ORDINE.

           

          Hope that helps

          Jonathan

            • Re: DynamicUpdateCommand fails with multiple tables
              Mirco Andreon

              The problem is that I need ORDINE, ORDINE_FO, VARIABILI to be linked together, because I need to filter on ORDINE based on selections on the other tables.

              Maybe should I build a single table (ORDINE) with everything inside, using SQL joins?

              Otherwise DynamicUpdateCommand will not work?

              Thanks!

                • Re: DynamicUpdateCommand fails with multiple tables
                  Jonathan Dienst

                  Hi

                   

                  I dont think the link is a problem, but I suspect the dynamic update does not work when the fields are key fields. That's why I suggest that you make aliases for these fields and apply the dynamic update using the aliases. You could possibly collapse everything into one table, but how well that works depends on your data structure. Creating the aliases does not affect your model structure.

                   

                  To create the aliases, modify your load script, something like this...

                   

                  ORDINE:

                  LOAD

                       ....

                       NUMORD,

                       NUMORD As duNUMORD,

                       RIGORD,

                       RIGORD As duRIGORD,

                       TIPDOC,

                       TIPDOC As duTIPDOC,

                       NUMCAR,

                       NUMCAR As duNUMCAR

                       ....

                  FROM ....

                   

                  Then modify the dynamic update to use the fields with the du.... prefix

                   

                  .... WHERE duTIPDOC = 'o' .... etc

                   

                  Hope that helps

                  Jonathan

                    • Re: DynamicUpdateCommand fails with multiple tables
                      Mirco Andreon

                      You're great!! Now it works, thank you so much.

                      Only a note: in the script fields are case-insensitive, but in DynamicUpdateCommand they are case-sensitive and they refer to the fieldnames loaded in-memory by QlikView, which become all upper-case (you can see it for instance in Table Viewer). So in DynamicUpdateCommand always use upper-case fields names, otherwise it will throw an error.