6 Replies Latest reply: Sep 16, 2016 2:54 AM by Marcus Sommer RSS

    Input Fields populated by existing Fields in a column

    Michele Ceresa

      Hi all,

       

      I want the data in "Index Feed" (Expression) column to populate the "Index" (Input Fields) column on the right.

       

      At the moment is manual and I am sure there's a way to write a Macro that does it for me.

       

      As you can see every year has its own "Index Feed".

      Capture.PNG

       

      Thanks in advance.

       

      Michele

        • Re: Input Fields populated by existing Fields in a column
          Tamil Nagaraj

          Hi Michele,

           

          Have a look at the attachment.

           

          Sub Test
           
           set Chart = ActiveDocument.GetSheetObject( "CH438" )
          
           for RowIter = 1 to Chart.GetRowCount-1
          
           set DateVal = Chart.GetCell(RowIter,0)
           set InputDateVal = Chart.GetCell(RowIter,5)
           set vFld = ActiveDocument.GetField("Index")
           
           vValueIndex = ActiveDocument.Evaluate("Concat(SNo, ',', SNo)")
           vValue = ActiveDocument.Evaluate("Concat(Date, ',', SNo)")
           
           vValueIndex = split(vValueIndex, ",")
           vValue = split(vValue, ",")
           
           for i = 0 to ubound(vValueIndex)
           
            If vValue(i) = DateVal.Text then
            vFld.SetInputFieldValue vValueIndex(i) - 1, InputDateVal.Text
            End If
          
           next 
           
           next
           
           Msgbox "Done!!"
           
           End Sub
          
          
          
          
            • Re: Input Fields populated by existing Fields in a column
              Tamil Nagaraj

              Hi Marcus Sommer,

               

              The above code is not working when we have a key field for comparison. I have attached a sample document that shows the problem. Any idea.?

               

              Thank you.

                • Re: Input Fields populated by existing Fields in a column
                  Marcus Sommer

                  If I remember correctly must an inputfield-value absolutely unique - this includes also the association from the inputfield (over key-fields) to other tables.

                   

                  In this case the inputfields are connected over the Date_Fund_VintageDate key-field to the MarketDate-table in which the values for Date_Fund_VintageDate aren't unique - they are multiple and so occur conflicts by assigning the values.

                   

                   

                  This isn't easy to solve - then you could of course make the key-values unique but this will quite probably lead to other problems within the datamodel and increase the complexity and efforts to enter and/or distribute the inputvalues correctly.

                   

                  I have always struggled with inputfields and use it today only in two quite old applications in a relative simple way. All approaches to use them in more complex scenarios failed (and nobody by us wanted a planning-tool which isn't excel ...) because they require too much efforts to create a working and stable application. If I had nowadays a new task in this kind I would use an extension like: SQL Writeback from QlikView extension object.

                   

                  - Marcus

              • Re: Input Fields populated by existing Fields in a column
                Michele Ceresa

                Hi marcus_sommer

                 

                This is the first solution Tamil posted and as you can see from the snapshot below we have the same problem but it works.

                 

                Am I understanding right or not?

                 

                Capture.PNG

                 

                 

                Thanks

                 

                Regards

                  • Re: Input Fields populated by existing Fields in a column
                    Marcus Sommer

                    In your example the inputfields are unique in relation to the keys within the datamodel and therefore it will work manually and per macro. But like above mentioned it's quite hard to develop more complex scenarios and you will experience some limitations of this feature.

                     

                    Therefore if you hadn't a really simple usecase I suggest to forget about inputfields and to switch to an extension-solution which read and write the data into a database and I don't know any tool which is based on inputfields which are used to handle user-inputs - they all use a database-connection. AFAIK each of these tools are commercial but to purchase one might be the most efficient way to solve your task.

                     

                    - Marcus