Forum Discussion
MarkShnier__You
Qrew Legend
3 years agoYa, 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
------------------------------
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
------------------------------
AnneMartin
3 years agoQrew Trainee
I tried this but unfortunately it still does not work. The variable seems to be the problem, as $myquery is highlighted in the formula.
------------------------------
Anne Martin
------------------------------
------------------------------
Anne Martin
------------------------------
- MarkShnier__You3 years ago
Qrew Legend
Please post a copy and paste your formula text and also the error message
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------- AnneMartin3 years agoQrew TraineeIs this what you're asking for?
//Tech Codes FID 8 = technician, FID 17 = bonus rate
var text myquery= "{8.EX.'"&[User to text]&"'}";
ToNumber(
ToText(
Getfieldvalues(getrecord($myquery,[_DBID_TECH_Info]),7) ))
------------------------------
Anne Martin
------------------------------- MarkShnier__You3 years ago
Qrew Legend
Change GetRecord to GetRecords
------------------------------
Mark Shnier (Your Quickbase Coach)
mark.shnier@gmail.com
------------------------------