InfoPath Dev

I have been charged with creating an online form that will be accessible on sharepoint. The form is basically an asset inventory form. Large numbers of techs will be manually verifying existing old database information manually by completing this new form online.

I have a copy of the old database in the form of an Excel dump. Ive uploaded the Excel file as a separate list in sharepoint.

I want the following:
I have a set of fields (asset tag, serial #, model#, etc.). I want to say that if user enters an asset # that already exists in the sharepoint list, then prepopulate my form's serial#, model# fields, with the values we already know.

Simple enough or so I thought....

I created a Rule on my asset# field in my infopath form.
It says if myasset# = datasourceasset#, then fire more rules: a) set mymodel to datasourcemodel, b) set myserial# to datasource#, etc. (Nested rules, all attached to the main asset# field.)

When I run the form it does prepopulate information in the addl fields, but it picks the first row of data from the existing sharepoint list, not the data to the specific asset# that was entered by the user.

What I really want to say is:
myserial# field should equal datasourceserial# where myassetID equals datasourceassetID.

I know I can create a Filter on a Value in a Rule, but Im not sure how to build the right filter. In my mind, I would like a Where clause, as in SQL, but I dont know the corresponding function using InfoPath.

Im no programmer, so an easy solution would be welcomed!

You're just missing the filter part.  You have to say "set mymodel to datasourcemodel where datasourceasset = myasset."  Do that for each one.  That's how you specify which record to use.  It's critical that the asset# is unique...

Oh, and you do that when you're in the screen where you choose the "set mymodel to datasourcemodel."  There should be a filter button at the bottom left of the screen when you're selecting the fields from your secondary data connection.

 This is awesome. Thanks! I think I just reversed the two values in the filter yesterday or something, while I was testing. That was exactly what I was hoping for.

 Next question...  User can now successfully enter an asset tag, and a machines serial#, etc now are prepopulated. I have that section set as a repeating table. So, if user wants to enter Asset #2, the data sitll prepopulates, but for this second entry, the filter is not being applied, and again, infopath is pulling the first row of data in my sharepoint list, not the filtered information for that specific asset # that is entered.

 How do I get my repeating table to understand that every new row must follow the same rules as the first inputed row? I monkeyed iwth the properties of the repeating table, and added my rules there, but then nothing prepopulated at all.

M

Hmm, perhaps instead of using rules, set the default values of the fields in the repeating table to the same way your rules were set.  This accomplishes the same thing except that the initial action isn't pushing the data to the other fields via rules.  Instead, if you set them to default to their respective values using the same filters, then upon selecting an asset within that row, the rest of the fields should populate accordingly.

The rest of the fields would not populate at all if I put my rules on the Repeating Table control. What I did instead was add a Button next to asset tag field, and am calling it a 'lookup button'. Attached to that button control are my rules to populate the additional fields. When a user adds a new line in the repeating table, the button control comes with, so a query can then be performed on that second line of input and populate the 2nd line's additional fields.

M

Shouldn't need to do that - it should just auto-populate using default values.