Forum Discussion

PaulHuizenga's avatar
PaulHuizenga
Qrew Member
4 months ago

Text Formula Field returning Blank

I have used formulas in this program for a while now and have had no issue until recently.
The field this formula is stored in is a text-formula type.

Formula:
If(
//Checkmark Boxes
[Billed]=true,"Billed",
[Invoice Drafted]=true,"Invoice Drafted",
[Approved]=true,"Approved",
[Hold for Authorization]=true,"Hold for Authorization",

//Reading Billing Status
Begins([Billing Status (Billing Team ONLY)],"Billed"),"Billed",
Begins([Billing Status (Billing Team ONLY)],"Invoice Drafted"),"Invoice Drafted",

//Formula
Contains([Service Billing Adds (Memo required)],"**NOT BILLABLE (Leave Comment)**"),"NOT BILLABLE",
[Project - Total Task Remaining Hours]>0 and [Project - Total Task Remaining Hours]<1.5 and [Project Type]<>"Advisory","Hold for More Time",
[Billing Family - Total Over Set Days (Connection)]>0,"Approved",
[Billing Family Approved Total]>500 and [Billing Family Approved Total]<1000,"Approved",
[Billing Family Approved Total]>1000,"Hold for Authorization",
"No Rule for This")

Now to my understanding, if I have, "No Rule for This", at the end of this formula,
then anything that doesn't fall into the above formula should just read, "No Rule for This".

When I use the new formula checker that is in beta, it confirms this theory.

However, I have some cells that are returning nothing (see image), they are blank.



------------------------------
Paul Huizenga
------------------------------

2 Replies

Sort By
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    4 months ago

    You don't seem to have any syntax or other errors - so my first hunch might be that one of your fields is potentially corrupted and so this formula falls in line too. A more concrete example might be doing an if statement where one of the If() conditions is doing math where the denominator is 0. QB throws that as an error and the result is is can't process the rest of the formula. 

    So the hunch is that one of your fields is doing a calculation that might be corrupted before this formula actually runs. So like [Billing Family Approved Total] - if that were a formula field and it was somehow undefined QB can't process beyond that as just a random point and pick example. I would suggest running a report for the records where your billing status formula is blank - and include in the report all the component fields that you're using in this formula. Check them to see if there is one with a blank value where you expect a 0 or something. You won't find a formula error for this because the actual error isn't with the formula it's the processing. So if I do [Price]/[QTY] there is no syntax error - but in your data if [qty] = 0 then the actual processing of that is impossible and it ripples into fields that might use that. 



    ------------------------------
    Chayce Duncan
    ------------------------------
    • PaulHuizenga's avatar
      PaulHuizenga
      Qrew Member
      4 months ago

      I will look into that, that makes a lot of sense to me.



      ------------------------------
      Paul Huizenga
      ------------------------------