Topic: Need Help With Filter
I am brand new to this product so bear with me. I'm trying to learn all the various nuances of MVD. I have a sort of master-detail situation, but not in the traditional sense. I have three tables. One is a SELLER table which contains businesses who are looking to sell. A second table is a BUYER table which contains potential buyers for the businesses for sale. These two tables in itself are not directly related. Neither one is a parent nor a child to the other. They are completed independent. The third table is a BUSINESS_TYPE reference table. The SELLER table has a field for Business_Type which is setup as related (foreign key) to the BUSINESS_TYPE table. This of course identifies the type of business the Seller has. The BUYER table has a Business_Type field which is also set up as related to the same Business_Type table. This identifies the type of business the buyer is interested in. Creating the database tables and the relationship is a piece of cake.
Here is the situation I need help with:
I have a SELLER form with the detail record displayed. On that same form I have a grid which I want to display potential BUYERS for the seller currently displayed on the form. I have the table grid defined for BUYERS, but I need to filter it by the same Business_Type as the seller displayed on the form. I tried using the filter field in the settings for the tablegrid, such as "Buyers.id_Business_Type = Sellers.id_Business_Type". But that resulted in showing all the buyers having the same Business_Type for "ALL" sellers. I only want to display buyers with the same Business_Type as the seller record that is currently displayed on the form.
I'm pretty sure there is a simple solution through a query or something, but as a newbie I haven't been able to figure out how to do it in this software. Once I know how to define this, I'll be able to do the same thing in reverse whereby I can display a buyer and have a grid of potential businesses available for the buyer. Hope you can help. Thank you.