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
------------------------------
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
------------------------------
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
------------------------------