SQLEXEC not working in Table fields

[KalashSharma]

Hi All,

I want to populate a table column cells with a Dynamic List (please note this is the table field). I have following set in the formula. When I verify using this it doesn't run.

The same SQLEXEC formula runs fine when I do it in a normal non-tabular field.
Is this feature not applicable for tabular fields? Is there something special that needs to be done for this?

[BenLyons]

Hi Kalash,

Have you seen the video for dynamic lists on our help page?

Thanks

[KalashSharma]

Ben,

I have seen this video. it is helpful but this only talks about how to make it work on normal fields. Not the tabular fields. It doesn't seem to work on tables.

[KalashSharma]

I have tried both of these below mentioned SQLEXEC formulae. They work fine on the fields (after replacing tableField1 with normal field) but NOT on the tabular fields. If this is the limitation of Decipher please let me know I will think of the alternative. 

[BenLyons · Accepted solution]

Hi Kalash,

Ok, it's a tricky one using the LIKE and STRUPPER. But I've got it working as follows:

SQLEXEC(STRJOIN("SELECT PurchaseOrderNumber FROM PurchaseOrders WHERE Vendor LIKE '%", STRUPPER(Vendor_Name:#), "%'"))

To get the STRUPPER working I had to add the Auto-Calculate flag, which isn't usually needed for a dynamic list.

Have you tried adding that flag?

Thanks

Ben

[KalashSharma]

Thanks Ben. I am now able to see the query result on the table. But it only populates the top/first item from SQL results. If the query fetches e.g. 5 results, the clicking on the down arrow to expand the dropdown doesn't display the remaining results from the query. If we do the same for a normal field, it would display all the results fetched by the SQLEXEC.

My objective is to give users the ability to select one item from the multiple fetched results within the table row (there can be multiple results for query e.g. the query can bring results from the database with slight variations in the name e.g. Tesco Ltd, Tesco Limited, Tesco).

[BenLyons]

Hi Kalash,

Because of the auto-calculate, Decipher will now try to calculate the value, so it will auto-populate.

That would indicate the SQL function isn't working as expected, try temporarily removing the "UPPER" function. Also, are you selecting the Batch Type with the connection string set up?

Is the field going to be assigned to a region in the document or just selected from the dropdown by the user?

Thanks

Ben

[KalashSharma]

Hi Ben,

The same SQL is working as intended when associated to a normal field. Still I tried removing UPPER but it behaves the same way. It seems the dropdowns will never expand or get populated with multiple entries when present in the table section. It could be a bug or a restriction e.g. the translated SQL code (within SQLEXEC routine) selects only the topmost record whenever SQLEXEC is fired from the tabular fields. 

The related DFD field isn't going to be assigned to a region. The user are supposed to select it from the dropdown. 

[BenLyons]

Hi Kalash,

It's working fine in my set up, so worth looking into your access/configuration. (see snips below)

If a user is going to be selecting each one, how are you planning on having any as straight through processing?






Thanks

Ben

[KalashSharma]

Ben,

I am using Decipher 1.2. Happy to see it working on your setup. Are you on the 2.1?  

For 'straight-through' processing, I will add a field mapping between 'Dynamic List Input' and 'Dynamic List Test' so hopefully with time it'll learn. Even if it doesn't, Decipher will hold such documents for verification and users will select manually. Given that such documents are low in numbers and the downstream process is quite time-consuming for humans, we will still save time.

If this behaviour is Decipher version related then I will think of an alternative arrangement.