Skip to main content
Question

Formula Query expecting number but found text

  • August 3, 2022
  • 0 replies
  • 486 views
I'm new to writing formula queries and keep running into type mismatches. 
I'm figuring bonuses for our technicians, and have the bonus rate stored on another table.

var text myquery= "{8.EX.'"&[User to text]&"'}";
Getfieldvalues(getrecord($myquery,[_DBID_TECH_Info]),7)

The formula above says it's expecting a number but found text. Do I need a different function to look up a text field?

------------------------------
Anne Martin
------------------------------
This topic has been closed for replies.

MarkShnierYou
Forum|alt.badge.img+22
  • MarkShnierYou
  • Quickbase Alumni
  • August 3, 2022
Ya, we are all new to Formula Queries.  I still get a thrill every time they work first crack.

Assuming that you know that you will just get one "hit",

try this
var text myquery= "{8.EX.'"&[User to text]&"'}";

ToNumber(
ToText(
Getfieldvalues(getrecord($myquery,[_DBID_TECH_Info]),7) ))

// the Totext converts the result from a "textlist" format to regular text and of courser the ToNumber converts it to a number
//  just yesterday I was not pleased to find that when the numeric currency Rate field that I was trying to grab was formatted with a leading $ that converting it to a number failed.  Seems very unfair that the formula query it's sensitive to the formatting of the field. Certainly Excel does not behave that way. But that is just my little rant for the day.  I'm balance formula queries are wonderful. 

If that also happens to you  you will need to do this.

ToNumber(
NotLeft(
ToText(
Getfieldvalues(getrecord($myquery,[_DBID_TECH_Info]),7) ),1)

Hopefully my brackets are correct.


------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------
Mark Shnier. Your Quickbase Coach
Terms & ConditionsAccessibility statement