[mohamad]

Dear Experts,
I connected external database (Mysql) to PM successfully. (Encode is utf8-UTF-8 Unicode)
I am going to use the mentioned database in order to get the products' names in my daynaform.
I used dropdown for my goal, I did the following steps:
1) I selected my special DB connection
2)in SQL part, I used this code to get the names of products: SELECT products_name FROM products
The aforementioned code works properly in phpmyadmin, but PM can not appear the list of products' names in my dropdown! It's worth mentioning that, in the dropdown some rows are added, but the names can not be appeared. (As seen in the attached image)
I would be appreciated if you could help me to solve my problem
Kind Regards

[MackieeE]

This is because of the SQL supplied. HTML Dropdowns are a key/value pair:

Code: Select all

<option value="[key]">[value]</option>

So currently your SQL has only one column, which fills the key but no value - so an empty label.
https://wiki.processmaker.com/3.0/Dropd ... Properties

Rework the SQL to fill both, For E.g.

Code: Select all

SELECT products_sku AS field_key, products_name AS field_label FROM products

[mohamad]

Thanks for your kind reply, another problem is that all of names are not appeared for me.
For example, in the external database's table, I have 10 names, but PM just show 4 names!!!
Do you know why is this like?!
And another question is that, how can I determine field_key? ( Should field_key be in database's table?)
Would you please help me to solve that?
Kind Regards

[amosbatto]

Mohamad,
The first field returned by the SQL query is the option's value and the second field is the option's label. The name of the fields doesn't matter.
See:
https://wiki.processmaker.com/3.0/DynaF ... Two_Values

[mohamad]

Dear Amos, thanks a lot,
What about the first part of my question?
For example, in external database(MySQL), there are 10 fields for the column, but in the dropdown of dynaform, PM shows just 7 of them!
I used this code:
SELECT DISTINCT product_name, product_name FROM products

How can I get all of product from external database, and if I want to prevent the appearance of repetitive names, what should I do?
I would be appreciated if you could support me to tackle my problem.
Looking forward to your comments
Kind Regards

[amosbatto]

What about the first part of my question?
For example, in external database(MySQL), there are 10 fields for the column, but in the dropdown of dynaform, PM shows just 7 of them!
I used this code:
SELECT DISTINCT product_name, product_name FROM products

How can I get all of product from external database, and if I want to prevent the appearance of repetitive names, what should I do?

Your query only contains 2 fields. Do you mean that there are 10 records (i.e., rows) in the table, but ProcessMaker is only returning 7 records?

DISTINCT will eliminate duplicates. Try your query in PhpMyAdmin or in MySQL and verify that it returns 10 records. Probably your SQL is wrong.

[mohamad]

Thank you very much, my problem has been solved.

[HeshanKaru1994]

Hi,
If we are using a postgres external database do we need APIs to retrieve or add data

[amosbatto]

Hi,
If we are using a postgres external database do we need APIs to retrieve or add data

No. See: https://wiki.processmaker.com/3.2/Datab ... PostgreSQL]instructions