[SOLVED] complex lookup fields failed after update

Scriptcase 8 Bugs
,
#1

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:



SELECT[INDENT]CONCAT(c.ClassName , " - ", c.ClassYear, " - ", c.Section) AS myColumn
[/INDENT]
FROM[INDENT]feesstructure AS f,
classes AS c
[/INDENT]
WHERE[INDENT]c.ClassID = f.ClassID
[/INDENT]
ORDER BY[INDENT]myColumn

[/INDENT]

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

#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

#4

[QUOTE=luckyelms;34046]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[/QUOTE]

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.

#5

[QUOTE=Thomas Soares;34059]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.[/QUOTE]

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!!