InfoPath Dev

I want to be able to take a value entered by the user and determine if it exists in another table that is part of the relational database that drives this form. I have added the table as a secondary datasource (it is not part of the main datasource and does not need to be) and have tried to write code to compare these values but I cannot get the SQL to work properly so if anybody has the proper code for that I would appreciate it. It seems to me that I should be able to create a data validation rule that compares the value the secondary data table but when I do this nothing happens at all. Any help is appreciated Thanks

Welcome to the forum.

Is there a reason why you are doing it with code?

You can set either a data validation or a rule on the field in the form that would compare the value of the field witht he value in your secondardy source.  You would just need to use a filter on your secondary possibly.

So an example would be the user enters a new company name in a field. I could add a rule that if the field is equal to the companyname field in my secondary data then trigger an alert and blank out the field or whatever. This would do a compare against all of the rows in your secondary and if a match was found return true and execute your actions. You could make this more complex with a filter but I think that is the direction you would want to pursue.

If you are working with data and various tables, I would recommend you check out the Database Accelerator.Qdabra Database Accelerator Suite Version 2.1 - Developer

It is a whole suite of web services. Web Services allow you to get around the limitations with InfoPath database forms only being able to query and submit to one table structure, etc.  If you are developing enterprise solutions, it can help a lot with a bunch of built in features and tools which make things much easier.

If you would like a demo let me know.

Thanks Clay, the filter worked and the data validation works now. Now the problem is the secondary source contains 80K records and it searches until it finds (or doesn't find). So my performance sucks, but I'll have to figure a work around for that

Yah that is why you want a web service so that you can query the data instead of loading all of it each time.

 if you can only load the fields you need in your data connection that could help some too.

This blog: http://www.infopathdev.com/blogs/mel_balsamo/archive/2009/02/04/comparing-items-in-a-list-against-an-updated-data-source.aspx might be able to help you in this kind of situation going forward.

 Cheers!