topic Re: SQLEXEC not working in Table fields in Product Forum

SQLEXEC not working in Table fields

denzain — Fri, 18 Feb 2022 11:33:55 GMT

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?

Re: SQLEXEC not working in Table fields

Ben.Lyons1 — Fri, 18 Feb 2022 11:52:56 GMT

Hi Kalash,

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

Thanks

Re: SQLEXEC not working in Table fields

denzain — Fri, 18 Feb 2022 12:26:48 GMT

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.

Re: SQLEXEC not working in Table fields

denzain — Fri, 18 Feb 2022 12:47:53 GMT

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.

Re: SQLEXEC not working in Table fields

Ben.Lyons1 — Fri, 18 Feb 2022 13:09:05 GMT

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

Re: SQLEXEC not working in Table fields

denzain — Fri, 18 Feb 2022 13:42:52 GMT

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

Re: SQLEXEC not working in Table fields

Ben.Lyons1 — Mon, 21 Feb 2022 10:39:54 GMT

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

Re: SQLEXEC not working in Table fields

denzain — Mon, 21 Feb 2022 13:21:20 GMT

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.

Re: SQLEXEC not working in Table fields

Ben.Lyons1 — Mon, 21 Feb 2022 15:09:08 GMT

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

Re: SQLEXEC not working in Table fields

denzain — Mon, 21 Feb 2022 16:44:59 GMT

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.

Re: SQLEXEC not working in Table fields

Ben.Lyons1 — Tue, 22 Feb 2022 08:01:53 GMT

For this test I was using 1.2, so you should be okay.

If you want to have Decipher learn the selected dropdown you can set it as a virtual field, which involves selecting the flags Assignable, Virtual an Unique. There were some improvements made to this feature in v2.1, so it might be worth looking into it.

This will store the selected value in the training data and link it to the layout of the document. If you don't want to link the selection to the layout, this wouldn't be the right option.

Thanks

Ben