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] ---------------------...
Forum Discussion
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
------------------------------
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
------------------------------
- @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
------------------------------ - 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
------------------------------ - 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
------------------------------- 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
------------------------------
