Jump to content

Correct relationship for calc field (Lookup function)

This topic is 3053 days old. Please don't post here. Open a new topic instead.

Recommended Posts

  • Newbies

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

Link to comment
Share on other sites

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


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.



Link to comment
Share on other sites

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).

Link to comment
Share on other sites

  • Newbies

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.

Link to comment
Share on other sites

This topic is 3053 days old. Please don't post here. Open a new topic instead.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
  • Create New...

Important Information

By using this site, you agree to our Terms of Use.