InfoPath Dev

Ok guys, I am at it again, I am trying to figure out a program that I have not had any formal training in :) thats right, infopath!

Well my other form that I had been working on went over well with the team (thank you for all your help), now i am trying to make a new form.

what i currently have is an excel spreadsheet with list validation boxes and a lot of functions, it makes for a nice tool that allows me to put into a visually appealing tracker that my team uses throughout the month. This has worked for a year now.

So why fix something if it isnt broke? well, why not learn something new and how it can apply? or, My users are broke, and they have to play with things they shouldnt play with. So far throughout the year they have "broken" the tracker close to 30 times, from simply deleting a function, to corrupting the entire workbook.

What this tracker is for.

At the begining of each month i simply take a list of employees and "assign" them to an analyst. the employees are in a top down fashion, each month the analysts complete observations on the employees, when they do they choose "C" for complete on their dropdowns (right to left)if the employee cannot have an observation completed then we exception them "E" on the dropdown.

throughout the month the tracker counts the amount of C's and E's in each persons tab and totals them in various ways. I use a simple excel COUNTIF function to count the C's and E's. then your typical sums and averages to get my percentages. Again the tracker is simple but highly useful, it is a shareed workbook which allows us to have access to it at anytime regardless of who is using it at the time.

Like i said previously, sounds simple until someone breaks it, then i have to use up my time to repair it.

My solution has been to create a database with all of the options i need. At the begining of the month i receive a team list of around 200 employees, from there i place them into an excel spreadsheet which allows me to copy and paste the data in bulk. i have the spreadsheet linked to access, and a single append to query which adds the new agents into the table i have created.

from there i have my infopath form with a data source to the access database which all of the analysts have access to. The infopath for has a query built into it which allows them to only see the employees that have been assigned to them, this make the form run quicker and more appealing to the eyes. When they save their form it updates the access data base, that way if someone deleted or corrupts their personal copy of the form, all they have to do is open the template and save a new copy to their personal hard drives and re-run the query, thus getting an automatic backup.

this all works fine and dandy except my functions. Remember how I stated that excel has a COUNTIF function, well Infopath and Xpath do not have that function, and I have yet to locate a similar function via the howtos on this site and tutorials located on other Xpath and infopath sites.

I was wondering if any of you knew how i could count up a dropdown list from a repeating table if the values are text/string based.

In other words repeating_table_1 includes dropdown_list_1, Dropdown_list_1 has two selections, "C' and "E". how do i make a text field to count how many times "C" or "E" has been selected?

Hello
You can do so by using Count Function and also FilterData simultaneously
Insert a Text Box1 and Open its Properties
In the default value, Select Function <Count> and Insert Field(Drop Down)and Click on Filter data
Add a Filter if Field1(Drop down Field) is equal to Text C
In the same way add another Text Box2 and repeat the steps above and add a filter if Field 1 is equal to E

In this way you get Count of C in Text box1 and Count of E in Text box 2

Why not use the built in security features in Excel to prevent your users from accessing the features that you don't want them to break? You should be able to lock down the workbook in a few minutes after you have read the Excel Help file for all of the details.

I am not very fond of excels workbook protection (possibly because i dont know all of the ins and outs).

Another thing is that I can keep a running tab with my database, instead of making a new excel file each month the database will be easier for me to manage. I just wish i had the time to really learn access and make the data access pages and forms i really want to see, infopath seems to be a nice midium to work with. the learning curve with infopath for me a a bit quicker than excel and access. I am still very green when it comes to macros and programming.

I tried to filter the data and ran into a couple of issues.
You cannot make a filter within the text box, you can only filter data from the repeating table (unless im missing something), and when i filter the data from the repeating table it returns no information (since the selections have not been made), I want the analysts to be able to make the selections manually, then as they make their selections the field will total it out as needed. i will attach a screenshot of what i have so far, the pictures may make it easier to see what i am doing.

I don't know exactly how your schema looks, but what drgkreddy suggested should do the trick. You default value formulas for the two circled boxes woud look something like this:

count(xdXDocument:get-DOM()/my:Tracker/my:Agents/my:Agent[my:Ob1 = C])
+ count(xdXDocument:get-DOM()/my:Tracker/my:Agents/my:Agent[my:Ob2 = C])
+ count(xdXDocument:get-DOM()/my:Tracker/my:Agents/my:Agent[my:Ob3 = C])
+ count(xdXDocument:get-DOM()/my:Tracker/my:Agents/my:Agent[my:Ob4 = C])
+ count(xdXDocument:get-DOM()/my:Tracker/my:Agents/my:Agent[my:Ob5 = C])

Of course you will have to put in your own nodes to match your schema, but the same general idea should work for the E blocks too. I hope this helps!

Thanks Drgkreddy and Matt, thanks a ton for the help so far, but my pinch isnt over yet.

What i ended up finding was the filter option when you are choosing the field you want to count, this is why i was unable to follow DRG's assistance at first. I was able to confirm it with the Xpath formula that Matt added. Again, thanks.

The fields exist in two places once in the datafields and once in the query fields

Here is a copy/paste of my XPath formula

count(../../../dfs:dataFields/d:TeamList/@Ob1[. = "C"])
+ count(../../../dfs:dataFields/d:TeamList/@Ob2[. = "C"])
+ count(../../../dfs:dataFields/d:TeamList/@Ob3[. = "C"])
+ count(../../../dfs:dataFields/d:TeamList/@Ob4[. = "C"])
+ count(../../../dfs:dataFields/d:TeamList/@Ob5[. = "C"])

here is the Xpath function pasted from the formula window for the text box that calculates the info. "Update the value when the rest of the formula is recalculated" is also checked.

now that i have my formula built for the text box i can query the analyst and receive the correct list of employees, however when i attempt to change the boxes from E to C, it will only update information for the "OB1" fields. Anytime i make changes to the other fields it is not updating the formulas in the summing text box.

I have also tried placing only one item count in a text box with the same result.

Thanks for the screenshots these are incredibly helpful!

This is weird though, because I don't see anything wrong... Try the below XPath, and if that doesn't work you will want to try putting a rule on each of the drop-downs that updates the nodes accordingly each time a new value is selected in the drop-down.

count(../../..//@Ob1[. = "C"])
+ count(../../..//@Ob2[. = "C"])
+ count(../../..//@Ob3[. = "C"])
+ count(../../..//@Ob4[. = "C"])
+ count(../../..//@Ob5[. = "C"])

I really wish i had someone in my office who knew infopath, the extra eyes would really be helpfull.

you know how it goes, skip this, skip that. errors abound. spend the next however many hours trying to figure out why, and end up finding out that.

the values in the dropdown buttons were changed from "C" and "E" to "1" and "2", while the displayed values stayed the same :(

so needless to say i figured it out :)

thanks for the assistance both of you, you were both able to help me point out what i couldn't figure out for myself and what i was missing.