Prevent New Record If Date Already Exists

[iRadiate]

How does one prevent a user from entering a new record if a record with the same date already exists?
I am trying to limit new record entries to only one per day for a particular table.

Stephen

[GGandhi]

Re: Prevent New Record If Date Already Exists

hello
you can check result set nextrow() and see if exists, if so do not allow insert.

[iRadiate]

Re: Prevent New Record If Date Already Exists

hello
you can check result set nextrow() and see if exists, if so do not allow insert.

Sorry, I don't see how to implement this in my dialog. I assume this would be done in the Server-side dialogValidate event?
Can you give a few more details or an example?

[-Jinx-]

Re: Prevent New Record If Date Already Exists

If you are using an SQL backend you can add a unique constraint to the date field. If it's time format you'll need to do a little more work. If you want to do it from alpha, you'll probably need to code some xbasic like Gandhi is talking about.

[iRadiate]

Re: Prevent New Record If Date Already Exists

If you are using an SQL backend you can add a unique constraint to the date field. If it's time format you'll need to do a little more work. If you want to do it from alpha, you'll probably need to code some xbasic like Gandhi is talking about.

If you create a unique constraint in SQL, what happens when you try to commit data to the table from Alpha?
I like the sound of this solution, but may be a bit tricky for me as it's a datetime field and I'm not sure how to make it ignore the time portion.

[-Jinx-]

Re: Prevent New Record If Date Already Exists

It would show up as an SQL error when you tried to submit the record....so not ideal in that it doesn't throw a custom error message. With the time field problem added in the Xbasic option is probably the cleanest and most functional.

It might be best to check in the initialize event and then tell the dialog to load the record that has already been submitted today for editing.

[GGandhi]

Re: Prevent New Record If Date Already Exists

Can you give a few more details or an example?

here is the part of code i use to check if the data already exist

sqlSelect = "SELECT * FROM your_table_name WHERE date = :newDATE"
	dim args as sql::arguments
	args.add("newDATE",date)
	result = cn.Execute(sqlSelect,args)
	if result = .t. then
		dim rs as sql::ResultSet
		dim flag as l
		rs = cn.ResultSet
		flag = rs.nextRow()
		if flag = .t. then
	    your_funtion_name = "alert('The item is already in the list');"
	    exit function
	   cn.close()
	      end if
	end if

use this in the onClick event of the submit button
the code goes into server side XBasic ( the third one on the top)
date is available via e.dataSubmitted.date (assuming that is how you declared your control)
i am sure you can figure rest of the code.
i use this with a grid, i have not tested this on a dialog yet.
if it is not to your satisfaction let me know, i will test against a dummy table and report back to you.

[-Jinx-]

Re: Prevent New Record If Date Already Exists

And here's a way to do it if you want to check before the dialog loads. You can then populate the dialog with that record that was already created for editing.

Like Gandhi's code above, I HAVE NOT tested the Xbasic. I did test the SQL with MS SQL however.

This would go in the initialize event.

dim cn as SQL::Connection
	if cn.open("::Name::YourConnectionStringName") then
		dim SqlCommand as c = "SELECT YourUniqueID, YourDateField, CONVERT(VARCHAR(10),YourDateField,110)As ShortDate, CONVERT(VARCHAR(10),GETDATE(),110) as todaysdate FROM YourTableName GROUP BY YourUniqueID, YourDateField HAVING CONVERT(VARCHAR(10),YourDateField,110)=CONVERT(VARCHAR(10),GETDATE(),110)"
			if cn.Execute(SqlCommand) then 
				dim rs as SQL::ResultSet = cn.ResultSet
					if rs.NextRow() then
						dim recordid as n = rs.Data("YourUniqueID")
					end if
			end if
	cn.Close()			
	end if

if variable_exists("recordid") then
	'To edit this action, place insertion point anywhere in the command, then click the 'Action Scripting' button'.
	ExecuteServerSideAction("Populate Dialog from Tables::Populate_a_Dialog_with_existing_record")
else
'You only need this if you are opening the dialog to edit an existing record otherwise skip it	
'To edit this action, place insertion point anywhere in the command, then click the 'Action Scripting' button'.
	ExecuteServerSideAction("Populate Dialog from Tables::Populate_a_Dialog_with_data_from_one_or_more_tables")
end if

[iRadiate]

Re: Prevent New Record If Date Already Exists

I LOVE this forum

Jinx and ghandi, thanks very much for your ideas ... this will give me something to work on next week.

[iRadiate]

Re: Prevent New Record If Date Already Exists

here is the part of code i use to check if the data already exist

sqlSelect = "SELECT * FROM your_table_name WHERE date = :newDATE"
	dim args as sql::arguments
	args.add("newDATE",date)
	result = cn.Execute(sqlSelect,args)
	if result = .t. then
		dim rs as sql::ResultSet
		dim flag as l
		rs = cn.ResultSet
		flag = rs.nextRow()
		if flag = .t. then
	    your_funtion_name = "alert('The item is already in the list');"
	    exit function
	   cn.close()
	      end if
	end if

use this in the onClick event of the submit button
the code goes into server side XBasic ( the third one on the top)
date is available via e.dataSubmitted.date (assuming that is how you declared your control)
i am sure you can figure rest of the code.
i use this with a grid, i have not tested this on a dialog yet.
if it is not to your satisfaction let me know, i will test against a dummy table and report back to you.

Hi Ghandi,

I'm finally getting around to trying this method.
I'm a little fuzzy on what needs to get returned as javascript in the xbasic function ... see image:

Untitled.jpg

Would you be able to upload a screenshot of the xbasic function above in the onclick event?

[-Jinx-]

Re: Prevent New Record If Date Already Exists

function serverside_AbunchOfNumbersAndStuff

Ghandi's code.......
But you'll probably have to change it a bit because you are doing it in a serverside action instead of an Ajax Callback action javascript

add this...

dim jscmd as c

then where this is:
your_funtion_name = "alert('The item is already in the list');"

change it to:
jscmd = "alert('The item is already in the list');"

finally, end with this:

serverside_AbunchOfNumbersAndStuff = jscmd

jscmd will be equal to "" if there isn't a record in the list already. if there is a record it will be equal to the alert.

I'm not sure if that will work for you though because you are going to want it to stop the submission if it exists already and that won't do it.

What I would do instead is build a function in your xbasic functions and use an ajax callback which is functionally the same as the above except for it will allow you to use other javascript with the ajax callback. In other words, it will allow you to stay in "Action Javascript" mode.

So, you'd start with the Ajax callback action javascript to your function,
then you'd return a variable at the end of your function....
dim jscmd as c
jscmd = "var existingrecord = true"
functionName = jscmd

Then, you'd use inline javascript to check that variable returned by your function:

if (existingrecord == false){
dialog.object}.submit();
}
else{
alert('record already exists');
}

Does that make sense?

[iRadiate]

Re: Prevent New Record If Date Already Exists

Thanks Jinx,

I think I understand what you're saying. I wouldn't want the submit to occur so I'll play around with your second idea it and see if I can make it work.
Appreciate you taking the time to reply.

Stephen

[-Jinx-]

Re: Prevent New Record If Date Already Exists

Nevermind, I'm an idiot.

Just return the javascript to submit the record or javascript to alert the user that it exists at the end of your action. I don't know what the heck I was thinking.

Clear out your submit button by clicking on server side action scripting and then do this in your serverside action script....

dim jscmd as c

run your code

if record exists
jscmd = "alert('The record already exists!');"
else
jscmd = "{dialog.object}.submit();"

serverside_AbunchOfNumbersAndStuff = jscmd



It was so simple, I don't know what I was smoking earlier....must have been sleeping still.

[iRadiate]

Re: Prevent New Record If Date Already Exists

Nevermind, I'm an idiot.

Just return the javascript to submit the record or javascript to alert the user that it exists at the end of your action. I don't know what the heck I was thinking.

Clear out your submit button by clicking on server side action scripting and then do this in your serverside action script....

dim jscmd as c

run your code

if record exists
jscmd = "alert('The record already exists!');"
else
jscmd = "{dialog.object}.submit();"

serverside_AbunchOfNumbersAndStuff = jscmd



It was so simple, I don't know what I was smoking earlier....must have been sleeping still.

Well, you may have been smoking something ;-) but you're definitely not an idiot!
Unless you're Selwyn, this stuff warps anybody's mind sometimes :-)

I'll give it a try and report back. Thanks again.

[GGandhi]

Re: Prevent New Record If Date Already Exists

Hi Ghandi,

Would you be able to upload a screenshot of the xbasic function above in the onclick event?

http://screencast.com/t/TDsfmYB8maPP

function addReorder as c (e as p)
	'debug(1)
	dim id as n
	dim name as c
	dim description as c
	dim size as c
	dim qty_reorder as n
	dim case_cost as n
	dim vendor as c
	dim vendor_item_id as c
	id = e._currentRowDataNew.id
	name = e._currentRowDataNew.brand
	description = e._currentRowDataNew.description
	size = e._currentRowDataNew.size
	qty_reorder = e._currentRowDataNew.qty_reorder
	case_cost = e._currentRowDataNew.case_cost
	vendor = e._currentRowDataNew.name
	vendor_item_id = e._currentRowDataNew.vendor_item_id
	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
	'dim count as n
	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
		dim flag as l
		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_reorder_qty,item_case_cost,item_vendor,vendor_item_id)	VALUES(:newID,:newNAME,:newDESCRIPTION,:newSIZE, :newREORDERQUANTITY,:newCASECOST,:newVENDOR,:newVendorItemID)"
	dim args as sql::arguments
	args.add("newID",id)
	args.add("newNAME",name)
	args.add("newDESCRIPTION",description)
	args.add("newSIZE",size)
	args.add("newREORDERQUANTITY", qty_reorder)
	args.add("newCASECOST",case_cost)
	args.add("newVendor",vendor)
	args.add("newVendorItemID",vendor_item_id)
	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

this is the code that runs on onClick event of the button. it is an xbasic function. on the picture you had serverside xbasic. this is not what I used.