Formula Help - Change a Multiple Choice Text field based on the day of the week.

  • 0
  • 1
  • Question
  • Updated 6 years ago
  • Answered
  • I have a Text - Multiple choice field: "Send Email" with a Yes/No
  • I want it to change automatically based on the day of the week without anyone required to edit the record(s)
I saw a formula that works off of a "Today" element but I am not sure how to modify that one to accomplish this.

Roughly I am looking for a statement like this, but I am not sure how to actually parse it:

If (
Dayofweek(5)= True then [Send Email] "Yes"
Dayofweek(5)= False then [Send Email] "No")


Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb

Posted 6 years ago

  • 0
  • 1
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
The formula for a formula text field called perhaps [Send Email if it's Friday] would be



IF(DayOfWeek(Today())=5,"Yes","No")

Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
If this works Mark, I may build something in your name.
Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
Thanks for the help Mark. It DOES work, but doesn't trigger the fact that the record has changed when it does. I was secretly looking for a way to trigger a Notification email without someone having to be in the record to do so. So while the field does change QuickBase doesn't acknowledge the "when a record is modified".
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
You would need to build a report and set up a Subscription. An email Notification will not suddenly wake up in the middle of the night and fire.

Maybe explain you bigger picture and there will be a solution .
Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
Subscription Emails do not have the functionality I would want. They can only send a static report.  In this situation I have feedback repository submitted by web form. I wanted an automated email to go out that would contain multiple reports within a single email, which is something you can do with the HTML functionality within a Notification Email.
I see two choices.
1. Present a user with a report of emails to be triggered and have them toggle a URL formula field on records, one by one to manually trigger the email.  For example if you had say 50 a week to go out, you would create a URL formula field that would 'jiggle" the record enough to trigger the email and then refresh the report.  With each jiggle, the report would refresh and get 1 record shorter.  So 50 clicks.

2. Contact a 3rd party developer like Juiced and use their "Triggers" tool to do this jiggling for you on an automated basis.
Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
Ok, since I'm not authorized to purchase any add ons, and the manual update aspect kinda defeats the purpose. Would something like this work? (I still suck at formatting If/Then)

If [Send Email],"Yes",[Date Modified](Now)

Basically forcing Quickbase to change the Date Modified?
Photo of Mark_Shnier

Mark_Shnier

  • 700 Points 500 badge 2x thumb
NO, it won't work.  Two reasons.
1. The [Date Modified]  is a system stamped field that is written in indelible ink and can't be changed except by actually modifying the record.

2. Email Notifications fire when the record is saved.  So unless the record is opened and saved, the system does not think about firing emails.  The email firing is actually integrated with the SAVE process (as an aside i was just reading something about performance issues and it mentioned that if you have a zillion email notifications to process, the actually save could have performance issues).

Now, I know that you are saying that the manual method defeats the purpose, but since you have zero budget, I need to ask the question as to how many would fire each week.  Say is only 100 and they take at worst 3 seconds to click a button and the report refreshes with 99 records, then click - 98 bottles of beer on the wall, click 97 bottles of beer.  So, 300 seconds = 5 minutes at worst, so at a clerical rate of pay of 33 cents a minute - say tops at $40,000 a year all in for a mindless weekly task that is a cost of $1.67 for a person who will probably be paid anyways if they click or not click for those 5 minutes.  But if you had 1,000 to fire, yes then it becomes impractical.
Photo of JessicaD_QB

JessicaD_QB

  • 90 Points 75 badge 2x thumb
Sorry to annoy Mark. Just frustrated with the limitations of the other email types other than Notification.  Thank you for all your help.