Hi all,
I have a SharePoint list with about 1,000 "business requirements" that are categorized by Functional Organization (HR, Engineering, Manufacturing, Design, etc) and by Project Tollgate (Initiate, Design, Plan, Execute, etc).
I need the SharePoint list to filter based on the Project Tollgate -- no problem there, I have a rule which sets the Query field -> Tollgate field to equal the form's Tollgate field value, then runs the query on the data source.
So now I have a subset of the business requirements that all relate to "Initiate" for example across each Functional Organization. I also have one Repeating Section for each Functional Org (11 repeating sections). I want to take all of the results' IDs where Functional Org = "HR" and put it in the Repeating Section group called "HR", all results' IDs where Functional Org = "Engineering" goes into the Repeating Section group called "Engineering", and so on. I have a RequirementID field in each Repeating Section with a default value of SharePointListItem_RW[count(current()/../preceding-sibling::HR) + 1]/ID (where HR is the Business Function and repeating section group's name -- so HR, Engineering, Manufacturing, etc). The default value works -- as I add rows to the repeating section it automatically populates with the next ID in the data source's results. But since I haven't filtered it further, it's only filtered where Tollgate = Initiate and I need the IDs going into the HR repeating section group to only be where Tollgate=Initiate and FunctionalOrg = "HR"
And that's where I'm stuck - I can't figure out how to "dynamically" filter the results. I had the bright idea to try filtering that default value further ((I'm paraphrasing the following so this is not the correct syntax)) -- SharePointListItem_RW[count(current()/../preceding-sibling::HR) + 1]/ID[my:SharePointList/my:FunctionalOrg = "HR"] but that wouldn't return any results at all.
My work around was to create 11 data sources with a rule on form load that sets the Query field -> FunctionalOrg equal to the numeric value of each functional org... Then when the tollgate field updates, the rule sets the Query field -> Tollgate to the form's tollgate value to all 11 data sources, queries all 11 data sources, and tada now I have the data source "split" into multiple repeating sections.
But this is cumbersome because it's querying the same SharePoint list 11 times different sets of data.
there has to be a better way to do this? Either through rules or VB code. Any ideas?