I am using FileMaker 5.5 for Mac OS. I have a question regarding
relationships.

Database 1

Col_1 Col_2

123 10
123 20
123 30
456 10
456 20
456 30

Database 2

Col_1 Col_2 Col_3

123 10 123-10
123 20 123-20
123 30 123-30
456 10 456-10
456 20 456-20
456 30 456-30

I am converting my current accounting system. In the process I need to
change my account numbers. The first database is the structure of the
old accounting system. Column 1 is the account number, and column 2 is
the department. The second database is the structure for the new
accounting system. Column 1 is the old acct number, column 2 is the old
department number, and column 3 is the new account number structure. It
appears I can only relate one column to one column. But, I want to
relate column 1 of each database and column 2 of each database to get
the new result of column 3 from the second database. How would I
accomplish this with FileMaker 5.5?

Thanks for any help you can offer me.

Jeff

Jeff,

You could add a calculation field to "Database 1" as follows:

Col_1 & "-" & Col_2

(text result)

Which will enable you to relate now with Col_3 in "Database 2"

Bill

(Note: when posting to newsgroups, try to make your subject unique. Your
post was buried in a discussion thread from March 2004 because it matched a
previous subject line.)

"Jeff Grossman" <jeff.nospam (AT) stikman (DOT) com> wrote

Bill Marriott <wjm (AT) wjm (DOT) org> wrote:
That won't quite work for me. I tried to be over simplistic in my
example. Hopefully I can be a bit more clear.

Database 1

Column 1 Column 2 Column 3
000-123-10 10 Lookup col_3 in database 2
000-123-10 20 Lookup col_3 in database 2
000-123-10 30 Lookup col_3 in database 2
000-456-10 10 Lookup col_3 in database 2
000-456-10 20 Lookup col_3 in database 2
000-456-10 30 Lookup col_3 in database 2

Database 2

Column 1 Column 2 Column 3
000-123-10 10 1231-10
000-123-10 20 1231-20
000-123-10 30 1231-30
000-456-10 10 4561-10
000-456-10 20 4561-20
000-456-10 30 4561-30

Database 2 has my old structure and my new structure. In database 1 is
all of my data that I need to import into the new accounting system.
But, I need to replace the old acct_num and dept setup with the new
acct_num which has both combined, plus some characters removed. I do
not know how I can relate the two databases together.

I guess I might just need to do a calculation in database 1 instead of
trying to relate to a second database for the information. I thought
doing the relation would have been easier.

That seems pretty weird. I guess your newsreader does not use the
references header to figure out threading.

I guess I'm still confused. According to what you described both tables
should contain exactly the same info in all three columns. Or is it that you
can't get the lookup to work?

If your goal is to eliminate Database 1, then couldn't you just import,
using the "update matching records" option?

In either case, since the old sequence numbers and new sequence numbers
follow a pattern, it is easy to calculate one from the other. All you need
is an identical field in both tables to relate them.

Bill


"Jeff Grossman" <jeff.nospam (AT) stikman (DOT) com> wrote

Bill Marriott <wjm (AT) wjm (DOT) org> wrote:
from both database to relate to each other. The only unique part is
col_1 and col_2 put together. Col_1 has many duplicate items in each
database. But, when you add col_2 to that, then they become unique.
But, it appears I can't have two relationships to figure out the correct
record I want in database 2. I will have to figure out another way.

Thanks for the help.

Jeff

Jeff,

This is kind of going around in circles.

As you are stuck in FileMaker 6, you need a single key field to use as the
basis of a lookup. (FileMaker 7 and 8 would let you define multiple
criteria, the two fields you want.)

Therefore, combine field 1 and 2 with a calculation to create the unique
field you need. Why wouldn't this work?

Bill


"Jeff Grossman" <jeff.nospam (AT) stikman (DOT) com> wrote

Bill Marriott <wjm (AT) wjm (DOT) org> wrote:
Thanks for the information. I will see what I can make work.

I guess the main issue I have is that in FileMaker 6 I can only have one
key field for the relationship.

Jeff

Why not admit, as other said, that :
- in FM7, you have 2 key fields "A" and "B"
- in FMP6, you create the one key field C = A & "/" & B
and you achieve the same results.
Couldn't we talk of something else, now ?
Remi-Noel


"Jeff Grossman" <jeff.nospam (AT) stikman (DOT) com> a écrit dans le message de
news: herh73x3u9.ln2 (AT) stikman (DOT) com...

In article <herh73x3u9.ln2 (AT) stikman (DOT) com>, jeff.nospam (AT) stikman (DOT) com says...
As Bill has suggested at least twice now: defining a calculation as:
fieldA & " " & fieldB and using that as the key field is virtually
equivalent to having "actual composite keys".

(I say 'virtually equivalent' because there are some corner cases that
can require some forethought, but to all intents and purposes you can
use field concatenations in calcs as composite keys.)

-regards,
Dave

"Remi-Noel Menegaux" <rnmenegaux (AT) free (DOT) fr> wrote

No; I don't mind asking questions/making suggestions until there is clarity
achieved. In fact, I prefer it to just letting things dangle. After all,
we're here to help. The traffic has been so light the past couple days, I
don't think it's a huge distraction. If it is, just skip it.

I'm going to go over what I understand and what I recommend one more time.

Jeff has given a specific example of how the two tables look right now:

The old accounting numbers and the new accounting numbers seem to be
consistent, and given one you can determine the other. Maybe he is looking
for this....

The exact calculation to add to "Database_1" is:

Column_3 (calculation, text result) =

Middle(Column_1,5,3) & Middle(Column_1,9,1) & "-" & Column_2

This will return the "new" accounting codes based on the old ones.

Jeff writes,

Now that you have "Column_3" in both databases, you may establish a
relationship between Database_1::Column_3 and Database_2::Column_3. That
relationship can be used for lookups in either direction, or for portals,
etc.

However, depending on what you neeed moved over and what already exists in
Database_2, it might be better to do an Import, using the "Update matching
records in found set" option, where Column_3 = Column_3.

It would be possible to create a relationship based on two matching fields
in FileMaker 7 and 8, but impossible in FileMaker 6 and earlier. A good
reason to upgrade.

Good luck

Bill