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.

Re: Need Help With Filter

Hi Ehwagner,
From what I can deduce, you're on the right lines.
Rather than try and explain, I've just knocked up a quick app to show you one of the many ways it could be done.  In my example, I've used 2 grids and highlighting a row in the seller grid shows all the possible buyers that have the same business type.
Sorry that it's so 'rough and ready' but I hope it helps to move you on a bit.
Derek.

Post's attachments

Attachment icon buyers and sellers.zip 337.06 kb, 494 downloads since 2015-11-13 

Re: Need Help With Filter

Thank you so much Derek for your response. You are definitely on the right track.  What I would like to see is the buyer grid on your "Seller" form rather than the "SELLANDBUY" form.  I'm assuming the "Button1" you have on the "SELLANDBUY" form is what makes the buyer grid work there, although I'm confused how it does work because it is hidden, but apparently is triggering the search to load the buyers. Maybe you can shed some light on it a little more.  Thanks a bunch.

Re: Need Help With Filter

HI Ehwagner,
Once you've set up a 'SEARCH' button, you have a choice of either 'clicking' it or of having it fire automatically. 
In the knock-up example I did, to fire it automatically, you use the 'incremental search property of the search grid to associate it with BUTTON1 (obviously, you don't have to hide the button but it just makes it look neater) - see attached.
It was quickest (I'm lazy!) to show you using the SELLANDBUY form but the principle is exactly the same if you want to put the buyer grid on the SELLER form instead as you suggest.
Glad it helped.
Derek.

Post's attachments

Attachment icon ehwagner1.jpg 116.06 kb, 301 downloads since 2015-11-13 

Re: Need Help With Filter

You're awesome Derek. I got it working using your concept. Since I don't have a seller tablegrid on the Seller form, I couldn't use your incremental search feature. However I used your button concept to do an auto search on the Seller form by placing the script function for clicking the button on the form OnShow event as well as the Business_Type combobox change event. It works perfectly (almost - lol).  Although it's not really a show stopper, here is the only time it does not work properly. If I have a buyer who has a Business_Type of let's say "Auto Repair" and I don't have any sellers currently with "Auto Repair" Business_Type, but in the future I have one and add the new seller on the Seller form. When I select "Auto Repair" for the Business_Type for the new seller, I expect the buyers with "Auto Repair" to show in the grid, but it shows an empty grid. However, after saving the Seller record and then go back into the form, the buyers with "Auto Repair" do show up as they are suppose to. I can also change the Business_Type in the combobox and the correct buyers show.  So I know the button search is working but not when a new seller is put in as the first time seller with the same Business_Type as an established buyer(s). Like I said, I could probably live with it. Just wondering if there's a way to get it to work in that situation.

I've learned a lot just from your example Derek. I learned about the Incremental search feature for a button and that apparently search results override Tablegrid settings for what's displayed in the grid box. Bear with me, I'm trying to get a handle on this software. Thanks again Derek.

Re: Need Help With Filter

Hi Again Ehwagner,

Don't know about awesome - just learn a lot from my mistakes along the way!

To get around the problem you have with adding new records and it not showing immediately, it might help if you attach a little bit of code to the 'on after save' button event to update the affected grid (eg - form1.tablegrid1.dbupdate;) - that often does the trick.  I imagine you would also have the same problem if you were deleting a record or amending its Business Type.

I had a bit more time today so I reworked my previous 'rush-job' to include the tablegrid1.dbupdate code and some other bits as well.  It's attached if you want to have a look at it and then chop it about to suit.  I'm sure it's different to how you want it to work in 'real-life' but it might give you some ideas and clues as to how to do certain things in MVD.

Good Luck,

Derek.

Post's attachments

Attachment icon Buy and sell 3.zip 339.86 kb, 487 downloads since 2015-11-14 

Re: Need Help With Filter

Wow Derek, very nice. What you did there was actually one of the views into buyers and sellers that I had planned to do. Thank you. I appreciate it. I even got some more insight into some of the things that scripts can do. I was wondering if you don't mind, I would like to see how you would place a buyer grid on your "sellercrud" form and get that working (showing the appropriate buyers) when changing the Business Type and when in add mode for a seller. Thanks Derek. I do appreciate your assistance.

Re: Need Help With Filter

Hi,
Please find attached a rough and ready version of the 'sellercrud' form with a buyers grid attached. 
You need to be careful when you scroll through the various buyers in the sellercrud.tablegrid1 because you are actually in 'amend' mode and will change the business type for the particular seller that has been pulled through onto the 'sellercrud' form.
For that reason, I would probably just use the 'match' form instead - it shows exactly the same information as the 'sellercrud' but without that risk.  I've deliberately left the buyercrud as it was before so you can compare the two different approaches.
Hope it helps,
Derek.

Post's attachments

Attachment icon buy and sell 3 ehwagner.zip 341 kb, 544 downloads since 2015-11-16 

Re: Need Help With Filter

Perfect, Derek. I adapted what you did into my project and it works beautifully. Thank you so much for your help. I really do appreciate it. It's good to know that the support on here is dependable especially as I am testing and evaluating this software.

Re: Need Help With Filter

No problem, and glad it helped.
There's a few of the users who are happy to help out (probably because we've all been there at some stage!) and Dmitry (Mr "My Visual Database") is totally amazing at getting back to you with answers and suggestions to all sorts of questions.
Most of what I've picked up is from downloading other people's projects that were attached to posts in this forum and reading through how they tackled various things and then carrying on from there.
MVD is an excellent tool - hope your evaluation goes well and you decide to stick with it.
Derek.