Announcement

Collapse
No announcement yet.

[SOLVED] complex lookup fields failed after update

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

  • [SOLVED] complex lookup fields failed after update

    I have updated SC & now this sql code in the lookup field isnt working:

    I have had more complex lookup SQL code from like 3 tables, concatenating multiple fields


    SELECT sc_concat( ClassName , " - ",ClassYear, " - ",Section)
    FROM feesstructure, classes
    WHERE feesstructure.ClassID = classes.ClassID
    ORDER BY ClassName , ClassYear, Section


    This same sql code (after removing sc_) works well in mysql server (phpmyAdm)

    error lookup field.png

  • #2
    luckyelms,

    I do queries like this frequently, but I always follow a couple of rules:

    1. I always prefix the column names with table name
    2. I always use "as" so that sc grids name the fields as I want
    3. I always use MySQL's CONCAT() instead of sc_concat.

    So, my version of your query would look like this:

    Code:
    SELECT
    CONCAT(c.ClassName , " - ", c.ClassYear, " - ", c.Section) AS myColumn
    FROM
    feesstructure AS f, classes AS c
    WHERE
    c.ClassID = f.ClassID
    ORDER BY
    myColumn
    I am guessing that ClassName, ClassYear, and Section are in classes.

    If my guess is true, then you need to also reverse your WHERE clause as I showed.

    See if this works.

    Dave
    Last edited by daveprue; 03-07-2015, 08:09 AM.
    Dave Prue
    Code Whisperer
    Lahar International Corp
    www.lahar.net

    Comment


    • #3
      no ...only class name is in table class, the rest are in another table!
      nway i will try it. indeed when i selected debug (in the application menu) it showed that no results where returned

      Comment


      • #4
        Originally posted by luckyelms View Post
        no ...only class name is in table class, the rest are in another table!
        nway i will try it. indeed when i selected debug (in the application menu) it showed that no results where returned
        The lookup of a field need to enter a value and one for display.

        Your select is setting only the value to be inserted, is missing the display value. Try changing your code to:

        SELECT sc_concat( ClassName , " - ",ClassYear, " - ",Section), Section
        FROM feesstructure, classes
        WHERE feesstructure.ClassID = classes.ClassID
        ORDER BY ClassName , ClassYear, Section


        Wait your response.
        Best regards,
        Thomas Soares.
        ScriptCase International.

        Email: t.soares@scriptcase.net
        Visit our Blog: http://www.scriptcase.net/blog/
        Visit out fan page: http://www.facebook.com/Scriptcase

        Comment


        • #5
          Originally posted by Thomas Soares View Post
          The lookup of a field need to enter a value and one for display.

          Your select is setting only the value to be inserted, is missing the display value. Try changing your code to:

          SELECT sc_concat( ClassName , " - ",ClassYear, " - ",Section), Section
          FROM feesstructure, classes
          WHERE feesstructure.ClassID = classes.ClassID
          ORDER BY ClassName , ClassYear, Section


          Wait your response.
          Worked perfectly....thanks.
          NOTE: This is true on forms only ( u need value to b inserted & value to display). On grids, the original code works perfectly!!

          Comment

          Working...
          X