Create new records in UX with unique values in three fields

[Stephen Schell]

Hi

I am trying to control the creation of new records in a MYSQL table. I want to allow new records to be saved only when they are unique. I have set up a UX form but am struggling with creating only Unique records. What I am trying to achieve is if the combined value of (Field1 + Field2 + Field3) is unique then the record can be saved. I have set it up as a simple record but it could be a Cascading List as well.

I am assuming that this is done with an Ajax callback in dialogValidate in Server-Side events when the submit button is pressed but cannot figure out what the code would be.

Can anyone provide some advise please?


Thanks Steve

[lgrupido]

Re: Create new records in UX with unique values in three fields

In the help section of Alpha, lookup - "executing a sql query with a sql connection object"

That has an example of how to connect with Xbasic and view the results.

So you will want to setup the connection string, run a sql query such as

select count(*) as numberof from mytable where id1 = e.datasubmitted.id1 and id2 = ...

The see if numberof = 0.

If it is zero, it is unique.

Start there and shout back if you have further questions.

[Stephen Schell]

Re: Create new records in UX with unique values in three fields

Thanks

I will give that a try.

[Davidk]

Re: Create new records in UX with unique values in three fields

Something else you could try. Let MySQL and Alpha do the work for you. Set up a Unique index in MySQL with the Index Fields column containing your 3 table fields.

Then, in the Server-Side event onDialogValidate add this code after your ExecuteServerSideAction...

dim myError as c = js_escape(rtc.errorText)

if myError <> "" then
	e.javascript = "alert('" + myError + "');"
end if

After the ExecuteServerSideAction completes... if there is an error... it will be in rtc.ErrorText.

You can test for a value in rtc.ErrorText or rtc.FatalError (which will be .T. or .F.)... whichever you like.

If there is an error, set e.javascript to do whatever you like. In this case pop up an alert with the full message. I would parse the message and just send back a few details... but that's the general idea. I might parse the message because the returning error is a bit wordy - perfect to have it all, but we don't need to see it all.

Normally, because a ServerSideAction sets e.javascript we add our own e.javascript to it so as not to overwrite the ServerSideAction setting. But, in this case, because we have an error and we want to control what's coming back, just send back your own e.javascript... not Alpha's as well.

If there is no error, then all will continue as normal.

So... in summary... this lets MySQL do the work and send back an error if there is one. If there is an error, we intercept it and push out our own.