InfoPath Dev

 I have to SP lists

List 1: BU List
BU#1 BUName1
BU#2 BUName2
BU#3 BUName3

List 2: REC List
BU#1 REC#1 RECName1
BU#1 REC#2 RECName2
BU#3 REC#3 RECName3
BU#3 REC#4 RECName4
BU#3 REC#5 RECName5

I have an IP form with connections to BU List & REC List

I would like to have a repeating table to display BU List with 1 additional column 'Count of REC' that displays the count of REC looking at REC List

Desired outcome:

 

Result of Repeating Table 3 columns

Count of  REC        BU#            BUName
2                            BU#1          BUName1
0                            BU#2          BUName2
3                            BU#3          BUName3

My current approach:

Drag the connection of BU List out to the form as Repeating Table, insert a column and then a textfield. My intention is to Count BEList REC where 'REC BU#' = current row 'BU#' but I wasn't able to get anywhere.

Please advice! 

So - can you paste the full XPath of an attempt at the formula in a reply? Create the formula with the filter (selecting the repeating row in List 2, select filter data, select the BU # is equal to the BU # in List 1) then select the Edit XPath checkbox and paste the result in a reply & I can probably give you a hand. :)

 Here is the XPath on the Count of Rec field: count(xdXDocument:GetDOM("RECList")/data-set/record/RECNum[../BUNum = xdXDocument:GetDOM("BUList")/data-set/record/BUNum])

 The result:

 

Image of result: https://drive.google.com/file/d/0BxlPX1FIi-PDeFRPQWEwSzgtZXM/view 

Here is the xsn file: https://drive.google.com/open?id=0BxlPX1FIi-PDd0dPZURUT0h3OFE. I couldn't upload the file here. Sorry.

You won't want to put a main data source field in your repeating secondary data table - you should use a calculated value instead. I've updated your sample and attached it to this reply - save the linked file locally, right click and select design to open in design mode, then preview. The XPath should be: count(xdXDocument:GetDOM("RECList")/data-set/record/RECNum[../BUNum = current()/BUNum])

So goooooddd. Thank you for your time & expertise.