[SOLVED] Change Select on add new.

Scriptcase 8 Discussion
#1

Hi all I hope someone can help, I may be over looking something but have searched every where for a solution.

I have a very simple form (editable grid view).

the select is this for the grid on one field only

SELECT id_warehouse, name FROM ps_warehouse

my question I want the ‘add new’ row to not use the same select as above. ( the reason is i want to filter out so the user cannot add the same warehouse id for a product)

I would like it to use this instead

SELECT id_warehouse, name FROM ps_warehouse WHERE id_warehouse NOT IN (SELECT id_warehouse FROM ps_stock WHERE id_product=id_product)

can this be achieved ?

#2

I’m not sure if the select is triggered after each and every insert. Afaik the screen is build and after that there’s ajax on each line, but I’m not sure if that’s ‘live’ when you do the pulldown or if the data is already there. But in general you can use the onafterinsert event to change the sql. General idea is that behind the where clause you replace the text by [myarg] Initially you can fill this with 1=1 causing it to be ignored. In the event you can then apply id_product=id_product although imho this is not correct as it is ambigious and you need to declare the table to use.

#3

I’m sure the select is triggered after each and every insert, I will try the onafterinsert and see what happens.

#4

Ive tried everything but no luck :frowning: it looks like that data is there before I can make changes.

#5

Ok i finally fixed it, and am sharing if anyone has needs the same help. I created a variable for the where statement (thank you aducom for pointing me in the right direction) then used macro if (sc_btn_new)
{
[glob_val] =“id_warehouse NOT IN (SELECT id_warehouse FROM ps_stock WHERE id_product=17)”;
}

in the onload event.

working now

1 Like