If Statement in Lookup Settings

Applications Forms
#1

Version=SC7
I am using a multi-record Form
Field type =select
The Lookup settings are set to automatic

I need to include an “if” statement in the select function with reference to another field in the form as below

IF(TRDAccSel=“M”)
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters
WHERE AttCustCode=[global_attcode]
ORDER BY AttUserCode, Client, Matter

Is my statement incorrect or does SC not allow the “if”

when running the app I get an error message :Error while accessing the database:
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'IF(TRDAccSel=“M”)SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter) ’ at line 1

Can anyone Help ?

#2

You need to have a valid sql statement for this. The if… is not valid sql. That’s why you get this error. Depending on your needs you need to use something to work-around. I.e. hide the field if it doesn’t matches the criteria, change the filter of showing the correct value(s) or use a lookup (caption link).

#3

In the single record form I have a number of fields inter alia TRDAccSel (This field has 3 select options M,S,G)
Depending on the option selected the field TRDAcc must display accounts from one of three tables

I have been trying for the past week to get this right to no avail

What work around will do the trick
I have tried ajax on change; sc_lookup

#4

It needs a bit of work but with this you probably get more in the right direction.
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters1
WHERE AttCustCode=[global_attcode] and ‘1’=[dbselector]
union
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters2
WHERE AttCustCode=[global_attcode] and ‘2’=[dbselector]
union
SELECT AttCustCode, sc_concat(AttUserCode, Client, Matter)
FROM matters3
WHERE AttCustCode=[global_attcode] and ‘3’=[dbselector]

Assuming matters1, matters2 and matters3 have similar columns that are checked.

I tested in oracle a similar one of two tables both having a NR and NAAM as columns.
select nr,naam from acc where ‘1’=‘2’
union
select nr,naam from loc where ‘2’=‘X’;
– no rows as result

select nr,naam from acc where ‘1’=‘1’
union
select nr,naam from loc where ‘2’=‘1’;
–only the rows of acc

select nr,naam from acc where ‘1’=‘1’
union
select nr,naam from loc where ‘2’=‘2’;
–the rows of acc and loc

So you may have to make something like that.
Be aware that updating will probably fail.
I havent tried it in scriptcase but it is a normal way to select data from different tables semi dynamically.

#5

Thanks
I will try this

#6

Hi… this work for me, write in “lookup settings” \ "SQL Select Statement ":

SELECT idsegurosalud, segurosalud
FROM
catalogo_segurosalud
WHERE
(idsegurosalud <>{idsegurosalud})
AND (idsegurosalud < (SELECT IF({idsegurosalud}<=2,30,30))
AND (idsegurosalud > (SELECT if({idsegurosalud}<= 2, 3, 0))))
ORDER BY idsegurosalud