Relating duplicate tables

[alder2009]

Hi everyone,

I have been using filemaker for about 6 months now and have created a valuable tool for our business.

I have a table containing customer accounts.

I have another table containing branches. Every branch record has a related customer account record. There maybe say 8 branch records all with the same customer account ID.

A record in the customer accounts table will always have a related record in the branches table with exactly the same information.

The customer accounts records are used for orders and financial information whereas the branch records are used for marketing and CRM information. The only common fields between them are address lines, city, county and postcode.

Clearly, there is duplicate information here which in itself doesn't seem correct.

When making a change to a field in the customer accounts, I have created a script to change the data in the related branch record. I use variables. The script knows what branch record to edit because of a yes no field I have created in the branches table. "Yes" indicates that the branch is linked to the invoice, "no" indicates it is a standalone branch and is not to be edited.

Is there a more efficient way of doing this? Thank you.

[eos]

Hi alder,

welcome, and nice to hear that you can put FileMaker to good use.

May I ask what's the reason for maintaining duplicate information, and for using two tables instead of one? If every Customer Account record has exactly one Branch record, and vice versa (as per your description), one table would (under most circumstances) be all you need.

[LaRetta]

Every branch record has a related customer account record. There maybe say 8 branch records all with the same customer account ID.

Hi Alder2009, I agree with eos that you are correct to question the redundant data. But from your description, you actually have a one-to-many (1:n) relationship if multiple Branch records hold the same Customer Account ID. Do you have them joined using equal (=) in your graph? It should look like this:

Customer Accounts::Customer AccountID = Branches::Customer AccountID

If the tables were joined then you could delete the address lines, city, county and postcode from Branches and just take those fields from Customer Accounts and place them directly onto any Branch layouts. This is the power of relational - the ability to cross-place related fields - all related data is instantly available through the relationship. :^)

[LaRetta]

UPDATE: To be clear, there may be times you will want duplicate data. For instance, a shipping address on an invoice should be planted instead of just cross-placing the customer's address fields because if the customer moves later, you want to know their address at the time of shipment. In that case, you still use the relationship but instead you use an auto-enter or lookup of the Customer Account data. Script would be unnecessary regardless once you have the relationship in place.

OH!! And how rude of me! Welcome to FMForums!! There are many wonderful folks here all willing to assist. :^)

[alder2009]

Thank you for the replies and the warm welcome.

Each Account record has a unique ID. Each Branch record has a reference to an account ID. Many Branch records may be related to one Account record. It's a one to many relationship. This is simple enough.

The problem I have is that in addition to this relationship, one Branch record needs to be related to one Account record. (One to One).

An account (i.e. a company headquarters) will have many related branches (i.e. different branches of that company). However, one of the many related branches will be the company headquarters itself. I have taken your advise and created and additional relationship between these tables. A one to one relationship based on a unique ID in the two tables.

If I edit a field in the Account table, how can I automatically update the 'one' related field in the branches table? And vise versa.

[eos]

You could add a field to the Branches table like "HQ", "Main Branch" or the like. Make it a number field (it will just hold a 1 or be empty; the meaning is in the field name). Create a value list which just has a custom value of 1. Put the new field on a Branches layout (or in a portal of a Branches TO) and format is as a checkbox with the "1" value list. Check the branches which are HQs.

Now create a calculation field, global storage in Accounts with the result of "1". Create a new relationship from Accounts to Branches, with AccountID primary and foreign key as one pair of matches, and the global calculation in Accounts and the "HQ"field as the other pair. There is your 1-to-1-relationship which for each Account finds the one “HQ Branch”, and which you can target with Set Field to update specific fields, e.g. with a field trigger. Another use is highlighting the HQ branch in a list of all branches with Conditional Formatting.

Note that you can re-cycle this global calculation field and the value list whenever you need to create similar relationships. Also, don't forget that you need to make sure there is at most one Branch record marked as HQ for an Account.

[LaRetta]

"The problem I have is that in addition to this relationship, one Branch record needs to be related to one Account record. (One to One)."

"However, one of the many related branches will be the company headquarters itself."

It seems this is similar to when a company has many addresses but needs to select ONE address as its headquarters address. In this instance, you would create a field in Accounts maybe called MainBranchID. To it, you attach a pop-up and specify a conditional value list called Account Branches as follows:

Select 'values from field' and select Branches in the pop-up on the left and specify the BranchID in the left pane and in the right pane, select the branch description. Then below, specify 'include only related values' and select your Accounts table and check 'show values only from second field.'

Now when viewing an Account, you can select (from only Branches related to an Account), the headquarters branch. It will insert the BranchID into the Accounts::MainBranchID field. Then your relationship for displaying the headquarters information will be:

Accounts::MainBranchID = Branches 2::BranchID <--- note Branches 2 is a second table occurrence of Branches

Place fields from Branches 2 directly onto your Accounts layouts.

Edited June 15, 2012 by LaRetta