Announcement

Collapse
No announcement yet.

If Statement in Lookup Settings

Collapse
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • If Statement in Lookup Settings

    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).
    Albert Drent
    aducom software netherlands
    scriptcase partner, reseller, support and (turn-key) development
    www.scriptcase.eu / www.scriptcase.nl

    Comment


    • #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

      Comment


      • #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.

        Comment


        • #5
          Thanks
          I will try this

          Comment


          • #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

            Comment

            Working...
            X