InfoPath Dev

Hi there.

Am new to all this :-)

I have a template file created based on an MS Access database.

One of the controls on the form is populated with the function Username. 

I have a main datasource, being the table in the access database where I wish to store the data from this form.

I have a number of secondary data sources, being tables from the database used to populate combo boxes.

Q1.  I wish to display on the form some information about the user.  That information is stored in a secondary datasource.  Exactly how do I achieve this ?

Q2.  This form is a Purchase Order form.  Once it is completed, i wish to save the data in the database table (tblPurchase Orders), which i appear to be able to achieve successfully, however, i also wish to send the form, via email, to another person for them to "authorise", before the order is sent to the supplier.  How to I disable the authorisation section for all users, other then the recipient of the email - ie the person who needs to approve the order form ?  I currently do not have a field in my database in the tblUsers table that indicates a person can approve orders.  Do i need one ?

Regards Gary

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.

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 ?

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

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.

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.

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).

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

Thanks for your help

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.

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?

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

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.

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.