InfoPath Dev

Hi,

Just wondering, if I have a Billing table in my database that has columns, ClientID, BillAmount, BillDate, can I populate these fields into my form dynamically? Dynamically in the sense, for example I want to retrieve Billing details from Client 12345 and there are only 3 bills to date, then I will have a 3x3 table. If I query on client 23456 and there are 5 bills, then a 5x3 table appears.

Unfortunately, repeating tables ain't behaving the way (dynamically) as I hoped. An added complexity is that one of the columns has to be read only...

My current, rather lame, workaround is to use 3 list boxes to represent my 3 columns, set size margins to zero to make them look like tables, lengthen the boxes by an exaggerated amount and put them into a scrolling region. This way, the no. of rows should not exceed my list box and users can scroll via the scrolling region. However, users won't be able to update the rows this way and I have to give them a textbox or something to update.

Does anyone have any good idea on how to dynamically populate table fields from database tables? Thanks!

Hi --

You want to actually copy the data from your table into the main data source of your form? It sounds like you do, since you want the users to update information....

If your data source has 3 rows, a repeating table bound to it should display 3 rows. If it has 5 rows, it should show 5. I'm having trouble picturing your workaround.... what is the behaviour you are seeing when you add a repeating table bound to your billing table data source?

If you need to copy your data from your secondary data source into your main data source, you can use code or qRules. If what you need is to ensure you have the same number of rows in your main data source as your secondary data source, you could again use code or qRules, or (kind of an ugly hack, here) include multiple instances of your main data source repeating group by default (under Tools / Default Values) and hide any that won't be needed....

Making a field read only is easy enough -- just use an expression box bound to the field.

At any rate, perhaps explain a bit more about what you are trying to do and maybe I can be a bit more helpful?

Hi Hilary,

 Thanks for your suggestions! Previously I was having trouble because I was trying to tie my secondary data source data to a repeating table which I've added separately. The end result was that this table did not populate according to my DB table. In fact nothing came out and only the first row was populated.

Your comment on adding a repeating table bound to my secondary data made more sense! Instead of creating a table separately and trying to bind, I now added the table straight from the data source and wala! It gave me exactly what I needed.

 Thanks again for your help!

Hi Hilary,

End up, I still need to copy my repeating table data (from data source) to another repeating table on my form. These data (say billing accounts history with Bill date and Amt) is drawn from my database and can be binded into a repeating table as you mentioned above. However, I would need this table to be editable, writable and able to insert or delete rows. Right now, binding this data to my repeating table allows users to write but not insert nor delete rows. Can this be made possible?

If not, is there a way to copy this repeating table into a normal repeating table where users can then insert new rows for additional billing details? When I use rules to copy repeating table field data, only the first row fields are added.

Hope you have some advise, thanks!

Zhen Yuan

I think you are saying you want to copy data from a secondary data source into your main data source? If so, qRules can help with that. But is your intent to be able to update your original data source? That is, when you copy this data into your form from, say, SQL, do you then need to be able to update that existing data? Can you maybe explain more generally about the problem you are trying to solve?

Hi Hilary,

I do not need to resubmit the existing data to its source. What I'm doing is actually to draw data from SQL to my repeating table -> Update existing value or insert/delete new rows BUT no need for updates on source as my users are supposed to use another system to do this update.

However, currently when I draw data from SQL, it does not allow me to insert or delete rows on the existing data (editing fields is possible though). So I am asking if I can draw SQL data into a repeating table that allows me to insert or delete rows?

By binding my repeating table to a secondary data source, my ability to add rows is disabled.

Is it possible to achieve this without qRules?

You can write code to copy the data from your secondary data source into your main data source. If you were able to add and remove items from a repeating table bound to a secondary data source, the next time the form loaded the data from the data source, everything would be just as it originally was -- since the secondary data source was unchanged.

So you will need to either write code or use qRules, I'm afraid, as InfoPath offers no build in functionality for copying a repeating table from one data source to another.

You'll also need to consider what you would like to have happen when your original data changes -- does the data in the form get updated or refreshed to match?

Hi Hilary,

 Thanks, we will find a workaround for this.