InfoPath Dev

Hi All,

I have a 2nd data connection from which I'm showing as a repeating table and doing a filter to only show 1 of the column items called [Parameters]

I then have a 2nd repeating table below that I want it to popuate itself based on the Parameters shown from the above 2nd data connection.

So, when the form runs, there might be 10 items pulled within the 2nd data connection.

APS

Transmission

APS

APS

Oil

Viscocity

and so on...

What I want is to then count how many times each item appears.

APS = 3

Transmission = 1

Oil = 1

Viscocity = 1

and so on...

I don't mind typing in the parameters as default values, like I say there's only 10 or so, but I'd like to be able to count how many of each.

Please help,

Mike

Hi Mike,

Are you looking for something like this? (see form attached - download, right click form, open in design mode, preview form, and enter values in the repeating table). If so, then you can use a formula similar to this (for each parameter):

count(my:group1/my:group2[my:field1 = "Transmission"])

Where group2 is the repeating table and field1 is the field or column which holds the parameters.

Your formula will be different since you will be using secondary data connections, but the format should be the same. Also, there are other approaches we can use to make it more dynamic (without entering hard coded values/parameters).

I hope this helps.

 Hello Don, thanks for your great reply and YES this is exactly what I'm trying to do.

However, I just can get this to work on my form. Up-until you replying, I had found a way of doing this, just playing around with rules and filters etc, I really don't know how I managed it but somehow managed to change a filter condition to become an expression?

looks something like this: . = current()/../my:field46  

Like I say, I have no idea where this came from but it does count the right values the only problem now is. Some of the values that come in from the secondary repeating table, they have merged data from SharePoint and some do have double or triple values. So I'd like to somehow change this line for one that says "contains". and I think I could nail it. 

Field46 by the way is the field I have setup in a repeating table and given it all the default values (hard coded) with APS, Transmission, Oil (parameters etc). Kinda doing a lookup on these values against the secondary repeating table values and doing a count yeah? 

One other thing Don, I will want to submit these count results back to SharePoint - I know in the past using calculated fields, they don't promote to SP, do they?

Thanks for your help, Its much appreciated

Mike  

Hello,

I'm glad to hear you were able to resolved the issue on your own. And yes you are correct, calculated fields cannot be promoted. A workaround you can do is turn the fields into text fields, right click on the field > select properties, and set them as read only. This way you will be able to promote the fields and prevent users from editing the values.

Hope this helps.

 Don, just before you go. Can you help me on how I can fit the line Contains into the statement I have working?

. = current()/../myfield46

I need it to say contains... so if the secondary repeating table has two values merged when its brought in, I can count these where as it contains APS or contains Transmission etc. At the minute its only counting the absolute values where only one value is found. 

Any ideas Don? 

. = current()contains/../myfield46 

Thanks

Mike 

Are you trying to insert the word "Contains" within the formula? Concatenating the fields and/or hard coded words using the function concat() should work. If it doesn't kindly attach a copy of your form so I would understand what isn't working for you.

Regards, 

 How do you upload a file to this site? I don't seem to have the option?

Mike 

 Ha! Think I've found it up-loads -

Hello Don, you still there?

Please see the attached, its your sample with adjustments -  When you preview the form now, the top repeating table is say my secondary table. You'll notice the last field has two parameters entered. APS & APS. I've just realised this would be unlikely but could contain APS & OIL or APS & Transmission. This is because the data coming in from SharePoint has been merged and could contain lots of the parameters all of them in fact on bad day.

You'll now notice the bottom repeating table has my formula count on current count(field1[. = current()/../field4]) - as you see, its working.. but only on 1 field item events not 2.

This is where I need to add "Contains" perhaps something like this: count(field1(contains[. = current()/../field4]) 

 Thanks,

Mike  

Hi Mike,

Please try this formula instead.

count(../../../my:group1/my:group2/my:field1[contains(., current()/../my:field4)])

Let me know if it works.

Regards,  

Hello Don, Thanks for staying with me...

Sorry, I can't get this to work either?

Here's the real line im using - count(xdXDocument:GetDOM("Retrieve Weekly Quality Data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW/d:Parameter[contains(., current()../my:field46)])

Not sure if you can see or do anything with this or see anything wrong?

When I try to add contains within the expression builder within the filter system, its doesn't like brackets - so I have to click "edit the xpath advanced to be able to insert extra text.

Did you look at the sample form I attached back previous? did you get it to work in there?

Thanks

Mike 

Hi Mike,

I was able to check the updated sample form you have attached and the updated formula I have provided you with is working, but it will need some changes if you are working with secondary data connections. Could you try this one instead:

count(xdXDocument:GetDOM("Retrieve Weekly Quality Data")/dfs:myFields/dfs:dataFields/d:SharePointListItem_RW[contains(d:Parameter, current()/../my:field46)]) 

Regards,

Nailed it Don!!

All working - can't thank you enough.

Really, really chuffed Don...

Massive thanks and thanks for sticking with me

best wishing

Mike