How to display related field from another table on a layout?

[Asphyxia1974]

Hi there, I've only been using Filemaker Pro 13 for two weeks. Most of what I've written is working well but I am really stumped on a very simple problem:

 

I have a field in my products table where the user can select a size from another table. This field shows the size name and works correctly. I then want to display related fields from the size table, so that after the user has selected a particular size, the height and width will automatically be displayed on the products layout. I have set up a relationship between the two tables, and set the height and width of the products table to be calculations, but the height and width never show up on the products screen. 

 

How to fix this?

 

Thanks!

Asphyxia

[comment]

Based on what you tell us, this should work - so there must be something you are not telling us. How exactly did you set up the relationship? Also check your data: if the values are not exactly the same (e.g. one has a trailing space) there will be no match and no related record.. Field types should match too.

[Kris M]

Do you have a portal based on the sizes table on the products table based layout?

You stated you have a relationship but what, on the products layout, are you using to display the size related data?

[Asphyxia1974]

Thanks for your help.. I suspect it's something small I'm doing wrong.  Ok here's the details:

 

In the products table:

Size field is text

Height and weight fields are calculations

 

On the products layout:

I am not using a portal. I have three individual fields.

Size is a drop down list with values from Sizes.  When I managed the value list, I set the values to be From Field Sizes::Size (This field works correctly)

The height and weight fields are Edit boxes - no special formatting or details.

 

In the sizes table:

Size field is text

Height and weight fields are both numbers

 

The relationship I set is that:

Products::size = Sizes::size AND

Products::height = Sizes::height AND

Products::width = Sizes::width

 

What do you reckon? Does this tell you enough to work out where I'm going wrong?

Thanks!

Asphyxia

[comment]

In the products table:

...

Height and weight fields are calculations

 

Calculations of what? I thought you wanted to select the size of the product, and display the height and the weight dimensions from the related record in Sizes. In such scenario, there would be no Height or Weight fields in the Products table, and the relationship would be based on:

 

Products::Size = Sizes::Size

 

only.

[Asphyxia1974]

Calculations of what? I thought you wanted to select the size of the product, and display the height and the weight dimensions from the related record in Sizes. In such scenario, there would be no Height or Weight fields in the Products table, and the relationship would be based on:

 

Products::Size = Sizes::Size

 

only.

 

 

Ok, I was thinking that the data would have to be copied into the relevant fields on the products table, but now I see that they are simply displayed direct from the series table.

 

SO... I have changed the fields on the Products layout to display data from Sizes::Height and Sizes::Width, and I have deleted the Height and Width fields from the Products table.

 

However, now the Height and Width on the product layout shows a "?" in the field.  What to do next?

[comment]

However, now the Height and Width on the product layout shows a "?" in the field. 

 

Are they wide enough to display their values? What happens if you click into them?

[Asphyxia1974]

Oh I've got it!  Thank you so much!  When I clicked into it it said the relationship between the two tables was invalid.. and I realised I had forgotten to remove the AND Products::height = Sizes::height AND Products::width = Sizes::width.  

 

Your help is very much appreciated.

 

Can you tell me, if I wanted to have a size override, how would I do that?  What I mean is that the user could choose a size from the sizes table, or else they could select, say, Custom Size, and if they selected that, then on the products layout they could enter a height and width that would be stored in the products table.  And if they selected any other size, then the size would be copied from the sized table into the products table.  So that the products table always contains the correct height and width, but the sizes table is just used to look up common sizes at the time of entry.  Does that make sense?  How would you do that?  

 

Thanks!

Asphyxia

[comment]

So that the products table always contains the correct height and width, but the sizes table is just used to look up common sizes at the time of entry.

 

"Lookup" is the operative word here. Filemaker has a built-in lookup mechanism to do exactly what you describe:

http://www.filemaker.com/help/13/fmp/en/html/relational.11.23.html#1033096

[Asphyxia1974]

Thank you so much - that's very helpful