Testing for Uniqueness for Two Fields when Submitting New Record via Grid

[cpgood]

How do you test to see if a new record is unique when you need to test for uniqueness on two fields? I can use the Cross File Validation to test for uniqueness if only one field needs to be tested but I cannot determine how to do this if a record needs to be tested for uniqueness based on the values of two fields.

Thank you for any one's help,

CP Good

[Keith Hubert]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Is that when each field is unique or when both fields together make a unique value?

[cpgood]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Keith, thank you for your clarifying questions. When both both fields together make a unique value is what I am after.

Thank you,

CP Good

[TheSmitchell]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

What kind of database? SQL? DBF? You could put the UNIQUE enforcement into your tables if your database allows for it. I've done this in MySQL. I don't know what DBF offers in terms of unique fields:

ALTER TABLE `some_table`
ADD UNIQUE KEY `name_of_constraint` (`field_a`, `field_b`);

Putting the unique constraint in the database has the added bonus of preventing you from violating that constraint should you decide to make more than one grid to update/add data to the table. Issuing a message to the user that they need to pick something else would just require capturing the error returned from the database & processing it into a message that's intelligible.

If enforcing uniqueness in your data isn't an option, you could query the table to see if the field pairing already exists...

SELECT * FROM `some_table`
WHERE `field_a` = :new_field_a AND `field_b` = :new_field_b;

...and issue an "invalid" error if you get anything that matches.

I hope this helps.
---
Sarah

[cpgood]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah,

Thank you for your reply. I'm working with DBF's. I can certainly see your wisdom on the SQL backend of enforcing integrity at the database level. I've tried to enforce the uniqueness rules in the Field Rules of the DBF table but these rules are not being honored by the grid. I have the "honor DBF rules" checked for my grid.

CP Good

[TheSmitchell]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah,

Thank you for your reply. I'm working with DBF's. I can certainly see your wisdom on the SQL backend of enforcing integrity at the database level. I've tried to enforce the uniqueness rules in the Field Rules of the DBF table but these rules are not being honored by the grid. I have the "honor DBF rules" checked for my grid.

CP Good

Bummer that the DBF rules are not being honored here. If the table is setup with a unique constraint and "honor DBF rules" is NOT honoring your unique constraints, I would definitely send in a bug report to Alpha.

Querying your DBF table for the two values may be the best route. You can use the lookup Function (or one of it's variants) to accomplish the query.

Good luck!
---
Sarah

[madtowng]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

The dbf honor field_rules does not function as us normal people would expect.

I would think the table lookup should work.
I know I have a variation of it in at least 2 of my projects, but I can't remember
if I concat'd the fields to make the lookup easier.

[jocarop]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah Mitchell,


Where would one capture the error returned from the database and process it into a message that's intelligible?
Thanks for your help!


Jose

[madtowng]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Jose,

Sorry my previous response wasn't much help, but I actually dealt with something similar yesterday.

The function I couldn't remember is a5_get_records_in_query().
example : niteorders = a5_get_records_in_query("c:\yourtable.dbf","ctodt(add_date+' '+add_time) >= ctodt(dtod(date()-1)+' 19:00') .and. ctodt(add_date+' '+add_time) <= add_date+' '+add_time)

In this example, I am looking for records entered between 7pm yesterday and 5am today.
You could easily modify it, with a result of 0 meaning it does not exist, and anything else meaning it does exist.

Let me know if I can help more.

Gregg

[TheSmitchell]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah Mitchell,


Where would one capture the error returned from the database and process it into a message that's intelligible?
Thanks for your help!


Jose

I believe you can put the error handling parts in the OnSQLExecuteError server-side event. I have not done this, but it looks to be where you'd go:

function OnSQLExecuteError as c (e as p)
'This event fires when an error occurs after an INSERT, UPDATE or DELETE statement on a
'SQL table has been executed and the Database server has returned an error.
'The event allows you to clean up the error message before displaying it to the user.

You can also capture it when you write your own xbasic database queries:

dim cn as sql::connection
'setup the connection

dim sql as C
'set your sql statement

dim args as sql::arguments
'set all the arguments you reference in your sql statement

if (cn.execute(sql, args) = .t.) then
 'success case
else
 'failure case. This is where you can check the CallResult of cn and parse the error
 dim error_msg as c
 error_msg = cn.CallResult
 if ( "1088" $ error_msg) then
  ' a duplicate exists
 elseif(...) then
  '...handling of other error nums
 else 
  '...oh snap! Unhandled error. I'd dump the horrid contents of the error message from MySQL to the user here and tell them to call me.
 end if

MySQL provides a list of error codes on their website. It would make the most sense to put all your error parsing into some sort of global function you could reference anywhere from within your project.

[madtowng]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah,

Gotta love the "oh snap!" option.
One of these days I hope to learn at least as much sql as you know.


I guess I misunderstood the part about the error message, I thought Jose was trying
to trap a non-unique multiple column combination.

[TheSmitchell]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

Hi Sarah,

Gotta love the "oh snap!" option.
One of these days I hope to learn at least as much sql as you know.


I guess I misunderstood the part about the error message, I thought Jose was trying
to trap a non-unique multiple column combination.

This thread has gone off-topic. CPGood was the original poster. He is trying to enforce a multi-column unique constraint in DBF.

Jose has jumped in curious about what I'd mentioned in an earlier post about Triggers & error handling re: SQL. Ahhaha. Just realized my reply to Jose isn't of much help! Whoops. Honestly, I have written zero error handling for my project. Though I think you'd put it onSQLExecuteError. Ahhh. Yes. I think that's where it goes after reading the event description. I'm going to update my previous post.

As far as learning as much SQL as I know, that won't take long.
---
Sarah

[GGandhi]

Re: Testing for Uniqueness for Two Fields when Submitting New Record via Grid

hello this reply is to cp good.
i do not work with dbf files so there may be some limitations.
step 1> when you design the table after completing all the fields create a new field and in the field rules section on the default expression create a simple expression combining the two fields you want to combine.
then you can set uniqueness or not it is up to you. in the web i don't think it really matters, because you can take care of that later in the web component design.
step 2> now in the web component create a grid to enter data and select the table you want and select all the fields. and in the field that is supposed to be unique set the calculated field expression again setting those two fields. you can fill using the dialog box that comes up. and then set up uniques to not exist in the dbf file. all these are done using the dialog box that pops up.
and when finished test the finished grid and enter some data, the red marker saying what ever message you wanted it to show.
as you can see in my signature i am using version 10.5 and tested with some silly data and it works.
ps:
do you need to set the default expression when designing - i do not know for sure, you may skip that step just create a field and set the type to be a character.
rest of the steps can be done in the web part.
hope i made sense in this long reply.