Find Related Records

[Timothy Moser]

In a one-to-many relationship, A is on the "one" side and B is on the "many" side. My goal is to have a button on A's layout so that it somehow takes the ID of the current record, goes to the B layout, and performs a find that returns all of the B records related to A. The script never works. I think my problem is the find criteria; I cannot get it to use the ID of the current A record. Does anyone have suggestions? If you need more specific ("real") information about the database, I am willing to provide it.

Thanks.

-Timothy Moser

Edited July 29, 2009 by Guest

[comment]

Why don't you simply use Go to Related Record [show related only]?

[Timothy Moser]

Because I have trouble finding the easy way to do things. Thanks for pointing out the obvious. It works just fine.

[Timothy Moser]

OK, here is the real deal:

The two tables are related in more than one way. Each table has more than one occurrence in the relationships diagram and they are related to each other both times in different ways. I want to restrict the found set to those found across one of the two relationships. Is there any way to do this? I guess I will have to go back to multi-step scripting.

Thanks.

-Timothy Moser

[comment]

It depends on the exact situation (which isn't clear). If your two relationships are built like this:

Parent -< Child

Parent -< Child 2

you should have no problem selecting the appropriate occurrence of the Child table in the GTRR step.

If, OTOH, you have two occurrences of Parent instead:

Parent -< Child

Parent 2 -< Child

you must go to a layout of Parent 2 before you can use GTRR to create a found set of Child based on the second relationship. Obviously, the former arrangement is preferable - at least for this purpose.

[Timothy Moser]

OK, one more dumb question. The relationship is between a text field (in the child table) and a calculated text field (in the parent table). The script always fails and says that the relationship is invalid. Is it because it thinks the fields do not match, or is it maybe because the calculation is originally based on another occurrence of the child table? Again, I can give more details if they are needed.

The help is much appreciated.

-Timothy Moser

[comment]

I can give more details if they are needed.

They are. Perhaps you could attach an example file showing the two relationships.

[Timothy Moser]

OK, here are the relationships:

This is the goat database. The only tables important right now are Goat, Breeder, and Goat Ownership. Basically one person owns a goat at a time, and each record in Goat Ownership represents a transaction in which a goat changes owners. The Goat table has a field in which the "last ownership transfer date" is calculated with "Max(Goat Ownership::Date)", and then a calculated text field, "current owner", is created from the "Goat_ Current Owner" occurrence with "Goat Ownership_ Current Owner::New Owner's ID" (the two table occurrences are related based on the calculated date in "Goat_ Current Owner" and the transaction date in "Goat Ownership_ Current Owner"). So far, so good: the Goat table accurately displays the last ownership transfer date and the name of the current owner.

The "Breeder_ Current Owner" occurrence of the Breeder table is related to the "Goat" table based on the Breeder name in the former and the calculated "Current Owner" in the latter. That seems to be the relationship that is messing up.

I hope this makes sense. Thanks for all the help.

[comment]

I am afraid I cannot tell you anything without seeing the file - except that I think you are making this much more complicated than it needs to be.

Your core relationships should be:

Goats::GoatID = Ownerships::GoatID

Breeders::BreederID = Ownerships::BreederID

To display the details of the last ownership (and the breeder), you could use a one-row portal to the Ownerships table, sorted by date, descending.

To calculate the last owner's BreederID, you could use =

Last ( Ownerships::BreederID )

provided that ownerships records are either entered in chronological order or that the relationship is sorting them by date, ascending.

[Timothy Moser]

I simplified it a little, so now Current Owner is calculated by Last(Goat Ownership::New Owner's ID), but I do not think the portal thing will work for my purposes. I want to be able to show my users all of the goats that a certain breeder currently owns, so I think I have to have a field on the Goat table for the current owner and a relationship between that and the breeders. The script still fails. Here is the relationship diagram:

[comment]

I want to be able to show my users all of the goats that a certain breeder currently owns

From the Goats layout??

[Timothy Moser]

No, I have a button on the Breeders layout.

[Timothy Moser]

But it would show records in the Goats layout.

[Timothy Moser]

I have been able to make a button that brings the user from a Goat record to the Breeder record of the goat's current owner, but I cannot get the relationship to work the other way around. Here is the modified relationship system.

[comment]

I am afraid you have lost me. You said:

I want to be able to show my users all of the goats that a certain breeder currently owns

I would guess this information should be viewed from a layout of Breeders - as this has nothing to do with a specific goat, and everything to do with a specific breeder. This is an entirely separate issue; so far the question was which breeder is the current owner of a specific goat (and I am not sure if you had successfully solved it).

[Timothy Moser]

I want the users to be able to navigate from their own Breeder profile to all of their goat records, and then from there to other tables related to the goats. It seems to me that the only way to do this would be with a sort of "show related records" command based on the relationship between a breeder and all of the goats that the breeder currently owns. The "Goat - Breeder" relationship shows all of the goats that the breeder has ever owned, not just the ones that he currently owns. That is the reason for the extra relationship.

[comment]

As I said, this is another issue - and a much more complex one:

In order to determine if an ownership is current, we must compare it with other ownerships of the same goat. The result of such comparison will by necessity be unstored and not suitable to be used as a matchfield in a relationship.

The workaround for this is the so-called "Ugo method" - see attached.

CurrentOwnerships.fp7.zip

[Timothy Moser]

OK. Suppose there was a calculated text field on the "Assets" table that showed who the current owner of each asset was. If we went into find mode in the Assets layout, we could type the name of the owner into that field and come up with all of the records belonging to a certain person. That is how it currently is in my Goats database. All I want is a script that will do that search from a button on the Owners layout.

[comment]

That would be quite simple - roughly:

Set a variable to the current owner's name;

Go to a layout of assets;

Enter Find mode;

Set your calculation field to the variable;

Perform Find

[Timothy Moser]

That is perfect. Thanks a bunch.