Forum Discussion

JeremyAnson's avatar
JeremyAnson
Qrew Cadet
4 years ago

Re: Calling a field from another table

If I've understood correctly, to build a 'vlookup' using the value in a field in Table 2 (assume it's named Field 1) to find a match in Field 8 in Table 1 and pull back the value in Field 9, I think you'd need something like this:

GetFieldValues(GetRecords("{x.EX.'" & [Field 1] & "'}",[_DBID_TABLE_1]),y)

Where x is the field ID of Field 8, y is the field ID for Field 9 and _DBID_TABLE_1 is the table alias of Table 1, which you'll find at the bottom of the advanced settings screen.

------------------------------
Jeremy Anson
------------------------------

4 Replies

  • QuickBaseJunkie's avatar
    QuickBaseJunkie
    Icon for Qrew Legend rankQrew Legend
    4 years ago
    @Siva Bhandar if there is a relationship between Table1 and Table2 you could use summary or lookup fields to get the values onto the same table for your formula.

    Otherwise, Jeremy's example is what you're looking for.

    For more information on Formula Queries, I put together this video ⬇

    ​

    ------------------------------
    Quick Base Junkie
    ------------------------------
  • AdityaBhandar's avatar
    AdityaBhandar
    Qrew Member
    4 years ago
    Hi Jeremy,
    What is x.EX?

    Table1: Field 9 should compare value derived from VLOOKUP from table 1 and write a specific value in Field 9. So, does your formula, GetFieldValues(GetRecords("{x.EX.'" & [Field 1] & "'}",[_DBID_TABLE_1]),y) derives value from table 1  into field 9 of table 2?
    Table 1
    Field 8 Field 9
    Vlookup Field 1 in table 2  
    Field id: x Field id: y

    Tabel 2
    Field1  


    ------------------------------
    Siva
    ------------------------------
  • AdityaBhandar's avatar
    AdityaBhandar
    Qrew Member
    4 years ago
    Jeremy, To try your formula I created a small example

    Table 1: Sales (DBID:_DBID_CUSTOMER_SALES) with Fields Customer, Sales in $
    E.g.: Customer   Salesin$
               1001           $100

    Table 2: Customer with Fields: Customer (field id: 6), Havesales
    E.g.: Customer   HAVESALES
               1001           GetRecords("{6.EX.'" & [Customer] & "'}",[_DBID_CUSTOMER_SALES])
    ​
    However the syntax says it is wrong.



    ------------------------------
    Siva
    ------------------------------
    • JeremyAnson's avatar
      JeremyAnson
      Qrew Cadet
      4 years ago
      I wrote a reply, but I guess I forgot to post it - sorry!

      The error is occurring because your field is expecting text as a result, but GetRecords is producing a record list. You'll want to wrap your GetRecords statement in GetFieldValues to complete your 'Vlookup', and if the result of that is not a text field (it looks like it's a number field), then you might need to use ToText as well. 

      So, you might have:

      ToText(GetFieldValues(GetRecords("{6.EX.'" & [Customer] & "'}",[_DBID_CUSTOMER_SALES]),n))

      Where:

      {6.EX.'" & [Customer] & "'} looks for records where the field with ID 6 in your customer sales table have a value that matches the value in the customer field. You'll need to check that the customer field in the customer sales table has ID = 6. 

      You'll need to replace n with the ID of the field that contains Salesin$

      Hope that helps.

      ------------------------------
      Jeremy Anson
      ------------------------------