How? Custom SQL for lookup but using value from record as WHERE

If you're a new user of AppGini, feel free to ask general usage questions, or look for answers here.
Post Reply
User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

How? Custom SQL for lookup but using value from record as WHERE

Post by onoehring » 2021-01-07 12:12

Hi,

I have this setup:
Table tbl_boxes: Holds boxes
Table tbl_customers: hols customer information
Table tbl_customers_boxes: holds current (!) relation between customers and boxes (m:n). The relation will change.
Table tbl_jobs: holds jobs that need to be done with tbl_boxes.

In tbl_jobs the customer needs to be saved forever (history). For this reason I would pull him from tbl_customers (where he always exists) and not from tbl_customers_boxes (as the customer can vanish from tbl_customers_boxes).

Problem: I can not do this in AG with the lookup afaik.

I can not use a "dependent lookup" with tbl_customers (with tbl_customers_boxes it would work), but I need to show only customers for a job that actually have currently an entry in the current tbl_customers_boxes table.

So I am wondering, if I can use a field from the current record (ID_boxes) in my custom lookup query - and if yes, - how do I do this?
If no, it would be a great feature to be able to use a field from the current record in a custom lookup query.

Olaf

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by onoehring » 2021-01-11 12:14

Hi,

just wondering if anyone can tell me, if there is a way or, that there is currently no way to use table field-values in custom SQL queries.

Olaf

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

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by pbottcher » 2021-01-11 16:54

Hi Olaf,

not sure I got everything you need, but in the init function you can modify the query to your needs. Maybe that helps
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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by onoehring » 2021-01-11 16:57

Hi pbötcher,

yes, I know that, but for my setting - I may be missing something - I need to change the lookup-source (according to some value in my current displayed record) but keep my table-source.


Olaf

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

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by pbottcher » 2021-01-11 17:00

Maybe you can make a sample to see if that would work
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.

User avatar
onoehring
AppGini Super Hero
AppGini Super Hero
Posts: 1156
Joined: 2019-05-21 22:42
Location: Germany
Contact:

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by onoehring » 2021-01-11 17:25

Hi pbötcher,

what do you mean? Am I able to change the source of a lookup in the _init function?

Olaf

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

Re: How? Custom SQL for lookup but using value from record as WHERE

Post by pbottcher » 2021-01-11 19:58

Hi Olaf,

no you cannot.
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.


Post Reply