InfoPath Dev

Hi, I am pretty new to InfoPath and have been working on a new project which tracks assessments. I have 3 SharePoint lists: 1. Assessment - this is where all the data is written to from my InfoPath form (actually a form library). It contains the 'Assessment ID' (from list '3'), the 'Country' (from list '2') and a completion date. 2. Country - this is a list which contains countries and their corresponding regions etc. 3. Assessment Type - this is a list which contains all of the assessments which are supposed to be carried out every month Essentially what I am trying to achieve is to create a grid by which the user can search a given month / country and it will display all of the possible assessments (from list '3') and then show which ones have been completed (from list '1') and which have not by checking against the assessment list (list '1'). The issue I am having is that I can't seem to find any way in which to join the two secondary data connections I have (lists '2' and '3') and the primary one (list '1') and display them in the same table. I am looking for it to look something like this: Search Date (Month / Year): March 2016 Search Country: All ---Results Table--- Country|Assessment ID| Date A | 1 | March 1st 2016 A | 2 | Incomplete A | 3 | March 2nd 2016 B | 1 | Incomplete B | 2 | Incomplete B | 3 | March 10th 2016 Does anyone know how this can be achieved? I do not have SharePoint Designer (company has it locked down) and can't add custom code (again locked by company) - so am limited to what InfoPath and SharePoint can do as standard... Thanks!

I wonder if you could use REST to join the lists - https://scottcurrier.wordpress.com/2010/01/02/rest-and-sharepoint-2010-quick-start-guide-multiple-columns-expand-and-filter-almost-a-join/

Hi Hilary, thanks for the suggestion. I am unfamiliar with REST - is it almost like creating an SQL query which can link multiple lists into the same data set? How exactly does it work? As I mentioned above I am pretty new to InfoPath and the more complex functions of SharePoint so I need a little coaxing with this stuff. Thanks for the help in advance!

Maybe you could start with this tutorial, and then once you have an idea of how REST in general works I could help you with the syntax? 

The tutorial you provided was great. I have been able to get REST working in InfoPath and outputting values from a single list. What syntax should I be using to join the lists? I basically need the data from list 2 merged with list 3 so that if I had only 1 country in list 2 (Country A) and two assessments in list 3 (Assessments 1 and 2), the result would give me two records = "Country - A Assessment 1" and "Country A - Assessment 2". From that step I then need to join that result with list 1 to show if there is an entry for the two records created from the join.

I'm so sorry - I clearly didn't read your initial post well enough. The REST option that would let us get data from a related list relies on the list having a lookup so we can use $expand. Which is not what you have going on here! I really can't think of a non-code solution for what you want to do. Would it be an awful pain to add a Country column to your Assessments list, allow multiple selections, and select all countries for each assessment? Or the reverse - add assessments to countries? Then in your data connection you'd have a nested repeating Value node that you could use for the repeating table....

Hilary - I have done that now for linking countries to assessments (lists 2 and 3) but now I still need a way to see which of the assessments have been completed (in list 1) compared to the combined lists 2 and 3. So my combined list now gives me, "Country_A - Assessment 1", "Country_A - Assessment 2" etc. so now I need to see if there is a "Country_A - Assessment 1" entry which exists in list 1. What kind of code would be required for this? I can check if it is possible to use it with my installation. Thanks!

Well, I think this part you don't need code for - I think you should be able to just use a calculated value in a table bound to your combined list - can you attach a copy of your form thus far to a reply under the Options tab?

Hi Hilary, unfortunately I cannot post the form here. However, I have managed to get the rows in my repeating table matching up to the 'country' component of my assessments list (1) using a filter on the secondary data set of list 1. The challenge I am now having is that when I am filtering against a distinct Assessment ID, it only seems to match against the first in the list from list 1. For example, list 1 has 2 Assessments which have taken place in Country A, Assessment 1 and Assessment 3. When I then search my new combined list (2 and 3) for Country A, it lists all of the possible Assessments - for example 1, 2 and 3. So what I want the calculated field to show is that it can find a value for Assessments 1 and 3 and show the ID# but a blank for Assessment 2. At the minute, it is only showing me Assessment 1 as a match...as though it is filtering list 1 based on the values for the first entry in the combined list only for each subsequent calculated field. I appreciate that this is perhaps hard to follow without seeing the form or having a way for me to add tables in this editor...but any help is appreciated! Thanks

Hilary - I just saw an earlier post from you regarding the 'current()' method and this has solved my issue! I want to say a huge thanks for the help you have provided for this!! Problem solved and everything is working as expected now!

Great news, glad you have it working - and you got a chance to learn about REST. :)

Hilary - I had only tested the form locally when I posted my last message. I have tried loading to SharePoint and am now getting an error of "Could not verify the form template on the specified server." and this is making the form not load to SharePoint. The error seems to be related to the code I have written in the button I added which checks if an assessment is taking place for the selected country/year/month vs. the schedule (a repeating table of the combined data from lists 2 and 3). if I remove the button, the form will upload (with some warnings about "filter expression is on external data and cannot be validated" and browser optimizations about "control sends data to server when value changes". The code I have in the button is as follows: count(xdXDocument:GetDOM("CED_Reviews")/dfs:myFields//dfs:dataFields/d:SharePointListItem_RW/descendant-or-self::node()/d:Review_x0020_ID[contains(., current()/d:Test_x0020_ID) and ../d:Country = current()/d:Country and translate(substring(../d:Review_x0020_Target_x0020_Date, 0, 11), "-", "") >= translate(xdXDocument:get-DOM()/my:myFields/my:SchedulingSearch/my:SS_Date, "-", "") and translate(substring(../d:Review_x0020_Target_x0020_Date, 0, 11), "-", "") 0 How can I make it so that this works? Having the code in a calculated field rather than a button was also giving the same issue.

 What is this part:

translate(substring(../d:Review_x0020_Target_x0020_Date, 0, 11), "-", "") 0 

Should that be = 0?