Input Fields populated by existing Fields in a column

[#1]

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".

Thanks in advance.

Michele

[tamilarasu]

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

[tamilarasu]

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.

[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

[#5]

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?

Thanks

Regards

[tamilarasu]

You are right. I too struggled a month back while implementing the input field using macro, Finally I decided not to implement the same again (using macro) as I don't want to complicate my work anymore. I saw this discussion and tried to help Michele. But it's not working for the second sample. Anyways, Thanks for your nice explanation.

[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