General

Re: Creating Infopath form with values from db field

Hilary Stoupa — Tue, 29 Mar 2011 16:05:48 GMT

I'm guessing we are cross posting a bit -- to clarify the first sample does not use qRules, but default values and a manual manifest change.

The second sample uses qRules.

So now you have two choices!

Re: Creating Infopath form with values from db field

Hilary Stoupa — Tue, 29 Mar 2011 16:04:34 GMT

Well, to use qRules, you'd need take the approach outlined in this document. Basically, you'd have to insert into your main data source the number of groups you need, then you can use a counter field to loop through and set values. Since the counter will move from n to 0, the items may be in the opposite order you expect, but you can resort them using SortTable.

Again, easier to explain with a sample. I'm attaching a sample form. The logic is on the qRules finishedLoading node and on the repeating "group2" in the main data source....

NOTE: This sample is injected with the trial version of qRules, and will expire on 6/15 (just wanted to add that for the benefit of future forum users who run across this).

Re: Creating Infopath form with values from db field

JackInBoston — Tue, 29 Mar 2011 15:59:42 GMT

That is exactly what I'm looking for! Did you use qRules for the form loads?

Re: Creating Infopath form with values from db field

JackInBoston — Tue, 29 Mar 2011 15:41:46 GMT

How could i get qRules to do this?

Re: Creating Infopath form with values from db field

Hilary Stoupa — Mon, 28 Mar 2011 22:08:19 GMT

You'll need to create a secondary data connection to your SQL table (you have DBXL, so you can use QueryDB for that). Then, the tricky bit is going to be copying your secondary data into your main data source --

Basically, you'll need to insert how ever many number of rows returned by your SQL query to your main data source. Then populate those with the correct traits. Unfortunately, out of the box, InfoPath has no UI way of accomplishing this. It can be done either with custom code, or with qRules.

There is probably a third option -- but it seems like it would have a greater potential for failure -- and that would be if you have a good idea of the maximum number of traits you'd have, you might be able to default your main data source to contain that number of rows in the repeating group where you want these, and modify the manifest to be able to populate the repeating fields correctly.... hmm. I'm attaching a sample, since it is easier to understand if you see it. Here's a blog post that talks about the manual manifest mods I made.

To check out the sample, right click the link in the heading to this post and save the XSN locally. Then you can right click on the XSN and select Design to open in design mode. Preview it to see the functionality....

Re: Creating Infopath form with values from db field

JackInBoston — Mon, 28 Mar 2011 19:53:41 GMT

thats exactly what i want to do, and unfortunately i dont have much experience with infopath or sql

Re: Creating Infopath form with values from db field

Hilary Stoupa — Mon, 28 Mar 2011 19:42:18 GMT

So, I think what you are saying is that when the form is opened, you want to have a table with all the traits and the user can select a rating for each trait? Then the user submits, and the data goes into your target table?

The tricky bit then is that you'll need to insert groups into your main data source, and copy in the Traits -- because you are going to have to have the data in the main data source. This can be done with custom code, or qRules can also do this (if I've understood correctly what you want to do?)

Re: Creating Infopath form with values from db field

JackInBoston — Mon, 28 Mar 2011 19:30:19 GMT

Thanks for replying so quick!

The lookup is within the db. Some of the values are:

Teamwork, Adaptability, Preparation, DecisionMaking, etc.

I am not sure how to pull those values in to the form so they would each display with a rating field next to them. When the form is submitted, the table would look something like this:

ID	Employee	Reviewer	Trait	Rating
1	Jane Doe	John Smith	Teamwork	4
2	Jane Doe	John Smith	Adaptability	3
3	Jane Doe	John Smith	Preparation	4
4	Jane Doe	John Smith	DecisionMaking	2

Re: Creating Infopath form with values from db field

Hilary Stoupa — Mon, 28 Mar 2011 19:17:22 GMT

Hey, welcome...

When you say:

JackInBoston:
The trait field has about 20 different values that i have setup as a lookup.

What do you mean? Do you mean that the control bound to the trait field is a drop down control? What are you using for the data source? Is it a secondary data source? Where are the ratings stored? Also a secondary data source?

I could use a little bit more info -- I'm pretty sure what you are trying to do is possible, but want to make sure I understand what you are trying to do, first....

Creating Infopath form with values from db field

JackInBoston — Mon, 28 Mar 2011 19:01:50 GMT

Hi All:

I have a table that has the following fields (this is for employee evals):

Employee

Reviewer

Trait

Rating

the form will be submitted through dbxl to the sql db.

The trait field has about 20 different values that i have setup as a lookup. What i would like to do is have each of those values display on an infopath form, with a rating field associated with it. How can i do this? I am at a loss and don't even know if it is possible.