Forum Discussion

ArchiveUser's avatar
ArchiveUser
Qrew Captain
10 years ago

Automatically set a ""Status"" when a field is no longer blank

I am trying to automate my database by having it automatically set its own status when a field is no longer blank. I know this can be done through the "Dynamic Form Rules" but it does not work when using Grid Edit. And Grid Edit is the primary source of inputs and/or changes to the database.

For Example:

  • When (Serial Number) is not (Blank) set status to (Pending Manufacture)
  • When (Form 2 Submit) is not (Blank) set status to (Manufactured)
  • When (Form 3 Submit) is not (Blank) set status to (Pending Approval)
  • When (Form 3 Approved) is not (Blank) set status to (Approved)
  • When (Tracking #) is not (Blank) set status to (Transferred)
  • If status is set to (Transferred) input (Date Shipped) to Today

My goal is to only have the status as a formula so that the rest of the fields can be manually changed if a mistake is made. For the record these are at the request of another department and I am sure more will be added later on, so if anyone can set me up with templates that would be superb.

Forms
Formulas and functions
reports and charts
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    10 years ago
    Yes, formulas are definitely the way to go. As you say they work in forms and Grid Edit and they are never flaky. QuickBase syntax is easier than Excel and have more informative systems error messages.

    Here's you formula for the formula-text field type called Status. I suggest that you write your formulas vertically as it's easier to spot your syntax errors that way. Note also that unlike excel you do not normally need to nest your IF statements unless you have some very complex logic. So in the example below the formula keeps working its way down the various tests until it finds the first condition which is true.

    The other thing that trips up novices is the treatment of nulls. If you have a numeric field there is a field property that you sent as to whether a blank should be treated as a zero as or as a null. They are not the same thing.

    The best way to test for null is to use the isNul() function. But that function does not work to test if a text field is blank so for that you have to test if it's equal to empty quotes as you'll see below.

    Any line which begins with // is treated as a comment and you can also put comments after a portion of your formula

    // I am assuming that all of these fields are text field type.

    IF(
    [Serial Number] <> "", "Pending Manufacture",
    [Form 2 Submit] <>"", "Manufactured",
    [Form 3 Submit] <>"", "Pending Approval",
    [Form 3 Approved] <>"", "Approved",
    <>"", "Transferred")

    Them separately, you're asking for a Date field to be updated when  the status changes to Transferred.

    I think the only way to do that in fact is in fact with Form a Rules, which of course means that it will not work with Grid Edit.

    The form rule would be

    When multiple conditions are true
    When all are true

    [Status] Is equal to Transferred
    [Date Shipped] equal (blank)
    Change [Date Shipped] to today.
  • olivermah's avatar
    olivermah
    Qrew Assistant Captain
    8 years ago
    i'm trying to do something very similar, however trying for the below - any suggestions?!
    • When (Booking number) is (Blank) set status to (Pending)
    • When (Booking number) is not (Blank) set status to (Booked)
  • QuickBaseCoachD's avatar
    QuickBaseCoachD
    Qrew Captain
    8 years ago
    If the data entry is only being done on forms and not grid edit you can set up a Dynamic Form Rules to do this.
Related Content