checking a value in a db before inserting

Using the DataAssist Insert is there a way to check a value in an exsisting recordset before inserting?

Here is what I need to do:

I have two tables that both have date fields in them. Data is already collected for table 1.

What I need to do is when the user enters a date in the second table, it needs to check that the date enters is not later than the date in table 1 before inserting this new record.

So, if the date in table 1 is 2011-01-31 then when a user goes to enter a date in table 2 it has to be before or equal to that date or else the form does not submit.

Is there such a trigger with DataAssist?

Thanks
Brad

you would need to create a recordset to compare the entered date with the date in table 1

SELECT table1.DateColumn from table1 where DateColumn >= paramEnteredDate


create a parameter:
Name: paramEnteredDate
Type: Date
Default Value: -1
Runtime Value: $_POST['dateFormElement']


you may need to use date formatting on the DateColumn to get the entered format to match the format in the database:
date-and-time-functions.html

Then in the Insert Record behavior for table 2, you can use the Recordset Not Empty trigger.

That makes good sense. Can you set two triggers to a form? Wouldn't I also have to set a trigger for when the submit button is pushed?

Well, with the way the recordset is configured, it can only return a record if the form is submited.

By Setting the default value of the parameter to use -1, the recordset will never return a value unless the form post is present. so using the Recordset not empty trigger is in essence using the Form Post AND Recordset not Emtpy trigger.

Thanks Jason,

That worked but just kept submitting the page as soon as it was loaded. Is there a way to prevent it until data is actually entered?

My work around was to set a date far into the future as the default.

I guess you could create a record to query the first table to see if it is empty or not, the set the trigger for the server validation to use Recordset not empty.