Finding the same sets in related dbs?

[mehronx]

I have a related orders database and customers database. The customer databse populates the order database with the customers address, etc based on a relationship of first name, last name, and email address.

What I want a script to do is this:

Go to a related found set.

So, for example, I have a found set of a hundred orders in the orders database. I want my script to jump me to a found set of the customers in the customer database.

[danjacoby]

Not an answer, just some further questions:

Is there always one (and only one) customer for each order?

Can several orders in any given found set have the same customer?

Now the big one: Under what circumstances, and for what purpose, do you want to do this? I ask because there may be another way to accomplish your task.

[mehronx]

Is there always one (and only one) customer for each order?

Yes, there is only one customer for each order.

Can several orders in any given found set have the same customer?

Yes. One customer could have 5 different orders.

Now the big one: Under what circumstances, and for what purpose, do you want to do this? I ask because there may be another way to accomplish your task.

This is the reasoning. I sometimes want to do a search on all customers who purchased certain items and then go to those customers records in the customers database.

An alternative way to do what I want would be to create some kind of portal from the Customers db to a purchasing history. This would however, get a little complicated since there is another db involved (lineitems). So I figured there would be a way to maybe do a loop search on customer ids or something.

[danjacoby]

You could create another "Items" DB that shows each item in a separate record, then has a portal for each time the item is ordered.

Come to think of it, you probably already have one -- it forms the line items in the "Orders" DB. In which case, a portal probably wouldn't do; use a sub-summary part when sorted by Item. Yeah, it would only be viewable in Preview mode, but it's there.

Or you could do a find in the Items DB for the item you want to look up, then just see what records are there (maybe sort them by customer).