Goto Related Record

[Joseph31]

I have two Databases Call them Data1 & Data2 -- both are hosted on FM11 Server.

I have made a pulldown menu on Data1 layout called Carrier... It pulls the names off of data2.

The relationship between them is Data1ID=Data2ID

The Problem is when I make a script to go to the related record to see more detail is goes to the wrong record... It goes to the ID that I am on not the name in the pulldown field

Any help would really help... An example of how this work would be very helpful.

Thank you everyone

Edited October 14, 2010 by Guest

[Matthew F]

It sounds like you want to make a new relationship between Data1 and Data2 using Data1::Carrier and Data2::Names as the match fields.

[Joseph31]

I thought that -- But everyone keeps saying make the relationship between ID's not names?

Any thoughts on this one?

Thank you for the reply

[Matthew F]

Either way, names or IDs. The point is to make a relationship that links to the other table based on the field in the pull down list. Using IDs you could do this:

1. Create a global field in the Data1 table called 'ID2_select'.

2. In the relationship graph create an extra instance of the Data2 table and call it Data2B'.

3. Link Data1::ID2_select to Data2B::ID in the relationship graph.

3. Format 'ID_select' as a pull-down list using Data2::IDs as the value list, but showing all records.

4. Use the Go To Related Records script step, but use the Data2B relationship (not Data2).

Incidentally, you can use IDs but have the pull down list display names, if you check the box "also display values from second field".

[LaRetta]

"The relationship between them is Data1ID=Data2ID"

"But everyone keeps saying make the relationship between ID's not names?"

Yes, absolutely correct. That is exactly what everyone keeps saying and it is very important that you understand it for your primary relationships. I believe you are missing the point that you don't join ID to ID (as in bold portion above) ... you instead join a Primary ID in one table (where it is the unique entity) to its same ID in the other table (where it is the foreign key). You are saying you have the primary ID from two tables joined and they will never match! For example:

Customers relating to Invoices. Customers is one to many invoices. In the customer table, the unique auto-enter serial is CustomerID, thus the primary or parent key. In Invoices, you add another CustomerID but this time it is NOT auto-enter - you insert the ID of the customer using a value list (pop-up). The name only displays from/through the relationship.

It is important that you understand this process and it will keep coming up and confusing you until you do. If you want specific examples, fire away. If you want to show your file and the relationship and how it should be connected, please ask. This is the basis of all good design and it's important that you understand it.

If the name is different than the ID then it is because you have planted the name as actual data in a field and it has not been kept in synch with its true ID. You only want the foreign ID in your child table, not the name *.

*note there are exceptions where you need to plant data for historical purpose but that is rarely necessary, particularly on names (which can change).

Edited October 15, 2010 by Guest

[Matthew F]

There can be times when you want to create a relationship based on something other than the primary IDs (although Names are a poor substitute for IDs, as LaRetta points out). For example, if you want a script to go to a set of records (or display in a portal) based on some set of criteria other than the primary IDs. The alternative is to simply switch layouts and to perform a Find.

If I understand you correctly, you don't want to go to the 'related record'. Instead, you want to go see supporting data based on a name which you have just selected in a field called 'carrier'. Is the name in 'carrier' unrelated to your current record, and in reality just a substitute for performing a 'Find' on the other table? That is what I was assuming in my suggestion, above, for creating a global field and a new relationship.

[LaRetta]

Good point, Matthew, and I thought that might be the case. I was covering the other side (logic of joining by IDs) because I sensed there was confusion on understanding it and that's why the question has come up. Thanks for adding more information!

[Joseph31]

Sorry my first example was not the whole story --- Carrier is related to the Data1 table -- it is the insurance company name that the person has the policy with.

The relationship between Data1 table (insured) and Data2 table (Carrier) was going to be Data1::Carrier = Data2::Carrier

But I thought that would be incorrect?

Thank you for the overview --- I am getting this slowing.

[LaRetta]

The relationship between Data1 table (insured) and Data2 table (Carrier) was going to be Data1::Carrier = Data2::Carrier

But I thought that would be incorrect?

It WOULD BE INCORRECT.

What if Allied Insurance, Inc. changes their name to Allied Coverage Pros? You have suddenly broken your relationships to all their insureds and you no longer have relationships. This is why we always use meaningless, unique IDs (so in this case, in your Data1 Insurance Companies table). Don't think insurance companies can't change names - they do it all the time and so do banks. There is only one way to protect ... use unique IDs for every table and your relationships will always remain intact. Anything less (except possibly social security numbers) will break (and even that I wouldn't trust because the data-enter person could enter it improperly and when they change it, it will break.

Let FileMaker handle it for you - transfer the ID between tables. :wink2:

Edited October 18, 2010 by Guest

[Joseph31]

Great -- So what would the relationship be then -- I try unique ID's but when making my script go to related record --- it goes to the wrong record every time...

Any help would help.. Thank you

Joseph

I tried ID_Insured = id_Carrier

ID_Insured is auto Serial and Unique

id_Carrier is related only ---

But still goes to the wrong carrier if I say go to related record...

Thank you

Edited October 18, 2010 by Guest

[Matthew F]

But still goes to the wrong carrier if I say go to related record...

If you put some fields from the related record on your layout, using the same relationship that you want to go to in your script, does it display the information for the record that you want to go to? If not then your relationship is not set up correctly.

[LaRetta]

ID_Insured = id_Carrier

Those do not relate. You need ID_Carrier = ID_Carrier

This is what I sensed earlier ... you do not understand how to relate your records. And you are not providing enough information. Post an empty clone of your file, please, or post a sample of the two tables and those main fields in each.

Your Insured table should have the ID_Carrier. GTRR to your Carrier table will take you to the one Carrier which belongs to that Insured.

Edited October 19, 2010 by Guest
Modified entire post :-)

[Joseph31]

I understand one to one relationships but not sure how to do One to many so well i guess (easy if I do Carrier to Carrier) but how to do Id's to relate backwards -- Please see example...

I think I am close...

Thank you again...

Sample_Carrier.zip

Edited October 19, 2010 by Guest

[Matthew F]

Your script works fine. You just need to add a number in the carrier ID field. Also you should not have a field called "carrier" on the policy table. The carrier_id is the match field, so you should show 'carrier' as a related field from the carrier table.

The 'Go to related record' script step works fine when going from a many -> one relationship (a policy can only have one carrier). However, going the other way a carrier can have many policies. Therefore you need to have a way to designate which policy to go to. For this I've added a portal on your carrier page. Select the row that you want and then the button will take you to that policy. If you don't do this then Filemaker defaults to going back to the policy which would show up at the top of the sort order (as determined by your relationship graph), not necessarily the policy that you were last viewing. An alternative is to simply change back to the the policy layout which will display the policy that you were last viewing.

Sample_Carrier.fp7.zip

[Matthew F]

P.S. I also added a new value list to make it easier to pick carrier IDs by displaying the carrier names as well.

[LaRetta]

ID_Carrier = ID_Carrier

I understand one to one relationships ...

I just want to clarify that, even though you join the same ID in two different tables does not mean it is a 1:1 relationship. Either side can be the many side and even both sides can be a many side. It is a matter of perspective in FileMaker.

As I originally pointed out and Matthew so graciously demonstrated, if your 'name' doesn't match same related record then it is because you have duplicate data. The Carrier name in Policy is redundant data since you have the ID_Carrier. It isn't necessary but even worse, you won't be able to keep it in synch with its ID_Carrier field. :wink2:

[Joseph31]

Thank you you both so far --- This is a great why to really make sure I have sold relationships in my database....

One question --- Is it possible to have just the carrier pull down menu make the link between the tables --- Meaning not have a separate field that displays the id's then another field display the carrier -- can we combine them into one, but show the carrier not the ID?

Thank you

Edited October 20, 2010 by Guest

[LaRetta]

You have have only the ID_Carrier field. Attach a pop-up menu to it with a value list based on your carrier (explained by Matthew). The 'show second field' and below radio button - 'only show values from second field' come into play here.

After you select your carrier from the list and exit the pop-up, it will only display the carrier name ... no second field holding the name is required. You can also place any other fields from the carrier directly onto your Insured's layout because carrier is the 'one' side to many insureds.

[Joseph31]

Funny, I did that exactly and it does not show the carrier name --- it still shows the first field... I tried it on the example posted...

Unless it only works on popup's --- why would that be? I guess I could make a script that does it so the fields look the same on the layout?

Hmmmm.. anythoughts?

Edited October 20, 2010 by Guest

[Matthew F]

Pop up menus and drop down lists behave differently from one another when displaying a value list in which you have selected "also display values from second field" and "show values only from second field".

Drop down lists will display the second value for the drop down, but will go back to displaying the first value when you have deselected the field. Pop up menus will display the second value both during the pop up and also when the field is not selected.

To make a drop down list behave more like a popup, in this regard, just create a second standard edit field (set no entry in browse mode), and place it on top of the drop down field. I've modified your example further to demonstrate this.

Sample_Carrier.fp7.zip

[Joseph31]

Great that works really well --- thank you everyone for your help --