InfoPath Dev

I have created a timesheet entry form in infopath, which sends the information back to an access database.  I have two tables setup in my mainsource data connection.  One table is Timesheet which has an auto generate number for each timesheet entry.  This table also has the employee info, work crew, work date, approved check box, approved by, and approved date.  The second table is called entry which basically list what the worker did that day broken out by time segments.

Alot of this will be filled out while in the field and submitted when they come back to the office at the end of the day.  There will be times when they get called out during the night and they will need to add after hour events to the timesheet and resubmit it. 

What I need to do is find a way to disallow a resubmit if the timesheet has already been approved by the supervisor.  The supervisor will be checking the entrys in Access and marking them as approved.

For security reasons I thought about creating a table called Timesheet_approval which would hold the timesheet number, approved check box, approved by, and date approved.  That way I could set security in the database to keep employees from marking a timesheet unapproved and adding time to it.  My concern with that was I didn't know how to do the same steps as above with another table involved.

Also if there are any ideas of a better way of doing this I am all ears. 

Thanks

Jeff

Hi, Jeff:

Have you considered using User Roles? You could set up a role for supervisors, and add some controls to the timecard that would be hidden if a person wasn't a member of that group. You could create another view of the timecard that was read-only, and add an open rule that would switch to the read only view if the timecard was marked approved and the person wasn't part of the Supervisor User Role. Does that make sense?

Now, if you want to continue having the supervisor check Access instead to mark entries approved, you could use the same approach. You don't have to have a control on the form for every field in your data source. So, for example, if your Access table has a column called 'approved', and the supervisor sets the value to 'true', your form could have the same type of open rule -- if the approved column is true, open in read only view.

Let me know if you need more details, okay?

I placed the approved field on the top of the form and set an open rule that if approved is true open in read only view.  I then entered a test form which submitted to the database with no problems.  I then went into the database and set the approved field to true.  I then reopened the saved infopath form and found that it still showed the approved value as false.  Is there a rule I need to run on open to update the approved field?

 Thanks for you easy to follow explaination.

Well, I'm going to guess that your form was created off your database. If that's the case, it isn't set to query on open.

I did a simple test. I created a form off a database, and then, I created an open rule that sets the data source query field for ID to the same value as the data source data field for ID. Then I added an action to run my main query. What this does is 'refresh' the form's data on open.... meaning I could go to my db and set a boolean to 'true' and that would show in the form.

Does that make sense?

Oh, and you'll want that ruleset to run *before*  the rule to switch views....

I completely understand the logic.  now my problem is I don't understand how to implement it.  Do I need to bind the approved field on the form to approved in the query data source or the table source?  I guess my next question is I don't know what you mean by run my main query?  I am really new to info path and just starting to learn the basics. 

Well, everyone here was new at some point!

You don't have to have a control on the form that is bound to the approved field in the data source, as long as the field is in your data source.

Is your form built off the table? That is, when you designed your form, did you select to design it off a database?

If so, your main data source for your form will have two groups in it -- query fields and data fields. If you look in those groups, you'll see two sets of fields with the same names. The query fields are the way we chose which data to return to the form. The data fields display the data. So, the fields on your form, probably intended for display and editing of data (or entering new data) are bound to the data fields.

I'm guessing that you have a field in your table that you are using for Id. I am suggesting you create a new open rule, to run before your switch views rule, that has the following actions:

1. sets the query Id field to be equal to the data Id field
2. runs a query using the main connection (in the rule action, select Query using data connection and in the dropdown, select Main Connection)

This will cause the form to query the database every time it is opened. If the timecard entry already exists (i.e., it has an Id), the data for that timecard will be refreshed in the form. This means that if the 'approved' field has changed, the form will have that change.

I'm attaching a simple (ugly!) template that I used when I set the rule to test, just so you can look at the rule and see what I mean. You'll not be able to test using my form, of course, because you don't have access to my local SQL server. But it may help clarify what I'm saying. Right click the link for the attachment and save it, and open it in design mode.

Thanks so much for your help Hilary, I am actually making some progress YEAH.  The only problem I have right now is when I open a blank form it is running the query and grabbing the ID of the first record in the database.  I'm sure there is a property field or another rule I can run to only run this query if its not a new form.  I will search the forum and see if I can figure this out before you have a chance to reply.

 Again Thanks

Jeff

Well, if you have a value in the ID field (data fields), then the form is not new. You could use that for a condition -- if ID field is not blank.....

I used the XDocument.IsNew = false expression and that seemed to work.   ^^^^^^^ as always I'm overlooking the obvious easy answer... lol

Ok not sure how I screwed up the previous post but lets try again.

I attached a picture of my data source.  I still cannot get the is_approved field to show true when its been updated in the database.  The entry table and approved table are linked to the timesheet table via the timesheet_id.

I have two rules set to run when the form opens.  Rule1 does the following.

1. checks to see if the XDocument.IsNew = False.
2. sets the q:tbl_Timesheet :timesheet_id to the value of d:tbl_Timesheet :timesheet_id
3. then runs the query on the main data source.

Can all of these be run in one rule or do I need to setup rules for each step.

Rule 2

1. Checks to see if tbl_Timesheet_Approval :is_approved is set to "True"
2. If "True then it opens form in view: Read Only

I don't know if this will work or not since I cant get past the is_approved update problem

Thanks again, if there is anything else you need that I left out let me know.

Jeff  (I don't know how I got this picture in the post, and I don't know how to get rid of it.)

Hi, Jeff:

Is Item 1 in rule 1 an action? Or a condition? I would think it would be a condition -- you don't want action #3 to run unless item 1 is true, right?

So, Rule 1, I think, should be based on the condition that the document is not new, and have 2 actions: 1) set the query field and 2) run the query

Rule 2 should have a condition to see if the approved field is set to true and an action to open the form in read only.

Is this how they are currently set up? as conditions on rules?

yes the 1st step in each rule is a condition.

Is rule 1 running? Throw a dialog box in there, see if it pops up....

Or show your query field on your form somewhere so you can see if it is being populated....

sitting with a sheepish grin..... nope rule 1 wasn't running.  I changed the condition to look and see if timesheet_id is not blank for the condition.  works like a charm.  I guess my trying to look smart with a fancy command didn't work out too well.

Thanks Hilary, you rule

Now on to finding my next challenge... LOL 

Jeff