Forum Discussion

Qrew Member

3 years ago

Question on a Formula Query

I have a Formula query that is giving me the error message "Expecting Number." I expect that the field (159) is a number in my table. The record ID (108) should also be a number? Commented out on...

formula query error 20822.pdf11 KB

formula queries

Qrew Member

Thanks Mike,

I tried ToNumber() and ToText() in a few places in the formula to no avail. I also tried changing the type of the filed
from Formula-Numeric to Formula-Text as a trial and that doesn't work either.

------------------------------
Chad Kragness
------------------------------

SeanConnaughto1

Qrew Cadet

3 years ago

Does this work:

ToNumber(ToText(GetFieldValues(GetRecord(108, [_DBID_TITAN_TIMBER_ITEMS]), 159)))

------------------------------
Sean Connaughton
------------------------------

MarkShnier__You
Qrew Champion
3 years ago
OK, so now we see your code, but can you say what you are trying to do with it? What's the goal?

------------------------------
Mark Shnier (YQC)
mark.shnier@gmail.com
------------------------------
- ChadKragness
  Qrew Member
  3 years ago
  I'm looking to pull the Cost of the item in record ID 108 from the table shown and bring
  it to a new table so I can then use it (and 43 others) on a price page for our items using an HTML page.
  field 159 is where the cost is stored in that table.
  
  Thanks,
  Chad
  
  ------------------------------
  Chad Kragness
  ------------------------------
ChadKragness
Qrew Member
3 years ago
Sean,

The formula you show allows me to exit the page without errors, but it does not return anything. I think we're past the number vs text thing? But not sure why it returns nothing. I may need to re-think my solution.

Thanks for taking time to assist.

------------------------------
Chad Kragness
------------------------------
- MarkShnier__You
  Qrew Champion
  3 years ago
  There needs to be a query with a filter. Try this. i know the syntax is brutal to get correct.
  
  var text QUERY =
  "{3.EX.108}";
  
  // this converts to a number
  ToNumber(
  
  // this converts the result which is a "textlist" format to plain text.
  ToText(
  
  // this gets the Field values from field 159
  GetFieldValues(
  
  this gets a set of records which meet the filter
  GetRecords($QUERY,[_DBID_TITAN_TIMBER_ITEMS])
  
  ,159)))
  
  var text QUERY =
  "{3.EX.108}";
  
  ToNumber(
  ToText(
  GetFieldValues(
  GetRecords($QUERY,[_DBID_TITAN_TIMBER_ITEMS])
  ,159)))
  
  ------------------------------
  Mark Shnier (YQC)
  mark.shnier@gmail.com
  ------------------------------
  - ChadKragness
    Qrew Member
    3 years ago
    Thanks Mark,
    
    I have some other Formula Queries that I had gotten to work that had the "query" part of them.
    I wondered if I needed to add one, but didn't get far enough into it to try that option.
    
    Thanks again.
    
    ------------------------------
    Chad Kragness
    ------------------------------
- SeanConnaughto1
  Qrew Cadet
  3 years ago
  Hey Chad,
  
  One last stab at it, see if this works:
  
  SumValues(GetRecord(108, [_DBID_TITAN_TIMBER_ITEMS]), 159)
  
  That worked for me when trying to pull a numeric field from another table, so hopefully it works
  
  ------------------------------
  Sean Connaughton
  ------------------------------
  - ChadKragness
    Qrew Member
    3 years ago
    That did it Sean. I hadn't tried the SumValues as I thought I should be able to just grab the value
    that I can see sitting in the field.
    
    Thanks again to you and the others for taking time to help.
    
    ------------------------------
    Chad Kragness
    ------------------------------