SOLVED - Lookup Where Clause

stefank

New member
Dear forum,

I am just new to DaDaBIK but it is very impressive in the possibilities and I start moving my databases from another product to DaDaBIK.

However I have just an issue: I want to use in the "where clause" for lookup tables a value of the actual form. Maybe I can work by using global variables as described in the hint, but I think it is not smart.
Is there a possiblity to use values of the actual form for the where clause of the lookup table.

Thanks a lot for your help,
Stefan.


Your current DaDaBIK version
You are using DaDaBIK version 11.4-Elba enterprise, installed on 22.04.2022 (installation code: 178556262ef9aa801f), the latest version of DaDaBIK is 11.4-Elba released on 28.02.2022

You are running the latest release of DaDaBIK

In case you want to upgrade to a more powerful edition (from Pro to Enterprise/Platinum, from Enteprise to Platinum) please contact us.

System info
PHP Version: 7.4.28

mysql version: 5.5.5-10.4.24-MariaDB

Web server: Apache/2.4.53 (Unix) OpenSSL/1.1.1n PHP/7.4.28 mod_perl/2.0.12 Perl/v5.34.1

Client: Mozilla/5.0 (Macintosh; Intel Mac OS X 10_15_7) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/99.0.4844.84 Safari/537.36 OPR/85.0.4341.60
 

eugenio

Administrator
Staff member
Hello,
it is not possible but if you describe better your use case you might have other solutions.
Typically cascade lookups are used.

Best,
 

stefank

New member
Dear Eugenio,

my use case is:
table 1 with a lot of items
id_table1
name_table1
id_use_table2_table1

table 2 which will use a table one entry via lookup table
id_table2
id_table1_table2
value_table2

Now I have in table 2 a field with a drop down list and a lookup to table 1
but I want only this items show up, which are not used BUT which used in this entry (otherwise it will not show up anymore)

so for the WHERE clause I want to use

ISNULL(id_use_table2_table1) OR id_table1 = id_table1_table2 (from the actual form entry of table 2)

The first is preparing the list, which are not used - the second is providing me the actual used item.

I hope it is now clearer for understanding.

Maybe this is a stupid solution from my side and there are much better available (I hope so).

Thanks a lot.

Stefan.
 

eugenio

Administrator
Staff member
Hello,
no sorry, it's not very clear to me. You have in table2 a lookup field (taking items from table1), you don't want to fill the lookup with all the table1 records, you want to add an additional constraint that however is not clear to me: I guess it's that ISNULL(id_use_table2_table1) but id_use_table2_table1 is in table 1 so it wouldn't be in the table2 form anyway.

Best,
 

stefank

New member
Hello Eugenio,

I am very sorry for my unclear description.

I want to show in the Lookup table only the UNUSED items of table 1 (for this I use ISNULL(id_use_table2_table1)), but I have to filter also the actual used item for the actual datarow of table 2, because otherwise it will disapear and the lookup field is only showing the unused table 1 items.

I hope now it is clearer.

Thanks a lot.

Best regards,
Stefan.
 

eugenio

Administrator
Staff member
Hello,
I am still not sure if I got what you mean with "used/unused" but I think I have understood the general problem.
I don't see an easy way to add this logic into the where clause in form configurator, maybe you can try creating an SQL VIEW and use the view (instead of the table) as the source of your lookup field.

Best,
 

stefank

New member
Hello Eugenio,

with used and used I mean if the item of table1 is used as reference item in table 2 (used) and if not (unused).

I try with views maybe I get it up and running, but I will need for the where string the actual ID value of the actual used dataset of table2. Is there a possibility?

Again thanks a lot for your help.

Best regards,
Stefan.
 

eugenio

Administrator
Staff member
Hello,
ok so just to summarize you want a standard lookup field but you don't want duplication in such field, is that correct? If that's the case you can:

1) Set a custom validation rule (the user will see the used items as well in the menu but if they choose them, they will get an error)

2) Try to filter (as discussed before) using a VIEW or the form config. where clause parameter.
In both cases you probably need to use a global variable to keep track of the current ID.
Let's say you have a products table (containing a lookup field id_brand) and a brands table, in that case the where clause could be:

id_brand not in (select distinct id_brand from products)

this would work for the insert form, but not for the update form (the field would be blank because the value is used)

so you should add the current id_brand to the where clause:

id_brand not in (select distinct id_brand from products) or id_brand = .....

which is something you don't know in advance but you can set it as global variable when the use enters in edit mode a record of the products table.

Best,
 

stefank

New member
Hello Eugenio,

okay thanks a lot for your answer and help.

I was unsure to use the global value, but now it is clear for the WHERE clause and it's functioning well.

I added the startup function for defining the global variable.

I have a last question Where can I set the global variable when the user enters in the edit mode of the record?

Thanks again,
Stefan.
 
Last edited:

stefank

New member
Hello Eugenio,

I did some tests and now I think I understood the concept. I used the layout hook for the edit page at the position before and I set now the right id by using a query and the where_value.

Thanks a lot,
Stefan.
 
Top