Discussions

 View Only
  • 1.  Formula Field - If this, then that; otherwise allow text entry

    Posted 11-22-2022 00:15
    Hi everyone,

    Just finished building a new tool for my team, integrating reporting to contacts tables to allow staff to look up contact records rather than repeatedly inputting the same data.

    However, our contacts table uses email addresses as the record ID.  When it was established, it made a lot of sense to do that to prevent duplicate entries, among other things.  We were only using that table to send emails once  a month, and if we didn't know an email address, then that contact wasn't added to that table.

    I've now been asked - what happens if we don't have an email address (the newest system requires the input of an email address).  My thought is to create a way for an automatic 'fake' email address to be created based on the community of interaction + '@noemail.com' or something to that effect to populate the field; but if the box is unchecked - to allow for regular entry of an email address.

    So - I wanted to see if this were possible:

    Have a checkbox - you tick if you "don't" have an email address for a new contact
    The email address field - has an if/then formula like this:
    IF([No_Address]=1,[community]+"@noemail.com",)

    Is this possible?  I've not seen any formula queries that specify that if the result is other, to allow for field input / data entry to still occur.

    Thank you for your thoughts!

    Jen

    ------------------------------
    Jennifer Juhasz
    FamilySmart
    BC, Canada
    ------------------------------


  • 2.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-22-2022 08:00
    OK, this will be no problem. The key field of your table is the email address and that must remain as a data entry field. Key fields must be data entry field and not formula field.

    So you're on the right track, make a formula field as you suggest, and then use a Form Rule that will say

    When the email address is blank and that boxes checked than the action will be to copy the value of the formula field into your email address field.


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 3.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-22-2022 09:50
    Okay - will give this a try this morning; so the formula field I create can also include the action to populate the record ID field with the 'fake' email address.  Can I do all of this with just a formula checkbox then?

    And thank you!!! Your advice is always so helpful!

    ------------------------------
    Jennifer Juhasz
    FamilySmart
    BC, Canada
    ------------------------------



  • 4.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-22-2022 09:59
    Just to get our Terminology straight, the key field of your table is the email field. Every table still has a Record ID field even if it is not set as still being the key field.  

    The formula will do the work of what you want to go in that field if the user does not have an email address, but the work of putting the value from the formula field into the email field will be done by a Form Rule.

    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 5.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-23-2022 09:53
    Hi Mark,

    I thought I was on the right path, but getting an error.

    I've created my checkbox.
    Created a 'Formula Email Addess' field (and alternatively tried both a Formula Text and Formula Rich Text field), but the following formula keeps generating a syntax error:

    If([No Email Address for Contact],true,[Staff Member - Community]&"@noemail.com")

    If I say ,true, - it says - expecting bool but found text
    If I say ,1, - it says - expecting number but found text

    I'm puzzled, why this isn't working.

    If I understand your guidance correctly, once I can get this field to populate, then I'll use the form rules to populate the 'Email Address' field with this content.  Is that right?

    Thank you!

    ------------------------------
    Jennifer Juhasz
    FamilySmart
    BC, Canada
    ------------------------------



  • 6.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-23-2022 10:13
    I suggest simply a  formula email field called [Email address to use if no email]

      [Staff Member - Community] & "@
    noemail.com"

    Then a form rule that says,

    when multiple conditions are true
    When
    all of
    email is (leave blank)
    No email available is checked

    Change [email] to the value in the field [Email address to use if no email]
    Change [No email available is checked] to unchecked


    ------------------------------
    Mark Shnier (Your Quickbase Coach)
    mark.shnier@gmail.com
    ------------------------------



  • 7.  RE: Formula Field - If this, then that; otherwise allow text entry

    Posted 11-28-2022 18:57
    This worked great!! Thank you Mark!  Always grateful for your help and guidance.

    ------------------------------
    Jennifer Juhasz
    FamilySmart
    BC, Canada
    ------------------------------