Filter Values from Validation Lists

[dsa206]

Hi,

I have added 3 validation lists in my DFD - SupplierList, CompanyList, and Account List

What I wanted to do is to show only Account Numbers based from the value of the Supplier and Company.
(e.g If I choose 13410 supplier and AU101 company, the dropdown list for Account number should only show 466219 and 011501.)

How do I do this?

[dsa206]

I'm using ODBC connection type. 

What should I put in the Filter(WHERE clause) in the AccountList? 

[dsa206]

SUPPLIER = [NobleDW].[export].[BI_PAYMENT_ADDRESS].SUPPLIER and COMPANY = [NobleDW].[export].[BI_PAYMENT_ADDRESS].COMPANY

Tried the SQL above but it seems it's not working. 

[dsa206]

The formula in the DFD seems to be the problem. I have set the flag field to IdErpList and DynamicList. 

[lookman]

Hi Dsa206.

I believe what you want to do is create a single validation list for the supplier and use that list in the validation list cell associated with the supplier field in your DFD.

Then use SQLEXEC and STRJOIN commands to create the SELECT statement you want. You'd put the specific formula in the formula cell for the other fields.

The idea is that once you have the value of the supplier from the validation list, execute a SQL command (SQLEXEC) using a SELECT statement that you will build using the STRJOIN command and the DFD ID name that only return the values based upon the supplier number result set.

In the formula cell for Company_Number, you could using something like SQLEXEC (STRJOIN ("SELECT Company FROM payment_address WHERE Supplier = '", Supplier_Number, "'")).

Decipher will first execute the STRJOIN command to build the query and then use SQLEXEC to execute it against the result set from the validation list.

Give it a try and let us know how it goes.

jack

[dsa206]

Hi Jack,

I've been using the SQLEXEC and STRJOIN commands as well and found some samples here Import settings (blueprism.com). Also, I have updated the Flags to AutoCalculate and DynamicList.

I used the formula you gave, updated the column ID names, so here's the updated SQL:

SQLEXEC (STRJOIN ("SELECT COMPANY FROM BI_PAYMENT_ADDRESS WHERE SUPPLIER = '", Supplier_No, "'"))

However, it is always showing one value for the Company. In the sample picture, it's always NO112. The dropdown list doesn't even show any options to choose from.

For the Account Number field, i'm using this formula but it's not showing anything from the dropdown list.

SQLEXEC (STRJOIN ("SELECT ACCOUNT FROM BI_PAYMENT_ADDRESS WHERE COMPANY= '", Company_No, "' AND SUPPLIER = '", Supplier_No, "'" ))

Did I miss anything? I really appreciate your help. 

[lookman]

Hi dsa206.

Perhaps I'm misunderstanding. The idea behind the validation list is that Decipher reads the value from the PDF and does a look up to confirm it exists. If it does, you are good to go. However, if the value does not exist, the field will be flagged.

Do you want the values to be in a list to choose or do you want to validate the values against the validation list?

So is Decipher finding the value NO112 on the PDF? There should be no reason for Decipher to pull this value out of midair so it must be coming from somewhere?

[dsa206]

The idea behind the validation list is that Decipher reads the value from the PDF and does a look up to confirm it exists. -> Some of the fields may not be in the PDF file, like Suppplier Number for example(some Invoices has Supplier number, others dont), that's why I did NOT choose the Assignable field, but it's a Required field.

Do you want the values to be in a list? - > Yes, I want it to be in a DynamicList, whereas the list changes depending on the SQL. Isn't that what DynamicList is for?

Do you want to validate the values against the validation list? -> Yes.

So is Decipher finding the value NO112 on the PDF? There should be no reason for Decipher to pull this value out of midair so it must be coming from somewhere? -> The NO112 is not in the PDF. It's coming from the validation list but it's not showing all possible value based from the SQL.

Basically, I want to limit the list/options for a field, based from the value of other fields.

Let me know if I'm confusing you. 😄

[lookman]

Hi dsa206.

In the formula cell for the field you flagged with DynamicList, use SQLEXEC and STRJOIN to include the ID field that you want to use as the filter in the WHERE clause.

jack