Relationships via Calculation match field 3 tables

[Pavel Smirnov]

I have 3 Tables

 

In a short way it looks like:

 

Objekt                                           ADDR                                        HOUSE

ID_ADDRESS                               ID_ADDR                                    ID_HOUSE

Name                                            Name (street or region)

 

 

Relationships:     Objekt::ID_ADDRESS=ADDR::ID_ADDR                         Objekt::ID_ADDRESS=HOUSE::ID_HOUSE

                            Objekt::Name=ADDR::Name

 

Objekt::ID_ADDRESS=

Case (

not IsEmpty ( Objekt::Name ) ; ADDR::ID_ADDR;

 )

 

_____________________________

So From table Objekt I can get records from related table ADDR by matching Calculated field (ID_ADDRESS)

But I cannot get list of records from related HOUSE by this field (ID_ADDRESS) that matched to HOUSE::ID_HOUSE

[Fitch]

Objekt::ID_ADDRESS is a calculated field? That doesn't seem right.

[comment]

Relationships:     Objekt::ID_ADDRESS=ADDR::ID_ADDR                         Objekt::ID_ADDRESS=HOUSE::ID_HOUSE

                            Objekt::Name=ADDR::Name

 

I am afraid this makes very little sense. Why don't you take a step back and explain what things these tables are supposed to track, and what are the real-life relationships between these things?

[Pavel Smirnov]

https://www.dropbox.com/sh/ipb1xrhmrw3z6nf/PKCoYI0taO

 

There are 3 files

 

My database is TestFias

FiasAddr - Region and street

FiasHouses - House Number for streets

 

In TestFias

ChooseRegion Москва

Street Тверская ул

 

And I need to choose a house from a list, from FiasHouse, but it doesn't happen.

Objekt::ID_ADDRESS is a calculated field? That doesn't seem right.

yes, it is

AOLEVEL: 1-regions; 3-districts; 4 - cities....

[Pavel Smirnov]

FiasAddr:

AOID - unique ID ;  ParentsID -Parents Record AOID

 

FiasHouse:

AOGUID - parens record in table FiasAddre AOID

[comment]

ChooseRegion Москва

Street Тверская ул

 

And I need to choose a house from a list, from FiasHouse, but it doesn't happen.

 

 

See if these helps:

http://fmforums.com/forum/topic/55459-automatic-zipcode-data-entry-help/#entry261760

http://fmforums.com/forum/topic/40834-clear-related-value-lists/#entry190602

[Pavel Smirnov]

Thank you, but the problem is that, there 3 tables. And every record must have Regions, than you choose district, city, and then street.

But you can choose Region, district will be empty, and you can choose city, street. There are will be different list of streets. 

And than you choose houses numbers.

I don't know how create relationships without matching by calculation field.

 

Why it's not right to use calculated fields in relationships?

[Fenton]

There is a difference between a field that is "unstored" (and cannot be indexed) and one that can be. A field which cannot be indexed (FileMaker will tell you so), can be used in a Relationship, but only as the "from" side; it cannot be on the "to" side of a relationship. Any field (including calculations) which can be indexed can be on either side.

[Pavel Smirnov]

There is a difference between a field that is "unstored" (and cannot be indexed) and one that can be. A field which cannot be indexed (FileMaker will tell you so), can be used in a Relationship, but only as the "from" side; it cannot be on the "to" side of a relationship. Any field (including calculations) which can be indexed can be on either side.

Thanks a lot, now I'm understanding how it works.

[comment]

Thank you, but the problem is that, there 3 tables.

 

It makes no difference, the principle is still the same: you make a selection and use an auxiliary relationship based on that selection to show the matching records for the next selection.

 

 

And every record must have Regions, than you choose district, city, and then street.

But you can choose Region, district will be empty, and you can choose city, street.

 

That's possible only if a city "knows" its region, in addition to (or instead of) its district. "Knows" in this context means a stored field - not an unstored calculation.

 

 

There are will be different list of streets. 

 

Not sure what that means.

[Pavel Smirnov]

Thank you for help. I made script triggers for each field, that put id into fields, and then made indexed calculated field with one of id that I need. Now I have related records on external files.