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