Dynamic Select

[AGilbert]

The attached solution is how I understand the field info tool can be used to dynamically select certain fields and this week's challenge has me wondering if there is another way. My #419 Submission. 

To simplify the question in 419 I mocked a March Madness Inspired question. The data table has the four #1 seed's scores for completed rounds of the Tournament. The goal is to view each team's most recent and prior round score regardless of how many rounds are added to the table. 

To illustrate, the attached workflow shows my understanding of how to use the field info tool to isolate the identified fields.

I've been trying to implement these expressions in a dynamic select but am unable to do so. The syntax below isn't exactly correct (otherwise I wouldn't be asking this question) but I hope they get the point across.

1. contains([_CurrentField_], m)
   1. where m is the max value of a field
   2. In this example it could represent the highest round number
2. [fieldnumber] IN range(n-1, n)
   1. where n is the count of fields 
   2. In this example the expression would always return the last two columns of a frame. 

Any ideas/thoughts/helpful nudges are welcome!

Thanks in advance!

-AG

[Watermark · Accepted solution]

AG, 

I posted in the challenge itself, but here's a version that creates the dynamic update you're looking for. 

 

[binuacs · Accepted solution]

@AGilbert another option with a batch macro

[AGilbert]

Thanks @Watermark!

What happens when you add the 2024 data below to your workflow? My goal is to have the field headers update dynamically on the existence of new data w/o having to update references in expressions. 

Spoiler

For example, output table for Check #2 in my "test" workflow (Challenge 419) will automatically show calculations for the most recent year included. See "Fundraising Goals 2025" below which is labeled as such from the upstream dynamic rename and without explicit rebuilding of expressions. I hope this clarifies the goal for Check #1.

[AGilbert]

@binuacs 

Oh, a batch macro! How timely. I just finished my first macro weekly challenge (#14) and was about to go figure out the control parameter.

Nice, so the dynamic select expression is updated with the action tool similar to how a filter tool would be. That's cool. It seems my missing link was the interface tool suite.

Thanks!

[Watermark]

It'll happen automatically. 

Instead of 23 being 'current', 24' would become current.  Regardless of what years your data is, it'll identify most recent as 'current'. 

[AGilbert]

Apologies, I didn't see the second workflow. Now I see your formula tool and its expressions on the max value. Until today I hadn't explored using metadata in this way.

Thanks for your help @Watermark!

[Watermark]

BTW - if you're hard core set on including the years in your naming dynamic, quick couple of tools can address that, see tools in yellow.