Enable Object Oriented List Management in Excel

[kayleighhoude]

Description:

At present, working in Excel does not allow list structuring (a la Grasshopper) when you apply an engine method to a list of objects nested within a cell it does not return a value (much less a list of values, summed.) The issue below details this for dealing with a list of pipes - we have no desire to expand that list out into its thousand pipes, we would like the sum of the query for solid volume to be enabled in a single cell.

[adecler]

It might be worth starting this discussion with what Excel is actual capable of doing independently of the BHoM.

Trying to reproduce the entire lacing system from Grasshopper in Excel seems to me like a very dangerous path. So I would prefer to frame this feature request within the native capabilities of Excel as much as possible with only some potential minor improvements here and there to get what we want. If a full lacing capability "a la Grasshopper" is required, then I would recommend to use the right tool for the job and move to Grasshopper.

Staying within the capabilities of Excel also means it is easier to avoid the slippery slope toward full lacing capability requests by keeping the user's expectations within the realm of native Excel.

With that lengthy intro out of the way, let's look at how to achieve the simple case mentioned in the issue description:

As we can see in column C, Excel can natively apply repetitively the same function on a list of cells. We have two issues though:

• the result will be be expanded in multiple cells (ex: column C)
• the input needs to come from a list of cells otherwise it fails (ex: column F)

Thankfully we have two BHoM methods to help with that: Condense and Expand. See columns G, H, and I for a possible scenarios matching the issue description.

We do have one issue to solve though:

BHoM formulas are not fully behaving like native formulas and cannot be applied repetitively on a list of cells.

@kayleighhoude , would you consider that Condense and Expand resolve your issue as long as we fix the problem above ?

[kayleighhoude]

Summary of conversation with @al-fisher and @FraserGreenroyd: -Fix bug raised above with new issue -Implement new list handling feature (inputs: objects, method, boolean (list of outputs or summed output))

@adecler

[FraserGreenroyd]

-Implement new list handling feature (inputs: objects, method, boolean (list of outputs or summed output))

My understanding of the discussion was to have a larger, continued discussion on this item, rather than an agreement to implement a new feature directly from the conversation.

[al-fisher]

Yes - good call both - I will raise a new bug issue now, capturing issue about repetitively applying BHoM formulas.

Then in this issue - let's create a clear description/mock up of what a feature for applying a method iteratively to a list of objects will look like for then discussion and agreement. The screen shots above are helpful so we can all visually agree what the use of features actually looks like - suggest we produce a similar "mock up" for the new feature.

Is worth also capturing from the call, the need for this feature as discussed with @kayleighhoude @FraserGreenroyd - as often times a large list of objects is pulled from another host software (i.e. 1000s of objects) - and a simple method is wanted to be applied in bulk. The idea is to facilitate querying/applying a method to a list without the need to Expand the list out - which I think your second bullet point above suggests the current method (bug not withstanding) would still require. Is that right @adecler?

[al-fisher]

Also then capturing one idea discussed previously, as well as on call, was to create a new BHoM Excel Formula - something like:

=BHoM.ApplyMethodToList(objectList, method, [sum]) with the sum an optional bool to automatically sum resulting list if numerical result.

If the method signature is not a single object and a single return value then we would return error. If the method does not return a numerical result and sum is true - then also return error.

Following @adecler's comments above about native limitations of Excel when handling list - this approach of creating a new formula is I think potentially an elegant mechanism in "capping" the feature at only explicitly "Applying Methods to a List of Objects" i.e. we do not have to address the more complex methods with multiple list arguments and lacing etc. etc. - where arguably we could be saying, handle differently/use a different UI.

Equally a Formula approach like this will make sure users are explicitly in control of the feature - similar to our Explode, Expand etc. formulas.

[al-fisher]

Raised new issue https://github.com/BHoM/Excel_Toolkit/issues/336 @adecler following call with @kayleighhoude @FraserGreenroyd

[al-fisher]

Capturing answer to my own question after good chat with @adecler

The idea is to facilitate querying/applying a method to a list without the need to Expand the list out - which I think your second bullet point above suggests the current method (bug not withstanding) would still require. Is that right @adecler?

The answer is "yes" you need to Expand - however you can Expand and Condense within a single nested in cell formula!

See column I in @adecler's first image above

i.e. =BHoM.Condense(CHOSEN_FORMULA_HERE(BHoM.Expand(E2)))