Forum Discussion
QuickBaseCoachD
10 years agoQrew Captain
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.
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.