Jun 24, 2019 05:59 AM
Hello!
I have a formula for a “status field” that keeps tripping me up. I have tried multiple ways and can get parts of it to work, but not all. Undoubtedly I’m a comma or similar away from the solution, but by now I’ve tangled myself so far in my own head that I have to admit defeat and humbly ask for assistance.
I have the following columns that should be considered in the formula:
{End Date} = Formula field
{BiU} = Checkbox field
{Last Shared} = Date field
{Next Share} = Formula field
What I want to achieve is:
If {BiU} is NOT checked = ‘-’
If {BiU} is checked AND TODAY() is after {End Date} = :white_check_mark:
If {BiU} is checked AND TODAY() is BEFORE {Last Shared} and {End Date} = :ballot_box_with_check:
If {BiU} is checked AND TODAY() is AFTER {Last Shared}, but BEFORE {End Date} = :link:
Any and all help would be greatly appreciated.
Solved! Go to Solution.
Jul 01, 2019 09:46 AM
Oh, and I have a column that cleans up the dateformat of {Next Share} if that matters.
Can you do that already IN {Next Share}?
IF({Next Share (formula)}, DATETIME_FORMAT({Next Share (formula)}, 'YYYY-MM-DD'))
Jul 01, 2019 11:05 AM
Hi there,
Thank you so much!! This helped a lot. I think the following should work, it’s a bit complicated and hard to configure without seeing the base itself so let me know if any part doesn’t perform as it should.
IF({BiU}, IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", IF(IS_BEFORE(TODAY(), {Start}), "🔗", IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))),"🎟")), "-")
To answer your latest question about {Next Share}, yes, you can certainly do that within the formula. The following should do the trick (I also worked in the additional clause you said you were going to add):
IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 2, 'days')))), 'YYYY-MM-DD'))
Jul 01, 2019 11:31 AM
For some reason all results are now “-”
This one works almost perfectly except that I have two occurances of ERROR!
Both share the same base They are one day events that has {Start} and {End Date} 2019/07/07.
{Last Shared} 2019/07/01
{ :arrows_counterclockwise: Frequens}=‘Low’
{Tickets}=‘No’
Unfortunately I can’t share the base here which would have helped a lot.
Jul 01, 2019 11:37 AM
OMG! [Massive face palm] I doublechecked and have of course used English in one place and no wonder it didn’t work. Corrected it and now works. Will double check in a sec to see that everything works there.
The ERROR! though is not a translation error.
Jul 01, 2019 11:55 AM
There are some anomalies. Working on scrubbing base of telling data and translating so I can share with you and you can have a look around.
Jul 01, 2019 12:10 PM
How does one share a base (in the forum)?
Just by link or?
Airtable works like a spreadsheet but gives you the power of a database to organize anything. Sign up for free.
Jul 01, 2019 01:10 PM
You shared it! Thank you, this definitely helps to make better sense of what you’re looking for :slightly_smiling_face: The share link you posted does allow anyone to enter and change stuff though, so I would make sure that is a copy of your real base. For future reference, I typically share a “read-only” link and leave the “Allow users to copy data in this base” box checked. This allows someone to make a copy of the base rather than editing the one you have saved. But this is all a matter of preference!
I took a closer look and found an error I made in the formula I gave you for {Next Share} :frowning: If { :arrows_counterclockwise: Frequens} is “Low”, the previous formula would only work if the days between {Start} and {End} is greater than 7. If it was less than 7, it would error. The following changes it so that it adds 2 days to {Last Shared} (which I think is what you had before). If none of the parameters match, it will also add 2 days. Happy to tweak if needed.
Apologies for the confusion!
IF({Last Shared}, DATETIME_FORMAT(IF(AND(IS_BEFORE(TODAY(), {Start}), FIND("No", {Tickets}) = 0), DATEADD({Last Shared}, 21, 'days'), SWITCH({🔄 Frequens}, "Low", IF(DATETIME_DIFF({End},{Start}) > 7, DATEADD({Last Shared}, 7, 'days'), DATEADD({Last Shared}, 2, 'days')), "High", IF(DATETIME_DIFF({End}, {Start}) > 14, DATEADD({Last Shared}, 14, 'days'), DATEADD({Last Shared}, 7, 'days')), DATEADD({Last Shared}, 2, 'days'))), 'YYYY-MM-DD'))
Jul 01, 2019 01:30 PM
Thank you! Both for the fix and the explanaition of sharing bases and good practice there. Yes, it’s a copy of a base and I’ve stripped a bunch of tables, info etc, so no worries what happens to it in the futura. The original is safe.
There are two anomalies in the {BiU Share Status - Test Column} column.
I added a checkbox column called anomalies to easier be able to point them out.
The events have started, and are ongoing which should then be :ballot_box_with_check: , but they say :tickets:
It is ONLY supposed to say :tickets: if TODAY() is before {Start} (and {Tickets} is not ‘No’)
Edited to add: I can see that ‘ :link: ’ doesn’t show for those events that are within range and haven’t yet been shared / {Last Shared} (highlighted by the column {Not yet shared}). I need it to fill all eventualities and leave no blanks.
Jul 02, 2019 11:28 AM
Thank you Annica!
I think the following might work… apologies again for all the back and forth - this one is a bit complicated (as I’m sure you know :slightly_smiling_face: )
I rewrote the formula and I took a look at your base - I think some of the anomalies might be caused because there are differences between the {Next Share} field and the {Next Share - Test Column} field. The below formula as-is returned “ :link: ”, but when I changed {Next Share} to {Next Share - Test Column}, it returned “ :ballot_box_with_check: ” as expected.
The below formula will also return “ :link: ” for anything that has not yet been shared.
IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No",{Tickets}), IF(IS_BEFORE(TODAY(), DATEADD({Start}, -3, 'days')), "⏳", "🔗"), "🎟"), IF({Next Share}, IF(IS_BEFORE(TODAY(),{Next Share}),"☑️", "🔗")))), "-"), "🔗")
Jul 02, 2019 02:21 PM
No need for apologies, We’re far beyond the point of what I could achieve on my own. I am beyond greatful for all the help I’m getting.
I added one more column, {BiU Share Status - Latest Test Column} and changed to {Next Share - Test Column} like you said in the testbase. There is only one thing left now that I can see after manually checking each event. One event, that I renamed ‘ :exclamation: Random event name 25 :exclamation: ’, so it stands out from the others, should return a ‘ :hourglass_flowing_sand: ’, but now returns a ‘ :link: ’. All other are accurate.
Yes, that is because I like to test things in a new column before applying “permanently”. I know I’m not all that advanced in my skills, so I like to have the freedom to test around and not loose any step until I’ve actually gotten things to work. Maybe it’s a bit of a weird quirk. :slightly_smiling_face:
Jul 03, 2019 04:35 AM
I tested this formula on our “Live” base and noticed a few issues that I think are down to me being to up in formula and ending up being unclear. I’m so sorry this all has ended up far more complicated than a simple answer and solved.
:link: - If an event starts in the next three days OR if TODAY is on or after {Next Share} (Regardless of what value {Tickets} have).
:ballot_box_with_check: - If an event has started {Start} but TODAY() is before {End Date}
(Regardless of what value {Tickets} have).
:white_check_mark: - If TODAY() is after {End Date}
(Regardless of what value {Tickets} have).
:tickets: - If you can book tickets (so {Tickets}=‘any other value then No’) AND TODAY() is before {Start}
:heavy_minus_sign: - If {BiU}=‘No’
I hope I was clear and that it helps you understand what I’m after.
Jul 03, 2019 05:49 AM
Don’t be sorry! This is a complicated one to solve and without knowing much about the use case it makes it a bit more difficult.
Not a weird quirk - I totally do that too! :slightly_smiling_face: Just wanted to make sure we were both testing the formula against the same dates.
Your latest post was very clear - however it doesn’t take into account the “ :hourglass_flowing_sand: ” from the previous variations - let me know if that needs to be worked back in. The below will also return “ :link: ” if {Last Shared} is empty. I went through and checked each one and it looks like each result matches the parameters you laid out in your last post.
IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(), {Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}) = 0, "🎟")))), "➖"), "🔗")
Jul 03, 2019 07:11 AM
OMG - my brain.
:hourglass_flowing_sand: - if an event has {Tickets}=‘No’ and TODAY() is more than 3 days before {Start}
How could I forget. And I was trying to collect my thoughts and be clear. I knew I was confusing myself, but that’s extreme.
Jul 03, 2019 10:12 AM
Hahaha no worries at all!
So I added a new column to your practice base, {BiU Share Status - Latest and Greatest}, with the below formula. Fingers crossed that this is the winner! There were a couple places where I thought it might be wrong as it’s returning a :link: instead of :hourglass_flowing_sand: as it did in previous iterations, but then I realized that’s because the {Next Share} date for those records is today.
IF({Last Shared}, IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(IS_AFTER(TODAY(), {Start}), "☑️", IF(OR(IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days')), AND({Next Share - Test Column}, OR(IS_AFTER(TODAY(),{Next Share - Test Column}), IS_SAME(TODAY(), {Next Share - Test Column})))), "🔗", IF(FIND("No", {Tickets}), "⏳", "🎟")))), "➖"), "🔗")
Jul 03, 2019 11:37 AM
There are three instances where the {Ticket}=‘No’ and TODAY() is way more than three days before {Start} and should then be ‘ :hourglass_flowing_sand: ’, but show ‘ :link: ’. I’ve marked the instances in the column “Should be :hourglass_flowing_sand: ”.
If we can get that to work I’ll be ecstatic. I’ve spotted another thing in all this, but I’ll deal with that on a rainy day, or month (Grins). These are the functions that are needed now and what matters most.
You have no idea how insanely gratful I am for your invaluable help. Thank you!
Jul 05, 2019 06:10 AM
We’ll get this figured out! I think we’re just having some miscommunication on the dates.
For the three records you checked, the first two are showing :link: because TODAY() is after {Next Share}. The last one is showing :link: because {Last Shared} is blank.
Let me know what should change :slightly_smiling_face:
Jul 05, 2019 11:08 AM
I missed that two had a share date, which they shouldn’t yet so that’s strange, but oh well; I removed them. But anyway. Those three events checked should all show “ :hourglass_flowing_sand: ” because it’s more than three days until they start and there is no registration or pre-sale of tickets. When I removed the value in {Last Shared} they should have switched over to “ :hourglass_flowing_sand: ”, but didn’t.
Ok. I’ll try to be clear and not confuse myself so I end up confusing you as well. If I can achieve that then today is a good day. I’ll type out my rainy day, or month, plan as well so I have it clear in my head already now, but PLEASE don’t feel like you have to trouble yourself with it.
:heavy_minus_sign: = Event should not be shared at all (is not {BiU}=‘Yes’)
:hourglass_flowing_sand: = Not time to share yet. The event is more than three days away and there is no registration or pre-sale of tickets.
(Down the line I also want this to show if the event has {Tickets=‘value other then No’} and TODAY() is before {Tickets released}, but that truly is something for a rainy day, unless you truly feel like tackling that one as well. I sooo don’t expect you to.)
:tickets: = If you can book tickets / reserve seat and TODAY() is before {Start}. (This one is also one for a rainy day, see above as it should only show if TODAY is after {Tickets released}).
:link: = Share event. Two uses
The event has not yet been shared ({Last Shared}=BLANK()) and the {Start} of the event is either three days away or TODAY().
Today() is on or after {Next Share}.
:ballot_box_with_check: = The event has been shared ({Last Shared}), is still ongoing (after {Start}, but before {End Date}) and TODAY() is before {Next Share}.
:white_check_mark: = TODAY() is after {End Date}
I think I might have managed, but you have to be the judge of that. Clear? Clear-ish?
Jul 08, 2019 08:53 AM
Hi Annica!
Apologies for the delay in getting back to you… things have been crazy lately!
Thank you so much for the clear explanations. It definitely helps a ton. I want to make sure I have this right before giving you yet another formula that doesn’t work :frowning: I’m starting to feel like I’m not helping, lol.
No Share :heavy_minus_sign:
After Event :white_check_mark:
During Event
To Be Shared :link:
Shared :ballot_box_with_check:
More than 3 Days Before Event
No Tickets :hourglass_flowing_sand:
Tickets :tickets:
Less than 3 Days Before Event
To Be Shared :link:
Tickets :tickets:
If all that looks correct, I’ll get to work!
Jul 08, 2019 09:41 AM
That looks correct to me. Below is just me trying to be super clear to avoid confusion.
During event
:ballot_box_with_check: IF {Last Shared} AND before {Next Share}
Less than 3 Days Before Event
To Be Shared :link:
Tickets :tickets:
**On day of event / {Start} **
To Be Shared :link:
Tickets :link:
Jul 08, 2019 10:38 AM
Excellent - thank you again!
I’m very anxious to see if this works. I added another column to your base - {BiU Share Status - Newest}. I checked and it looks like all the parameters are met… let me know :slightly_smiling_face:
IF({BiU} = "Yes", IF(IS_AFTER(TODAY(), {End Date}), "✅", IF(OR(IS_SAME(TODAY(), {Start}), IS_AFTER(TODAY(), DATEADD({Start}, -3, 'days'))), IF({Last Shared}, IF(IS_AFTER(TODAY(), {Next Share - Test Column}), "🔗", "☑️"), "🔗"), IF(IS_BEFORE(TODAY(), {Start}), IF(FIND("No", {Tickets}), "⏳", "🎟")))), "➖")