Calculate relationship

[MacB]

Hi there..

I am trying to do something but have no idea if it can be done LOL.

Is it possible to calculate the relationship between 2 tables?

[color:red]table1: mainrecords, products, product_ID and the targetfield where the path to an image should come.

[color:blue]table2: images with product_ID, pathfield AND "imagelabel"( "img_main, img_back" etc).

There is a relationship between those tables based on product_ID. Now I want to add another rule to this where the name of a field in table 1 corresponds with the content of the "image-label" field in table 2.

Something like this: the field "[color:red]img_main" of record "1234" in table1 = [color:blue]imagepath from table 2 where [color:red]product_ID=[color:blue]product_ID and the name of the field "[color:red]path_field" (table1) = "[color:blue]imagelabel (table2)

Hopes this makes sense...

[mr_vodka]

I am not sure why you would want to base your relationship based off the field name that you would key off of.

Instead why dont you key off a global text field on the parent side to the imagelabel field. This way it can be dynamic based off what value you set the global field to.

product_ID = product_ID

gPath = imagelabel

[MacB]

hmmm It's complicated( for me doh..) so here the real world data:

table 1 has 25 imagefields (max), each field holds it's own type of image: mainimage, backimage, drawing, detail etc. Keyfield is 1234_ so SKU is 1234 and all images start with 1234_ followed by "main", "back", "detail" etc.

In table 2 there are, let's say, 10 images: 1234_main_tif, 1234_back_.tiff, 1234_detail1_.tif etc.

Now i want those images show in the corresponding product-record (1234_) AND in the appropriate imagefield: the main-image field, the back-imagefield etc. I figured i use the part in the imagename ("back") to link to the fieldname for the back -images.

[mr_vodka]

Well there lies your issue to begin with. You really should only have one table of images with two fields; one container field that stores the image and another field with the type of image (main, back, detail, etc) Having multiple image fields like such is not really the way to go.

[MacB]

That is the case: one (image)table. Thing is however that the MAINTABLE is used as an ODBC datasource for indesign and I only want to use ONE table with this. This means that the path/image in the MAIN-table is calculated (based on relationship). So the main key is the SKU (1234_) BUT I want the related images to end up in a fixed, appropriate field: backimage in the field with the name "back", detail1 image in the detail1 field etc.

[mr_vodka]

I am not following. Earlier, you stated that you had table 1 and table 2, now you are stating that there is only one image table.

[MacB]

There are 2 tables, as stated in my first post:

1. the maintable is the "work" table with product-info, text AND the path to all related images.

2. the (image) table holds all the images (10000+) and has a relationship with the main-table based on the sku (productcode and the imagename-beginning: 1234_ and 1234_.tiff

A product can have up to 20 images, all different in category/type. There is a "main" image but also pictures of the back, tech-drawing etc. Of course the SKU is the main relationfield: all images that start with 1234_ are listed in the main-table.

[mr_vodka]

I guess this is where I am missing you. Your table for images should have all the data pertaining to the image within that table itself, not in the main table.

table 1 has 25 imagefields (max), each field holds it's own type of image: mainimage, backimage, drawing, detail etc. Keyfield is 1234_ so SKU is 1234 and all images start with 1234_ followed by "main", "back", "detail" etc.

In table 2 there are, let's say, 10 images: 1234_main_tif, 1234_back_.tiff, 1234_detail1_.tif etc.

This should not happen. Both those 25 images and the 10 images should be in one table.

[comment]

To do exactly what you describe, you would need to have 20 (or is it 25?) calculation fields in the Products table and 20/25 relationships to individual occurrences of the Images table. Then you could either place 20/25 related image fields on the Products layout (each from a different relationship), or - if the fields must belong to the Products table - add another 20/25 calculation fields with the result set to Container, each being equal to one of the related images.

If the image names and paths are consistent, you could simplify this significantly by calculating them directly in calculation fields (with Container as the result). For example, if you know that the main image of product 1234 must be named "1234_main.tiff", you could make the calculation field =

"imagemac:/" & << path to images folder/ >> & ProductID & "_main.tiff"

This would fetch the image into the calculation field directly - even without any relationship between your two tables.

[El_Pablo]

Sorry to open this old thread, but I didn't to start a new one since I have the same kind of problematic on my side.

So the best way should be to create all the fields as global in the main table?

Where the relationship is between the global fields and the product id.

Example :

Products (pkProductID, ..., gImgBack, gImgFront, ...)

Images (..., fkProductID, imgLabel, image, ...)

Wouldn't this be a mess in the relationship structure?

[comment]

I am not sure what you are referring to. The first part of my post was certainly not a suggestion - only an explanation of what it would take to do it that way. The second part uses no relationships (and no global fields, either).

[El_Pablo]

Let say that there's a house table and an image table.

For each house, it's mandatory to have at least the front picture, the neighbourhood picture and the map of where it is located (eg: Google map printscreen).

Sometime depending of the case, there is a need for photos of the kitchen, the living room and the bathroom.

The photos are 1-to-(0,1) relationships. There could be a single kitchen photo, but not always and never more than one.

There's also a possibility of adding other photos that are categorized as general. In this case, it's a 1-to-many relationship.

What would be the best practise to model the tables?

I will attach a file in the next post

[El_Pablo]

Here's my example. Is it ok?

I think there's a more efficient way to create the relationship structures.

houses.zip

[comment]

You could put the mandatory photos directly in the Houses table (with the penalty of not being able to manage all photos centrally). Or you could use a left outer join (which in Filemaker requires either event-triggered script or scripted navigation). Or you could just leave it as it is (though I would use unstored calcs instead of the globals).

[El_Pablo]

I don't want to create unnecessary containers in the house table since I don't know how much space is allocated for unused containers.

If I leave it like this.

Which one is more efficient? Global fields or unstored calculation? What is the difference?

[comment]

I don't know how much space is allocated for unused containers, either. Why would it be necessary to allocate any space for this?

Your global field is stored and re-evaluated every time a new record is created. An unstored calculation holds just the formula and is evaluated on screen refresh. Not much of a difference in practice, it's just neater IMHO.

[El_Pablo]

Okay thanks for your suggestion!

As for the space allocated for containers, I guess that they don't work the same way as text, number and date fields.

In SQL, each type is allocated a space even if it's null (eg: INT takes 4 bytes). So I thought that containers were working the same way.

[comment]

All I know about this is that Filemaker is not SQL.