Forum Discussion

AngelaAnderson's avatar
AngelaAnderson
Qrew Member
2 years ago

Help with GetFieldValue(GetRecords....to lookup value in another table

I want to pull a value from one table based on a field in another table.
I have a summary table with raw material prices, lets call this table B.  each entry is for a different month/year, so this field is text.  It is called Date and the field id is 10 on my raw materials table.  The other fields on this table are numeric and have various RM costs.  For this example I'll use:  RM 1, field id 25.

I have another table with customer product information, lets call this table A.  I use this table to calculate product margins at a certain time.  This has a field called Date as well and it is a multi select text field, id 120.  There is another field on this table called RM A Cost, this is where I am entering the formula.

When field 16 [Date] on table B = field 120 [Date] on table A, return the value from field 25 [RM1]

Here is the formula I used:
GetFieldValues(GetRecords("{16.EX.'"&[Date]&"'}","tableB"),25)

It is not returning a value.  It is underlined in red and says expecting value but found text list.  
Is this because the Date field on table A is multi text?  Or because I am using both text and numeric fields?

I am very new to programming in quickbase, so please explain in very simple terms.

Thank you

------------------------------
Angela Anderson
------------------------------

2 Replies

  • The result of the GetFieldValues formula is multi-select text, so it has to be converted to the data type of the field it's in (unless it's already a multi-select text field). So if it's a text field, convert the formula to text:
    ToText(GetFieldValues(GetRecords("{16.EX.'"&[Date]&"'}","tableB"),25))

    ------------------------------
    Ahuva Brown
    ------------------------------
    • AngelaAnderson's avatar
      AngelaAnderson
      Qrew Member
      Just tried that.  It lets me save the field, but the entire formula is underlined in red and now says expecting number but found text.

      When I look at the value on my table for RM A cost I am getting a value of 0.

      ------------------------------
      Angela Anderson
      ------------------------------