Comparing Select fields in one table

[RPA]

Hi! Is it possible to compare single and multi-select fields across columns in one table? Would a formula be able to do this?

I have a single select field listing the launch month for content. Then there are 2 lookup fields with the launch months for connected content (each with multiple months selected). Ideally, I want to compare the single select field to the 2 lookup fields and output anything that is after the single select field month. Would these fields need to be dates rather than single or multi-select fields for this to work? If so, is there a way for multiple dates to exist in one field? Or is there another way to do this?

Thank you!

[TheTimeSavingCo]

Hi RPA, could you include some screenshots of the columns and data you’d like to compare and the output you’d like after the comparison?

Would love to help but could use more details!

[RPA]

Thanks @Adam_TheTimeSavingCo ! Here’s a screenshot of a small piece of the current data.

It’s helpful to see the months visually like this for other purposes. I think I could use a formula to make a date field from the first column, which is single select, if a date field would be helpful. (We don’t want to enter the dates twice and still want the visual version). I’m hoping for a way to check the other 2 columns against the first column and return a result if there is a month in either of them that’s after the month in the first column.

For example, when September 2022 is in the first column, I’d want to note whether either of the other columns has October 2022, November 2022, etc.

My initial thought is that these likely need to be date fields to use a before/after formula, but how can I have multiple dates in one field? Is there any other way to denote order of items and test for before/after?

[TheTimeSavingCo · Accepted solution]

Thanks for the screenshot!

Hmm, I think if you’re looking to get the exact select options returned (e.g. your first column has November 2021 and your second column has October 2021, December 2021, January 2022, and you want December 2021, January 2022 to be returned) then the only way to do this would be via a script I think

If you’re looking to just get some text back saying “Column 2 includes month years that are after the date” you could potentially hardcode a super long formula field that would work just fine

It would first check whether Column 2 and Column 3 contained any options that had years after the year set in Column 1, and then it would check whether any of the options included months that were after the month set in Column 1, resulting in output like this:

Base link here

To view the formulas, you can duplicate the base by clicking the title of the base at the top of the screen, then the three horizontal dots on the right, and then the “Duplicate Base” button

[RPA]

Thank you!! This worked perfectly.