Merging Multiple Fields from Different Tables into Single Field on Layout

[fred_smith]

Hi,

Hopefully someone can help me of point me in the right direction.

I have a database with multiple tables. All tables are linked via a 'unique ID' in the relationships graph to a master unique ID table (Table 3 below).

Ex

Table 1

======

name

address

city

zip

info1

info2

unique_id

Table 2

======

name

address

city

zip

info3

info4

unique_id

Table 3

======

unique_id

In some instances, there is overlap/duplication in the data in the tables (eg., name/address/city/state is the same), in other cases the data might be similar but is slightly different (eg., address in table 2 might be written differently or have information missing from fields).

I'm looking for a way to create a Layout that 'merges' the fields from the 2 (or more)different tables above based on certain criteria so I can have ONE master layout view. So this Master Layout would look like:

Name------------->Pull the information from either table 1 or table 2 depending on criteria (eg., if field is blank in one table but populated in another take populated field, if both fields are populated just take first one)

Address------------->same as above

City------------->same as above

Zip------------->same as above

Info1

Info2

Info3

Info4

Thanks in advance,

Fred

[bcooney]

Your setup looks completely wrong. Why do you have such redundant tables? Info1, info2, etc. is typically a sign of a poor data model.

If you want, describe the data that you're dealing with in a paragraph, and we'll offer you assistance in defining the data model. For example, "Each person can have several xx.."

[fred_smith]

Hi,

Many thanks for offering your help. I have some old mailing lists with different source data.

I am trying to integrate these lists(tables in the DB), so if someone is at the same address in any of the tables

I have a way of viewing the info from the corresponding tables(in a layout). It's not perfect, by I found that by using a unique ID which

I have generated by combining the first 5 characters of the address,city and zip (then dropping spaces)

I was able to group most addresses successfully (and not have the problem which prevents de-duping

such as extra spaces or parts of address shown differently (as below in Street vs. St.). Table 3 is just a list of the

unique_ids

Below are a couple of examples of the data in the tables. Info1 and Info2 were meant to be in both tables - so ignore my previous

message where we have info3 and info4

What I'm looking for is a way to view information from both tables but use criteria in the layout mode of filemaker to choose

which fields to pull from

In the example below, I'd want to view information from Info1 in table2 as it's a longer entry in terms of characters. For info2 I'd want

to view the information from table1. And I'd like to view these in a single field in the layout view if possible.

Hopefully this describes what I'm looking for more clearly. Thanks in advance!

Table 1

======

name:William Smith

address:1234 Main Street

city:Los Angeles

zip:83882

info1:Yes...contact

info2:medium_prospect

unique_id:1234LosA83882

Table 2

======

name:Bill Smith

address:1234 Main St

city:Los Angeles

zip:83882

info1:Customer has discussed their need to renew our service. They would like contact on the following number:###

info2:

1234LosA83882

Table 3

======

1234LosA83882