Microsoft Access

Re: Infopath - Display data from a database

Hilary Stoupa — Fri, 30 Jan 2009 01:52:11 GMT

Oh good! Eval is great, but a little tricky at first.

Re: Infopath - Display data from a database

clawrence — Thu, 29 Jan 2009 16:16:19 GMT

Nevermind, I got it.

Re: Infopath - Display data from a database

clawrence — Thu, 29 Jan 2009 15:17:44 GMT

I am trying to do this in a form I'm working on, but I keep getting the error "does not point to a valid location path of a field or group." I'm trying to use the Quantity and LowestCompetitivePrice fields in the LineItems group of the data source shown below.

Re: Infopath - Display data from a database

Hilary Stoupa — Tue, 14 Oct 2008 23:06:48 GMT

Math is definitely not my strong point. I'm attaching the template again, this time using sum & eval, see if this helps.

The formula is sum(eval(group2, "my:field1 * my:field2")). Note that with eval, you can select the group with Insert Field or Group, but must type the expression in manually.

Re: Infopath - Display data from a database

Gary Burke — Tue, 14 Oct 2008 22:33:53 GMT

Hi Hilary,

I have been able to get into the form....however, the Grand Total did not calculate correctly.

I put in 10 in the 1st column and 20 in the 2nd column - the total calculated correctly on the row as 200

I then inserted a new row and put in 20 in the 1st column and 30 in the 2nd column - the total calculated correctly as 600 on the row.

However, the grand total was incorrect at 1500. It should be 800. The grand total is adding the 1st column together (10+20) and then multiplying that by the total of the 2nd column (20+30) - ie 30*50

Re: Infopath - Display data from a database

Gary Burke — Tue, 14 Oct 2008 22:28:08 GMT

Thanks so much for the example however, I need a username and password :-)

Re: Infopath - Display data from a database

Hilary Stoupa — Tue, 14 Oct 2008 14:07:31 GMT

So an expresssion box with the formula Quantity * UnitPrice (with your fields, of course) placed in a column in the repeating table doesn't bring back the per row total price?

For the grand total you could use sum(field) * sum(otherfield). I'm attaching a quick sample to this post, okay?

Re: Infopath - Display data from a database

Gary Burke — Tue, 14 Oct 2008 06:18:53 GMT

Sorry...I jumped the gun. :-)

All works fine until I introduce a repeating table.

In that section I have Quantity and Unit Price. These two fields are linked to my main data connection. Naturally I wish to extend the total for each row in the table, and to then have a Grand Total for all rows (of just the extended total).

My next part will be depending upon the value in the Grand Total, I will need to do x or y, so i will need to reference this value.

I have tried text box, but that has to be bound to a field in my data connection, and I don't have one, nor should I have one (its not good programming to have extensions stored in a database table, they get calculated when needed).

I have tried an expression box, but then how do i add these to obtain a Grand Total ? Plus it would appear that each expression box contains the same values, on each row, irrespective of the Quantity and Unit price used on each row.

Looking forward to your assistance once again.

Re: Infopath - Display data from a database

Gary Burke — Tue, 14 Oct 2008 03:04:04 GMT

All working just fine now......I suspect my problem was that my "where" clause was not querying the correct field :-)

Thanks for your help

Re: Infopath - Display data from a database

Hilary Stoupa — Mon, 13 Oct 2008 14:32:39 GMT

The filter in your XPath looks correct for what you are trying to do. I'm sure you've done this, but do verify that your user name is in your access DB exactly as it is being returned in the form (one way I do this is to write a query in my DB and copy and paste the contents of my text box directly into the 'where' clause).

Re: Infopath - Display data from a database

Gary Burke — Mon, 13 Oct 2008 05:59:24 GMT

Actually the POUserID is populated with the userName function.

The UserId is the field in the secondary data source.

The formula is trying to say "find the phone number in tblUsers (in Access) where its UserID (an indexed field) = the POUserID (as found on the form).

I do not get an error, but no data is returned.

Re: Infopath - Display data from a database

Clay Fox — Mon, 13 Oct 2008 05:49:02 GMT

That looks correct.

I would have a field that defaults to Username(), I am assuming that is the UserID.

So your formula looks correct.

Have you put UserID on the form to verify what is coming across? USername gets the local username not the domain account. That is one limitation that I prefer an AD web service for so that it works with VPN users and etc.

Re: Infopath - Display data from a database

Gary Burke — Mon, 13 Oct 2008 00:21:54 GMT

Another way of looking at what i am trying to do is as follows:-

Requestor Contact Number Contact Email

gary.burke 614 123 456 gary.burke@superamart.com

I get the Requestor's name from the function userName.

I now wish to merely display that user's contact number and email address. In an attempt to achieve this, i have inserted 2 expression boxes (1 for each field). The data for these 2 fields is found in a table in an Access database called tblUsers. The connection to this table is a secondary data source called Users.

I would think that the data could be retrieved by filtering as follows:-

@UserPhoneNumber[@UserID = POUserID]

POUserID is the data used for the Requestor

Re: Infopath - Display data from a database

Gary Burke — Sun, 12 Oct 2008 22:15:27 GMT

Thanks for the reply.

For 1

I did not see this as being a repeating table or section.

I have a field UserID whose contents is the function username. I wish to have a couple of other fields to display the user's telephone number and email address. These 2 pieces of data are found in the table tblUsers. How do I use conditional formatting as you have suggested ? Specifically what is the condition and/or function ?

For 2

I believe you are saying that in my table that contains the names of those who are authorised to "approve" a purchase order, I need a field named "Approver" and the data in that field would be a yes or no.

I then need to set the name of the approver and a corresponding "approved date" field as visible = false, unless the username = approver name in which case I would set the "approved date" as visible.

How do I send the approver a link programatically ?

How do I set the display fields as visible = true or visible = false ?

Re: Infopath - Display data from a database

Clay Fox — Fri, 10 Oct 2008 13:45:28 GMT

For 1.

I am assuming you would want a repeating table or section. Drag you r secondary onto the view to create it. Then add conditional formatting to hide all but where username matches username in your main data.

For 2.

Yes you would want to use the approve field to control whether the button to approve is enabled or visible, etc.Again this is done in conditional formatting.

I would recommend you just send the approver a link and they open a new form and query the data again, or make sure your form requeries each time it is opened so you do not have data that is not current being sent to people and potentially overwriting newer data.