Forum Discussion

AdityaBhandar's avatar
AdityaBhandar
Qrew Member
3 years ago

Calling a field from another table

How to write QB formula that will pull up the record from another table field?

Table 1: Field1, Field2, Field3

Table 2: Field4, Field5, Field6

.[Field6]=
.[Field2]


------------------------------
Siva
------------------------------

7 Replies

  • Hi Siva,

    Could you explain what you are trying to do with that field from another table? Are you looking to pull one value from one record or pull up for example a summary or the most recent record's value, etc?

    ------------------------------
    Evan Martinez
    ------------------------------
    • AdityaBhandar's avatar
      AdityaBhandar
      Qrew Member
      "Are you looking to pull one value from one record or pull up for example a summary or the most recent record's value, etc?"

      Yes Evan! this is exactly what I am trying to do. Mimicing Vlookup and based on the value, I want to initialize certain value.

      To keep it other way, 
      If Vlookup from another table is X and if this matches with value in column C(same table), then write Y into the cell.

      ------------------------------
      Siva
      ------------------------------
  • 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
    ------------------------------
    • Quick_BaseJunki's avatar
      Quick_BaseJunki
      Qrew Captain
      @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
      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
      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
        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
        ------------------------------