Dynamic Lookups

[zdenek.kabatek]

Hi,

has anybody tried dynamic lookups? I am trying on a very simple case to see how it works but I can't make it work.

I prepared a document where there is only ZIP Code, I prepared a database having three columns - ZIPCode, State, Town. I createad DFD where I have three fields - ZIPCode, State, Town. For State (and Town) I have an SQLEXEC like this

SQLEXEC(STRJOIN ("SELECT State FROM SourceList WHERE ZIPCode = '", FT_1_USER_FIELD, "'")). When I run a document having ZIPCode 18000 (in the database it maps to Prague and CZE) I am getting in Decipher Boston, MA.

What am I doing wrong?

Thanks for any hint or help.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[PatrickAucoin]

Hi,

For fields in the DFD you want to look up, have you assigned the field flags Auto-calculate and DynamicList ?  
Also check that you have added the dynamic list in the batch type settings. In addition to the database connection string, you have to specify the location of the list in the database.

------------------------------
Patrick Aucoin
Senior Product Consultant
Blue Prism
------------------------------

[zdenek.kabatek]

Hi, Patrick,

I have checked Auto-calculate and Dynamic List. I also created database connection string but I do not fully understand the relation of Dynamic list in the batch type settings to the DFD. I thought that this is connected to Validation List column in DFD and not to Formula column. See my settings in the images below.

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[PatrickAucoin]

Hi,

You are right, the lists defined in the batch type settings are for static lists. The list name column in the DFD binds the static list to that specific field. The entire list is imported to validate against the value generated by the OCR engine.

For dynamic lists, it looks like you only need to specify the import connection string. So, you should not need to specify lists in the batch settings or the DFD.

I have two other suggestions for you: 

In addition to AutoCalc and DynamicList flags, can you also try applying the Calculable flag as well?

Another idea: integrated variable support works for the user-defined field IDs, but I don't know if it works for external database queries. Try enclosing the column name variables in square brackets?

------------------------------
Patrick Aucoin
Senior Product Consultant
Blue Prism
------------------------------

[zdenek.kabatek]

Hi, Patrick,

thanks for you suggestion and spending time on this topic, however, it still does not work.

Calculable flag did not make any difference.

The other idea I don't quite understand what you mean. Can you write exactly what I should put in SQLEXEC?

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------

[PatrickAucoin]

Instead of:

SQLEXEC(STRJOIN ("SELECT State FROM SourceList WHERE ZIPCode = '", FT_1_USER_FIELD, "'"))

Try:

SQLEXEC(STRJOIN ("SELECT [State] FROM [SourceList] WHERE [ZIPCode] = '", FT_1_USER_FIELD, "'"))

I will try to get more information on formula syntax.

------------------------------
Patrick Aucoin
Senior Product Consultant
Blue Prism
------------------------------

[zdenek.kabatek]

Hi, Patrick,

I tried both and there was no difference. It would be really good to get information how it is supposed to work than trying to guess how it should be built.

Regards,

------------------------------
Zdeněk Kabátek
Head of Professional Services
NEOOPS
http://www.neoops.com/
Europe/Prague
------------------------------