Forum Discussion

LB's avatar
LB
Qrew Cadet
12 months ago

Formula Help

Hello, 

I've created a formula to make another field unique based on selections in another status field. I'm not sure how to end the formula or if I have the set up correct? The error I'm receiving says "expecting comma"?

If([Referral Status] <> "Evaluation completed" , [Member ID],
If([Referral Status] <> "Evaluation not completed" , [Member ID],
If([Referral Status] <> "Member/legal guardian declined evaluation" , [Member ID],
If([Referral Status] <> "Other reason why not completed" , [Member ID])

Thanks, 
LB



------------------------------
LB
------------------------------
  • ChayceDuncan's avatar
    ChayceDuncan
    Qrew Captain
    12 months ago

    Looks like you're not closing out the ifs correctly. I will note that Quickbase is not like Excel so you don't need to repeat the Ifs for each condition. Try the below: 

    If(

         [Referral Status] <> "Evaluation completed" , [Member ID],
         [Referral Status] <> "Evaluation not completed" , [Member ID],
         [Referral Status] <> "Member/legal guardian declined evaluation" , [Member ID],
         [Referral Status] <> "Other reason why not completed" , [Member ID],

    "")



    ------------------------------
    Chayce Duncan
    ------------------------------
    • LB's avatar
      LB
      Qrew Cadet
      12 months ago

      Thank you for your response, the field accepted that formula but now it's not letting me save it correctly...I think I may have my settings backwards?

      I receive this error

      Basically I want to not have duplicate member ID's if they the referral status is pending or open...and if it's in one of the completed status as in my formula above to allow those records to save duplicate member ID's in the secondary field and push to the back end report. But when I enter in a test record I receive the error above even though my other record was placed in one of the complete status. 

      - LB



      ------------------------------
      LB
      ------------------------------
      • ChayceDuncan's avatar
        ChayceDuncan
        Qrew Captain
        12 months ago

        My assumption from your comments is that when it's falling into a status that isn't in your formula then it's defaulting to blank which is a value but not unique. My suggestion would be to change the default/else value to something unique that still makes sense in your reporting. Examples might include the Record ID#, a text string that says "COMPLETE" & [Member ID] - something that allows you to more or less mimic what you're trying to do but not deviate from uniqueness.



        ------------------------------
        Chayce Duncan
        ------------------------------