Automation to Link a Record based on a different fields input?

[zLow]

I have a table that I created to track resource allocation, and use that information to then report out to a graph in my interface to show based on the allocations, how much I would be expected to spend each month.

The setup for this is a bit of a workaround to solve for not being able to have multiple fields reported into a chart.  This is working how i need, but it is prone for error if I do not manually link the months in the linked field.

I'm trying to create an automation, or solve this in any way that doesnt require me updating both the linked field and the fields that determine if a resource is active or not.

My hope is that I could create an automation that would check the Month Fields, and if a Month is set to active, it would then enter that Month into the Linked Months field - this way i could just update the allocation fields, and my report would always be insync with what months should be linked -  See Image attached.

[TheTimeSavingCo · Accepted solution]

Hmm, I think I'd try:
1. Creating a formula field that outputs the list of months in the same format as the linked fields based on the values in the respective select fields
  - e.g. "Jan - 24", "Feb - 24" is "Active"
    - Output: "01 Jan - 24, 02 Feb - 24"
  - e.g. "Jan - 24" is "Active",  "Mar - 24" is "Active", Feb - 24" is "Inactive"
    - Output: "01 Jan - 24, 03 Mar - 24"
2. Creating an automation that'll trigger when the formula field is updated and its action would be to paste the value from that formula field into the linked field

The formula field's formula would be something like:

IF(
  {Jan - 24} = "Active",
  "01 Jan - 24"
) &
IF(
  {Feb - 24} = "Active",
  "02 Feb - 24"
) etc etc

Works but seems clunky, and would love to hear alternative ways to solve this!

[zLow]

I changed the formula to include commas after the Month strings, and it's working perfectly! Thank you so much for your help.  Posting the Formula change below

IF(

  {Jan - 24} = "Active",

  "01 Jan - 24" & ", "

) &

IF(

  {Feb - 24} = "Active",

  "02 Feb - 24" & ", "

) &

IF(

  {Mar - 24} = "Active",

  "03 Mar - 24" & ", "

) &

IF(

  {Apr - 24} = "Active",

  "04 Apr - 24" & ", "

)

[TheTimeSavingCo]

Ahh, nice one, sorry about that!