This topic is locked

unigue values for help please

8/1/2021 2:18:09 PM
PHPRunner General questions
muammer author

I'm using the "lookup wizard" for the data I'm going to enter in a field. But when I check "List page with search", the "unique values" option is disabled to open unique values.
I need to use unique values when the "List page with search" option is checked. I need help with this. I need step-by-step guidance or sample help. Many thanks to those who can help.

acpan 8/1/2021

Try this:

  1. First enabled "List Page" for the lookup table, (eg. table name: "my_lookup") refer to manual on Choose Pages

  2. To filter for unique values, you can do it at the SQL Editor Screen for the List page you enabled at step 1.
    Use SQL Query: SELECT DISTINCT my_unique_field FROM my_lookup;

  3. Then go to Lookup Table, select "List page with search" radio option as you did, and on the right, you can now select the List Page you created at step 1. There is no need to select "unique values" option, as the lookup values are unique due to step 2.

muammer author 8/1/2021

Dear acpan, thank you very much for your interest and suggestion.
but I have other fields in my table in my columns. As you said in the sql query, if I only do one field, you say that there is no problem.
When I want it to appear in the same table in other fields, it shows the repeated values.
Let me give an example for better understanding.
I have a table named products. In my table, product code, product category, product sub-category, product image, customers who can see the product, date added, etc. continues....
There is more than one product from the same product code, but for example, the customers who can see it are different or the product features are different. As you said, when I execute the sql command, repeated calls appear in the product code.
as follows
SELECT DISTINCT Urun_Kodu, Kumas_Bilgileri, Urun_Resimleri, Alt_Kategori, Etiket, Yetkili_Musteri FROM Urunler

img alt

acpan 8/2/2021

You can use simple SQL query statement to form unique rows, which is the basic purpose of SQL Query,
beside SELECT DISTINCT, there are many other ways, such as:

SELECT Urun_Kodu, Kumas_Bilgileri, ... FROM Urunler GROUP BY Urun_Kodu


SELECT CONCAT(Urun_Kodu, "-" ,Kumas_Bilgileri) as product_name_id, ....

The idea is to create a unique lookup list, before you use it at List page with search.

admin 8/2/2021

Yes, you can create a custom view in PHPRunner and use this custom view as a source of your lookup wizard. In that custom view SQL query you can use either DISTINCT or GROUP BY.

The main question you need to ask yourself is what you want to display in the rest of the fields. For instance, you have two records with FIN14300. In the resutls there only will be a single record like this but the question is what to display in Kumas_Biglieri field as you have two options there.

acpan 8/2/2021

Don't use concat, I like to use it for lookup, to show the name along side the ID when drop down, in this case it doesn't help to form unique values, just to be technically correct.

muammer author 8/2/2021

Thank you very much for all your help and guidance. I love phprunner and his team. Good luck to you. your suggestions helped. I hope that in the next phprunner versions, the most requested requests here will become practical and do great work with less code.