Help with validation rule

[twwtc]

I need help with a validation rule for a field called "Active" in a grid.
This field can only be set to "Yes" or "No"
When entering a new record, I would like a rule that checks to see if there is another record that has the value of "Yes" if so, return an error message.
I am trying to warn the user if there is already a record that is set to "Yes".
Thanks
Tony

[madtowng]

Re: Help with validation rule

Hi Tony,
Is this something that can be done using the either the onFocus event for the "active" field,
or the onBlur event of the field that sets the unique id you are using to lookup the active field?

example : if this is based on whether or not an account is active, you would use something like

function eventname as c(e as p)
dim errortext as c
dim vargs as ::sql::arguments
vargs.set("acct",<accountID>
if sql_lookup(<connection>,"accountID=:acct","active",vargs) = "Yes" then
errortext = "This account is already active"
eventname = "alert('"+errortext+"');"
else
errortext = "No"
end if
end function

You would need to use the e.pointer variable names where required, but I think this does the general task.

[twwtc]

Re: Help with validation rule

Gregg
My field "active" does not have anything to do with an account being active or not.
I use the field "active" to return an certain group of records from a table.
So I could have used as an example a field named "switch_setting". The only two values permitted are "on" and "off" and I only want to permit 1 record at a time to be "on".
When a user is enters a new record and they get to the field "switch_setting", I would like to check the table and alert the user if there is another record where the value of
"switch_setting" equals "on".
Thanks
Tony
Thanks
Tony

[madtowng]

Re: Help with validation rule

Tony,

We missed on something.
I understand the "active" field can either be "Yes" or "No".
The question is What is active , and what criteria are used to determine with it's active or not.

[lvasic]

Re: Help with validation rule

Depending on where the other record might be. It is either a loop through the grid or an Ajax-Callback.

[madtowng]

Re: Help with validation rule

I think we are getting closer.
A company I work with has been in business for over 20 years.
They have many customers that for whatever reason have not done business with them
during the last 18 months. Those customers are marked as Inactive.

A second example. I have a AAA membership.
Last year, I chose to not renew.
If I call AAA, I know they will say I have an Expired (inactive) account until I pay my membership dues.

[twwtc]

Re: Help with validation rule

I keep track of golf tournaments. When a tournament is on going, the field named "active" is set to "Yes"
Once this tournament is over, it should be changed to "no"
When a new tournament is being entered, the field active should be set to "Yes", but if the last tournament was not changed to "no" this causes problems

[madtowng]

Re: Help with validation rule

That's the answer I needed.
The code below should guide you in the right direction,
The event or action it takes to run the code depends completely
on when you want it to make the announcement. Obviously, you can
also set the value of Active, and eliminate the possibility of user error for this field.

function eventstatus as c(e as p)
dim errortext as c

if sql_lookup(<connection>,"active='Yes'","count(*)") >0 then
errortext = "There is an active tournament"
eventname = "alert('"+errortext+"');"
else
errortext = "No"
end if
end function

[twwtc]

Re: Help with validation rule

Gregg
Thanks, I will give this a try!
Tony