How would you like to display these related customers? In a portal or in a list view?
In a layout based on your BusinessPartner table you could make a portal that is based on "CustomerDealer" that shows the CustomerId's for that particular DealerId.
Add to that layout the BusinessName from the RelatedCustomer table and you should have something.
Otherwise you might make a list view based on "CustomerDealer" that contains the CustomerID from the CustomerDealer table but the BusinessName from the RelatedCustomer Table.
In a list view you might have to do a find for the "BusinessPartnerId" from the BusinessPartner table in the DealerId in the CustomerDealer table. This to display only one dealer and to see his related customers.
I hope this makes sense, it sounds a little funky, I know. :)
I'd be inclined to use one table for Contacts (dealers, customers etc.) for name, web site etc and a related table for addresses. And with yet another table for phone numbers and email addresses. This can be more flexible when a given contact might have many different phone numbers and more than two addresses.
Working with what you have, a portal to customerDealer can be placed on a BusinessPartner layout. Fields from RelatedCustomer can be included inside the portal row to list addresses and other data.
You can also create a list view layout based on CustomerDealer and perform a find on it for a specified DealerID (or Use Go to related records from the BusinessPartner layout). You can include fields from RelatedCustomer on this layout to list the addresses.
Thanks DaSaint, I'll try it. But I don't want to list just the dealer's customers. I need to list all their addresses. And as you know, they are in a different table. (I made a mistake of not including the Address table in the screenshot)
Sounds to me like you need to use a second TO of the adress table as the basis for your portal, and maybe relate the board member table to this secont Table Occurrence's BusinessPartnerId.
The idea being that it's your adress table that holds every adress ad has a link to every Business Partner.
So yo would get a list where you see the same business Partner multiple times, each time with a different adress.
I'm not at home right now so I can't work this out in more detail, I'll look in to this a little more later.
I made a little trial.
Could something like this work?
I tried it with portals to be as compact as possible.
In the second tab you can both:
- Link contacts (or add linked contacts)
- Select contacts to see their adresses
Then you can set the button to do something with the adress.
thanks for the demo. This approach seems to work, I've already solved it by using something similar. However what I originally had in mind, (and what I may still be asked to do) is list all the relevant addresses in one long list. The names of the related customers should be displayed along with the addresses in a special column. Ideally they should only have the customer name on the first address line belonging to that customer, but that's not a requirement. I think the list should contain both billing and shipping addresses, since the customer is likely to only have one address, listed as billing, but serving for both billing and shipping. I've attached a picture of what I mean, made by mashing up all the addresses from your example. I'm not sure I'll even need this, but as part of my FileMaker training I'd really like to know how to do this, for future reference.
I think we should be able to come up with something like that.
The way I did it now was to keep it as compact as possible.
I'll have another look at it tonight.
But that example of you looks like it needs a list view with a sub summary part.
Allright, this was a little bit of a tricky one, but I managed to find a way.
It required making a find based on a value list from another table. But it seems to work nicely.
The way it works is:
On a layout based on the table contact, when you click the orange button. A variable is created that is a list of all the linked contacts of the contact that is currently the active record.
A new find is created and in this find a new record is made for every contact in the list.
Then the find performs and shows you a list of all the adresses of all the contacts that are linked to the contact you came from.
It's a list with a subsummary part.
You could then use the select button to perform whatever script you want.
I should have renamed the file, but I forgot to.
Thanks, I'll study it and try to emulate it, just to learn how it's done.