Correct relationship for calc field (Lookup function)

[Dmytro]

Dear all!

 

I have 2 tables: Companies and Ownership. 

The first contains fields: Comp_Code ... Country ... etc. 

The second contains: Comp_Code, Mother_Company ... Country. First 2 fields lookup company names from the first table (Companies). Country field in the second table is calc field and must lookup country name for Mother_Company based on Country assigned to a company in the 1st table. 

Can you advise on correct relationship between both tables and proper Lookup function script for my calc field? 

Thank you.

 

Example below:

 

Table 1: Companies

Comp_Code    …    Country    …

AAA                         USA        …

BBB                 …    USA        …

CCC                …    CAN        …

DDD                …    CAN        …

 

Table 2: Ownership

Comp_Code        Mother_Company    Country*

AAA                              BBB                USA

BBB                             CCC                USA

BBB                             DDD                CAN

 

Layout: Ownership

 

Company:                         BBB

 

Mother companies:        CCC            USA

                                       DDD            CAN

 

 

* - calc field using Lookup function

[bcooney]

This may be helpful: http://tdan.com/a-universal-person-and-organization-data-model/5014

[comment]

First thing: are you sure you need a lookup here?

A lookup copies the data from the lookup source (Companies::Country) into the lookup target (Ownerships::Country). The idea here is that if you change a company's country later on, the ownership record will not be modified and will continue to store the country of the related company as it was at the time when the lookup was performed. Judging from the nature of your data, I don't think that's what you want.

Re relationships, you will need (at least) two:

Companies::Comp_Code = Ownerships::Comp_Code

and:

Ownerships::Mother_Company = MotherCompanies::Comp_Code

where MotherCompanies is an occurrence of the Companies table.

With this in place, you can place a portal to Ownerships on a layout of Companies to show the companies that own the currently viewed one. Place the MotherCompanies::Country field inside the portal to show each owner's country. The Country field in Ownerships can be deleted in this arrangement.

 

 

[Dmytro]

Thank you very-very much!

This works indeed.

I am using FM Pro for the second day only, didn't get used to it so far. Before used Access all my life. It differed a bit for that kind of tasks

[bcooney]

I also wonder what the definition of Comp_Code is, Dmytro. Be careful about using meaningful values for record keys. You're better off using an auto-entered serial or Get (UUID).

[Dmytro]

Comp_Code is naturally Company_Code.

In my business I control around 50 companies. All have long names in different languages. I use Comp_Code to store an alias with 3-8 symbols, and it serves me as a key field. Company full name is stored in separate field for description purposes.

Comp_Code is rather meaningful to me as I know all my companies by heart and do not plan to have them much more in this concrete database.