Basic validation question

[WindForce]

Hi
In an invoice scenario with line items for products for instance, where the products are entered in a linked child grid, how can i tell the grid to limit the available productID to those not already in the grid? In a perfect world, i would like to omit in the lookup for productid 's , all those productid already entered in the grid, and if someone manually enters a productid that is already in the grid, this would generate a validation error...

Jaime

[Ted Giles]

Re: Basic validation question

You should be able to check if the item exists in the current table if the data has been saved, but you will need to ensure that the record you interrogate is the one linked by the Invoice Number to the Item.
In Desktop, the lookups can be filtered to exclude specific items, but I've not tried it using AS.

[WindForce]

Re: Basic validation question

So basically, if i am entering a new invoice, i could enter the same productid one line right after the next one :-(
I'd like to enter in the productid lookup definition something like "select productid from products where someFilter and productID not in {myGrid.ProductIDColumn}" :-)

[Ted Giles]

Re: Basic validation question

Depends at which point you have data to check against.
I'm no WAS guru, but as it's raining here, I'll see what I can come up with.

[Ted Giles]

Re: Basic validation question

Validation.JPG
Fields, Validation Rules should help.
As I said, I'm really not into WAS so others will have some ideas I'm sure.

Edit.
To keep it simple, suggest you run Submit after each line, else the user may get confused as to which is the correct line to delete.

[GGandhi]

Re: Basic validation question

hello

i did this for my reorder items where if you try to enter an item more than once you will get a warning and that line is not executed.

preamble: this is done in version 10.5, mysql backend (as in my signature) and using grid NOT dialog.

i used result set and next row() function to check.
if you are able to do this way and happy with it then it is good.
if you are not able to do then i can give you the code i used for you to study.
if you are going to do in dialog, i have not figured that yet, and especially version 11 seems to have more xdialog than one
and i have no plans of updating.

[WindForce]

Re: Basic validation question

Well, Gandhi, you scenario seems close to the one i am using now. Could you post/send your code so i can see? I am also using mySQL by the way...

[GGandhi]

Re: Basic validation question

hello
here you go..
1: the grid is based on a read only grid with search capability.
2: the grid contains a button to add to reorder list
3: the button calls an ajax callback function - addReorder
4: the addReorder function is listed below

function addReorder as c (e as p)
	
	dim id as n
	dim name as c
	dim description as c
	dim size as c
	dim vendor as c
	id = e._currentRowDataNew.id
	name = e._currentRowDataNew.brand
	description = e._currentRowDataNew.description
	size = e._currentRowDataNew.size
	vendor = e._currentRowDataNew.name
	dim cn as sql::connection
	dim result as l
	result = cn.open("::Name::local_MYSQL")
	if result = .f. then
	    addReorder = "alert('Could not connect to database!');"
	    exit function
	end if
	'check for the record if exists inform and exit
	dim sqlSelect as c
	sqlSelect = "SELECT * FROM inventory_reorder WHERE item_id = :newID"
	dim args as sql::arguments
	args.add("newID",id)
	result = cn.Execute(sqlSelect,args)
	if result = .t. then
		dim rs as sql::ResultSet
		rs = cn.ResultSet
		flag = rs.nextRow()
		if flag = .t. then
	    addReorder = "alert('The item is already in the list');"
	    exit function
	   cn.close()
	      end if
	end if
	'else
	dim sqlInsert as c
	sqlInsert = "INSERT INTO inventory_reorder(item_id,item_name,item_description,item_size,item_vendor)	VALUES(:newID,:newNAME,:newDESCRIPTION,:newSIZE,:newVENDOR)"
	dim args as sql::arguments
	args.add("newID",id)
	args.add("newNAME",name)
	args.add("newDESCRIPTION",description)
	args.add("newSIZE",size)
	args.add("newVendor",vendor)
	result = cn.Execute(sqlInsert,args)
	if result = .f. then
	    addReorder = "alert('Record could not be inserted!');"
	    cn.close()
	    exit function
	end if
	addReorder = "alert('Record successfully inserted!');"
	cn.Close()
exit function

5: the record is added to another table
6: i have other things to do with that table - not included