InfoPath Dev

Is there any way to use a query field to search a data text field for matching text.  Or use a query field to search for matching text in multiple data fields.  I do not know how to code.

 I have a form that keeps statistics on a project and the user fills out the form and enters the individual tools that were lost in the field for that project.  Currently they enter them into individual fields but I could make it one text field and have them separated by a comma if that would work.  What they want to do later is be able to query all projects that have any tools lost or query for a specific tool lost.  I would like to make the query field a dropdown list so they can select which tool and query on that.

I cannot figure out any way to use one query field to search many data fields.

 Anyone think of a way to do this?  Thanks!

So you are wanting a text field where users input a comma delimted list of tools that they lost. Correct?

Then in another view or another form they query this data?  How is this done? Is the data being stored somewhere or are you merging forms or what? How are all project forms aggregated?

I would either provide a list of tools which they could possibly use and select which one did not come back or have them enter them into a repeating, optional table.  This is much easier to deal with programatically.  If they do not like having to insert records I would say too bad, stop losing so many tools!  It sounds like they need some disinsentives.

You could take a comma delimited text blob and using substring commands, capture each item from the text box and put it in its own field.  But this is a pain to set up and I do not believe that much easier for the user to input.  You could use a code routine to parse text boxes but this would require some coding and special functions in your reporting application.

I have seen using the find command to do text search used somewhat effectively in these scenarios but again I am not sure how you are querying multiple forms.

Maybe give us more information of what you are trying to do but I think it is more work than it is worth to get creative here.

The form is currently connected to an Access Database.  I have a form they fill out then I want either another view or another form where they can query all projects with tools lost or a specific tool lost.

If I use a repeating table for the tools they enter I cannot capture that data to the database, it will only capture one item to the field in the record.  So I then tried using different fields, tool lost 1, tool lost 2, tool lost 3...  But when you query you can only bind a query to one field.  So if you bind and query tool lost 1 and look for hammer you will only get the records that have hammer in the tool lost 1 field.  What if a user enters hammer into the tool lost 3 field.  I tried to figure out how to make the query search accross all the tools lost data fields but cannot figure it out and do not know how to use code.

Next thing I tried was to use one text box where they could list all the tools they lost on the job like hammer, screw driver, wrench....  and thought I could find something that would let me query for a word in the text box but I cannot figure out how to do this either...

 Is there any way to enter an sql string into a button or something...

OK that helps.

 First I would recommend the Database Accelerator product. It would make what you are trying to do, interact with a database, much easier.

Qdabra Database Accelerator Suite Version 2.0 - Developer 

There are tutorials available in the files area.

To do this correctly with Access or SQL Express you would need to change your table structure. It sounds like you just have a flat structure, one record per project. You would need to put some of these items into subtables. LIke losttools, maybe client, or jobs performed, anything that would have a one-many relationship.

You should have created your form as a database form.  Your table lay out should have a project table with multiple linked tables.  So the project table would have a primary key, giving each record or project a unique ID and other data. Then you should have another table which is losttools.  This would have ID, Name, Description, etc. but also a projectID field.  This field would link each lost tool record or multiple records to a specific project.  This would be your table relationship.

When you create a database form, you would select you main table but then use the add button to add additional sub tables. It will prompt you for which fields to make the relationships with.  What this gives you is your main data source will have your main table data but then also repeating groups for each of the additional tables.  Then when you add new items and submit it will also update these sub-tables and you will have what you tried initially, which is the correct way to do it.

Let me know if you need more explanation.

Seems even some of the simplest of things cannot be done in Infopath and I always get redirected to the database accelerator product or a web service which I again have no clue about setting up.  I have downloaded ithe accelerator many times and tried to get it working...  No luck.

 I do have the form connected to the database as you suggested and I did think of the the different sub-tables you talk about.  Only problem is that Infopath can only submit data to one table.  It cannot submit the tools lost from a repeating table into another Access table.  It cannot submit to secondary data sources.  Which makes this so impossible.  If you could just submit to multiple tables it would make so many things so much easier to do...  Especially for those of us who cannot write code.

Let me know if you know a way to submit items to these sub-tables you speak of...

Thanks for your help

InfoPath can do a lot but you are right in some areas it is limited.

What I have described is possible.  You are right, you cannot submit to multiple seperate tables in InfoPath, however, you can query and submit to related tables as long as they have a one to many relationship.

So you need a main table, and all of your other tables need to relate back to a record in the main table.  I have done this successfully with nested tables three levels deep and with multiple child tables.  When you insert a record in the main table you will get a blank (default) record in each of the children, but you can add multiple child records to one main record. The key is that you must have a primary key for each table and the proper one to many child relationships configured.  When you add the data connection make sure it says both submit and receive.  If it does not show submit, something in your table structure is not right.

The only otherway to get around this is with web services, like the DBXL Web Suite.  Then you can query and submit without limits.  If this is something you are interested in contact me directly and I could help you get it set up to test it.  I have been improvng the documentation and tutorials and posting them on the community site so it is easier now than in the past.

Sorry for taking your time...  I guess I do not know enough about Access to understand any of this.

 I will have to look for another solution.

You have to structure your data correctly in your tables for the data connection to access it properly.

The primary table must have a primary key field either record ID or some other unique ID. Then in your subtables they would need a field that contained the unique ID from your primary table for the record they coorespond to and it can be a many to one. You should setup the relationship in SQL or Access but I do not think it is required. With this the data connection will see this and you should be able to set it up and both query and submit without a problem.

You would add the primary table then select add table and select your secondary table. It will then prompt you for the relationship and you can match the two fields from the two tables. You can add additional related tables in the same way.

Hope this helps.