Comparing Two Pieces of Information to Post a Third in a Text Box

My first time posting a question and also very new to Retool!
I have a database of subscribers whose membership expires at the end of a year. I want to have a text box which shows if they are 'Past' or 'Current' members whenever they are selected. I know the year their membership expires as it is the first two characters of their membership number and I want to compare those characters to the current year to determine if the member is current or past members and display that a the text box. I can't seem to find a way to achieve this so any help would be welcome.

Hi @AlanT Welcome! Thanks for posting your question :sunglasses:

I would recommend writing some Javascript code to solve this case. You could use a Javascript transformer or you could put the code right in the text box config (You can put Javascript code inside of {{}})

Here's an example where I select a user from a table & then interpret whether they are past or current from their membership number. If you're not using a table to select the user, you'll just need to slightly modify the code for your specific case. You may also need to modify the Javascript for your specific data type. In my case, I am assuming the year is at least 2000:

{{("20"+table1.selectedRow.membership.toString().slice(0,2))- moment().format('YYYY') <0? 'Past' : 'Current'}}

In the code, I am using moment.js to get the current year. Then, I am using a ternary to return either "Past" or "Current" depending on the difference of the two years. For reference, moment.js is an external library that Retool includes by default

How will you handle 2023? Do memberships ever expire part way through the year, or is 2023 always considered Current?

Hi Tess, Thanks for the welcome and thanks for your support in solving my question. I have used your solution and I am really pleased with the result. I have also learnt the use of 'slice'! Fortunately our membership year ends on the 31st December which makes things easier.
Thanks again.

1 Like