Data Connections

Re: database connection and autofilling repeating table

adamrharding — Wed, 31 Oct 2007 17:49:30 GMT

Hi, another thing I just noticed is that your one of your tables does not have a primary key, this prevents submission.

Fix this and you can query and submit.

Happy Halloween

Re: database connection and autofilling repeating table

adamrharding — Wed, 31 Oct 2007 06:24:01 GMT

Sorry, shjould have welcomed you to the forums!

OK, I think the issue you are having due to the query being run again on submit and the form getting confused. That is why it is better to have it on a button so that it doesn't re-run.

To store the result you would need a field in the first database to act as the sum of the hours in the subsidiary database. Looking at your files you would need hourly rate, total

You can go into your repeating table to filter the results on the display tab of its properties.

One other consideration it would be worth having your tables connected the other way around so that the hours sum up for each client rather than you current setup.

Cheers

Re: database connection and autofilling repeating table

mgleed — Wed, 31 Oct 2007 05:15:26 GMT

Thank you for being so helpful, but there's still some issues..

I figured out a way, almost like how you said, to type a client's name and then it fills the address and the other repeating table also. I had to make a whole new form that was not blank, but rather based on a database so I could get the query options. But now I cannot store information of the controls within the form, which I'd used in my previous form using functions to calculate the total cost, taxes, discount, etc.. But I am now getting some error that the query cannot be submitted or something when I hit the submit button, so the form basically is useless now until I can figure out how to fix it. It was working before... I tried redoing parts of the form but to no avail.

But say I do get that fixed I can't figure out your explanation of getting the sum of the hours. I'm not sure I understand your explanation, butI think I might have done it the way you were meaning but it ended up adding up all the hours in the entire database, not just for the selected client. I don't know if theres some type of filter I could apply, but otherwise I'm not sure how I'd fix that. I also figured out how to format the total amount of text allowed in a text box, but I can't figure out how to constrain the decimal places instead.

Thanks for your help so far

see this link to download the files I'm using.. http://overheardonmyspace.com/pics/statements.zip

Re: database connection and autofilling repeating table

adamrharding — Tue, 30 Oct 2007 18:21:07 GMT

Easiest way to do this, I think is to have the two tables related by a common field in the same database. Then change the drop-down list to be a queryfield bound to the same field and add a query button to pull back the records in the tables that match the customer in the querfield.

To sum the hours you need a field ABOVE the repeating table in the relationship between the tables, then drop it in place and set it's default value to SUM(HOURS)

To fix the missing digit double-click the field and click, format, then format it to always have 2 d.p.

I hope this helps

database connection and autofilling repeating table

mgleed — Fri, 26 Oct 2007 06:36:27 GMT

Hi, I'm kind of new to InfoPath. I've got several questions and I think this could belong in at least 3 of these threads. So I hope some people will see this. :p

I'm trying to create a statement form. I figured out the way to autofill the address (from database 1) after selecting the client name in a drop-down menu by reading an autofill tutorial on this site. But, after selecting the client's name, I'd also like it to autofill the columns below by a link to another database containing the date, type of work, and amount of hours I helped them (database 2). I suspect I'd accomplish this by using a repeating table, but I can't figure out what rules i'd assign or what script I'd write to have it autofill. I don't think I've got the databases wrong, so I imagine I'm making mistakes within InfoPath.

Is it possible to say, select Client A, and have all the dates/description/hours in the database go with it, even if the client is on multiple rows? see the picture of database 2.

Despite my mishaps, I managed to figure out the formula to calculate the taxes, client discounts, total cost, etc., but I'd like to find a way to add up all the hours in the hours column so the total will automatically be calculated.

And one more thing, when I enter formulae into the bottom part of the form they don't take into account significant digits of prices. It'll say $120.2 instead of $120.20, if I can't figure out this part I don't think it'll be a big deal, but it'd be nice.

Please help!

Database 1

Database 2

Infopath Form