Cognito Forms: Query across repeating sections by ItemNumber

Question

Is there a way to lookup by ItemNumber across repeating section?

This can really help the design when you have large sections that are bound by the number of items but should be logically separate in the design and work-flow. For example, sections get filled out at different times in the work-flow. Think of it as a one-to-one join.

Design:

Enter two items into the form:

The next section should show the items separate, however, it lists both items in both fields:

See: "Referencing repeating data" here: https://www.cognitoforms.com/support/51/calculations/repeating-sectionstables

=Form.RepeatingSection1.Where(ItemNumber = ItemNumber).Select(UserInput)

This could be a cognito limitation / bug. I can tell that the first ItemNumber in the Where function definitely references any field in RepeatingSection1 because of the drop-down autocomplete list so we would expect that the the second ItemNumber would properly reference the current section.

Answer 1

The reason this isn't working how you expect it to is because the variable scope changes depending on context. When you reference Form.MyRepeatingSection.Where(ItemNumber = 8) it is impossible to get and compare to the ItemNumber of the repeating section element that contains the calculation. You may have noticed that when you type in the calculation box, it can autocomplete certain names, that autocomplete list is essentially the only thing you have access to.

To make a one to one repeating section copy using calculations, you need to use a bit of trickery. The MyRepeatingSection.Select(MyFeild) will return every value for that field in your section. What we need to do is first get all the values, convert that list of values into a string, split the string at the commas, then reference a specific element in that array. Here is an example

=String.Copy(Form.MyRepeatingSection.Select(MyField)).Split(",")[Int32.Parse(ItemNumber)-1]