Populate Fields from A Secondary Data Source

[mg]

Hi, I'm selecting a record from a SQL database based on selection criteria
(from a secondary data source) and once a record is selelected, we'd like to
populate several fields that are pointing to the Main data source (another
SQL database).

Can someone point me inthe right direction for populating fields from a
secondary datasource in the same form or perhaps even from a different form
if necessary? Thanks.

 

[mg]

I was rushing out the door to play tennis when I posted this thread so I hope
it's clear. ;-) To add a bit more information the secondary datasource
points to a table with over 20,000 records so I don't think it's practical to
prepopulate all the relevent fields (e.g. patientID, name, address, etc.).

However users may not always have a patientID to select just one record.
They may have only last name or no information which could bring over 20,000+
records! So this is an additional issue I'm not sure the best way to handle.

But my original post is basically asking how to populate corresponding
controls connected to the main dataFields once the user finally selects one
demographic record.

Finally, if they enter a patientID manually we also need to check it against
the secondary datasource to make sure it is valid. I'm looking through some
of the sample forms to see if any of them contain examples of this kind of
functionality...

 

[mg]

I guess I'll have to answer my own questions. ;-) For the InfoPath novice it
sometimes takes days to sift through the mountain of material--especially if
you don't already know what you're looking for.

Anyway, for the second issue of too many records for InfoPath to handle,
this is what I found: InfoPath chokes when handling large data sets (in this
case > 20,000 records) so you need to limit the number of records sent to the
InfoPath client. InfoPath is also not very good at this task, at least when
using a SQL Server data connection. It will only accept very basic T-SQL that
pulls the entire table to work with in a disconnected state. Only after
retrieving the entire table can InfoPath provide the functionality to narrow
the record set using query parameters.

It doesn't appear that InfoPath can accomodate a parameterized T-SQL query
that accepts input directly from form fields. One workaround is to create a
T-SQL stored procedure along with scripting in the form to accept search
criteria and only return records that meet that criteria.

I'm about to try this and will report what happens. Meanwhile, if any
experienced InfoPath developers want to correct any novice misconceptions,
please be my guest!