InfoPath Dev

I looked and didn't see an answer to my question. I have a text field with a value of FL, GA, or PR. I have another field that is based on an external database connection. I need to filter the data based on the state field. However, the database connection has a field that is a number 1= FL, 2= GA, and 3 = PR. How do I write a formula or whatever so that the filter works correctly?

thanks

You could change your initial textbox (with the value of FL, GA or PR) to a drop down list. Your 'Display' text could be FL, GA or PR, and your 'Value' could be the corresponding number that is correct for that field...Then when the user selects 'FL' the actual field value would be 1 and you'd be able to filter with that.

Yes, I thought of that but the problem is that the field already exists in online database and I cannot change it to a number field. If this is the only way to do it, then I will have to add a new field to the database. There is no way to create a formula to look at the field and convert it to a number?

thanks!

Well, translate() is a character for character type of thing, so that won't work.... Can you add another field to your data source? If so, you could use a rule on the text field to set the value of your 'helper' field to the appropriate number, then use your 'helper' field for your filter.... so your rule would have conditions, like if the field was 'FL', set the helper field value to 1, etc....

Looks as if I need to do it this way. Thanks!

Note that you will still end up downloading all items to your form. If you have a large query that could take time. To reduce the time and filter dynamically, take a look at our Web Service Solution Pack which lets you filter SharePoint lists or SQL tables using fields in your form. Because the filtering happens in the Web Service layer, the response time is much better than if the form had copied all of the data down and filtered client-side.

http://www.infopathdev.com/files/folders/dbxldownload/entry34123.aspx