A join question

[laurenals]

I'm hoping that someone out there may have a suggestion for what I'm working on. Here's the deal:

I have three files, an Account file, a Owner file, and a hinge between the files to allow me to keep track of a many-to-many relationship. The hinge file has three fields: AccountID, OwnerID, OAHingeID (which is the primary key).

When I'm in the Owner file, I want to be able to look at all accounts which belong to an Owner. So, supposing I click a button which says, "View all Accounts", it should take me to a list of accounts.

In order to do this, I end up copying the OwnerID, then searching for that OwnerID in the hinge. In the hinge, I come up with several records which the OwnerID is the same but the AccountID is different. Now I need to somehow get all of those accounts visible within the Accounts File.

One idea was to make a flag in the Account file, then search and mark the flag on each account which belongs to that owner. Then, when all appropriate accounts are flagged, perform a search in the accounts file to get all accounts that are flagged. This, however, would require a lot of going back and forth between files, and would be fairly slow. Does anyone have any suggestions of a better way to do this?

Thank you!

laurenals

[djgogi]

I've made example files.

Look at attachment.

Dj

[djgogi]

Retrying to send attachment

[laurenals]

I am unable to access the attachment.

laurenals

[laurenals]

The example files reflect exaclty the setup that I have currently (which I explained above). My problem still exists: I need to be able to view all of the accounts individually from within the Account file. So, basically, I have a list of accounts in the Owner file (through a portal) and I want to find that exact same list in the Account file. The question is, what is the best way to do this?

[keda]

If you can see the accounts you are interested in from the master file you could always use Go To Related Record.

/Keda

[laurenals]

I can't go to related record because I've got to go through the hinge.

laurenals

[The Bridge]

Off the top of my head, how about something like this:

In your Owner file, define a value list, say, vl_Owner_Accounts, which draws related Account Keys from your Hinge file. Then define a global field in Owner, e.g. Current_Owner_Accounts. Create a script which sets Current_Owner_Accounts to the values in vl_Owner_Accounts using the ValueListItems() function. Define a relationship from Current_Owner_Accounts to Account Key in your Accounts file. Then use the Go To Related Records (show only related) script step to take you to accounts.

Hope this helps.