Select distinct in `lookup field`

The recommended method of customizing your AppGini-generated application is through hooks. But sometimes you might need to add functionality not accessible through hooks. You can discuss this here.
Post Reply
bescott53

Select distinct in `lookup field`

Post by bescott53 » 2018-04-23 13:10

Hi everyone, I am trying to select distinct values from a table. I updated lookup field in appgini, amended the advanced option to SELECT DISTINCT but still get a lot of the same name coming through. any ideas how to solve this?

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Select distinct in `lookup field`

Post by pbottcher » 2018-04-23 21:16

Hi bescott,

can you poste your query. It should work, but watch out, as AppGini needs the ID to reference the lookup value this might cause issues, unless this does not count for your app.
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

bescott53

Re: Select distinct in `lookup field`

Post by bescott53 » 2018-04-25 20:56

Sure, here is the query,

SELECT DISTINCT `VendorMultiplier`.`RefNum`, `VendorMultiplier`.`Vendor` FROM `VendorMultiplier` ORDER BY 2

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Select distinct in `lookup field`

Post by pbottcher » 2018-04-26 21:08

Hi bescott,
not sure what you try to acvheive.
I assume you have something like

RefNum Vendor
1 a
2 a
3 b

and would like to see some output like

1 a
3 b

correct?

In this case you can not use the DISTINCT query, as the Refnum are all different. I would suggest to use

Code: Select all

SELECT min(RefNum) , Vendor
FROM   VendorMultiplier
group by Vendor
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

bescott53

Re: Select distinct in `lookup field`

Post by bescott53 » 2018-04-27 14:34

pböttcher, thank you fir your help, yes this worked a treat. appreciate it!

snawaz
Posts: 18
Joined: 2019-09-14 17:12

Re: Select distinct in `lookup field`

Post by snawaz » 2022-11-17 08:56

Dear pböttcher,

I appreciate your support. The code you shared below works well, but there is still one minor issue: the search function does not work when we use this code.

Once we get the distinct values, and if it is an extensive list, we want to look for a specific value. How do we find that value if the search function does not work?
Attachments
lookup.png
A snapshot of a lookup field has the search issue
lookup.png (7.77 KiB) Viewed 1275 times

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Select distinct in `lookup field`

Post by pbottcher » 2022-11-17 10:37

Hi snawaz,

can you post the query you are using in order to see what might be the issue
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

snawaz
Posts: 18
Joined: 2019-09-14 17:12

Re: Select distinct in `lookup field`

Post by snawaz » 2022-11-18 00:05

Hello pböttcher,

Below is my query. I have tried two more queries given below, and all are working fine, but I can not perform the search as already shared in the snapshot above.

SELECT `NFTS_WORK_TYPE_FAS`.`id`,`NFTS_WORK_TYPE_FAS`.`stc_po`
FROM `NFTS_WORK_TYPE_FAS` GROUP BY `NFTS_WORK_TYPE_FAS`.`STC_PO`

I have tried two more queries which gave me the same result.

1. SELECT COUNT(*), `NFTS_WORK_TYPE_FAS`.`STC_PO`
FROM `NFTS_WORK_TYPE_FAS` GROUP BY `NFTS_WORK_TYPE_FAS`.`STC_PO`;


2. SELECT MIN(`NFTS_WORK_TYPE_FAS`.`STC_PO`), `NFTS_WORK_TYPE_FAS`.`STC_PO`
FROM `NFTS_WORK_TYPE_FAS` GROUP BY `NFTS_WORK_TYPE_FAS`.`STC_PO`;

`NFTS_WORK_TYPE_FAS`.`STC_PO` This field has duplicate values, and I want to show the distinct values while filling out the form. I have achieved this by using the above queries but the problem is I can not search for the required value in the dropdown list.

Regards,

pbottcher
AppGini Super Hero
AppGini Super Hero
Posts: 1635
Joined: 2018-04-01 10:12

Re: Select distinct in `lookup field`

Post by pbottcher » 2022-11-18 11:54

Hi,

you may try to use

Code: Select all

SELECT `NFTS_WORK_TYPE_FAS`.`id`,`NFTS_WORK_TYPE_FAS`.`stc_po` from 
(SELECT `NFTS_WORK_TYPE_FAS`.`id`,`NFTS_WORK_TYPE_FAS`.`stc_po`
FROM `NFTS_WORK_TYPE_FAS` GROUP BY `NFTS_WORK_TYPE_FAS`.`STC_PO`) NFTS_WORK_TYPE_FAS
instead. This may fix the search issue
Any help offered comes with the best of intentions. Use it at your own risk. In any case, please make a backup of your existing environment before applying any changes.

snawaz
Posts: 18
Joined: 2019-09-14 17:12

Re: Select distinct in `lookup field`

Post by snawaz » 2022-11-18 18:00

Dear pböttcher,

You are a genius! It worked perfectly. Thank you so much. Appreciated

Post Reply