Lookup from multiple tables

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Guy - 28 Jul 2008 15:31 GMT

How do I perform a lookup from multiple tables.

I enter a name on an enrollment form
I want address from table 2 or table 3 or table 4, etc.

Guy

Signature

-------------------------------------------------
Captain Guy
s/v Island Time (Beneteau 352#277)
AICW 845.5
386-689-5088

Reply to this Message

Martin Trautmann - 28 Jul 2008 16:20 GMT

> How do I perform a lookup from multiple tables.
>
> I enter a name on an enrollment form
> I want address from table 2 or table 3 or table 4, etc.

Do one field per lookup:
ft2 <- table 2
ft3 <- table 3
ft4 <- table 4

Then do a calculation on field ftx:
case(ft2, ...,
ft3, ...)

But why do you name the data structure as 'tables'? If you do use .fp7,
why do use use lookups at all, instead of calculations on the related
data itself?

case(
tab2::address, ... ,
tab3::address, ... ,
...)

HTH,
Martin

Reply to this Message

Grip - 28 Jul 2008 16:55 GMT

> > How do I perform a lookup from multiple tables.
>
[quoted text clipped - 21 lines]
> HTH,
> Martin

And why do you have addresses in different tables? They likely should
all be in one table.

G

Reply to this Message

Martin Trautmann - 28 Jul 2008 17:06 GMT

> And why do you have addresses in different tables? They likely should
> all be in one table.

Should they?

Personally, I prefer to keep data in different tables, according to my
needs. It's much higher speed, both for operations, for search (not much
in fact here), but especially for delete, replace, export, import and
backup.

For data exchange it is much easier to work on split databases (not only
tables) which may be exchanged by mail or be created on the fly (from
export/import data). Creating an empty table by "delete all records" upt
to .fp5 (FMP5/FMP6) was done in an instant. Deleting all records from
one ot the .fp7 tables may take ages.

BTDT - and it's more than annying to have an import of 1 GB running for
several days, just when it brakes at 95 %...

It's a good idea to think about flags and merging tables - but it's not
good to do it every time, instead of thinking about how to split data
properly to different tables.

- Martin

Reply to this Message

Greg Dember - 28 Jul 2008 17:21 GMT

> If you do use .fp7,
> why do use use lookups at all, instead of calculations on the related
> data itself?

This caught my eye. I have wondered, I guess for several years now, why
I should continue using lookups, rather than calculations like you
suggest. However, I have dutifully continued using my lookups. Is the
standard now that lookups are just a legacy function?

Is there any reason to use them now, except that in some cases they are
simpler?

Thanks

Reply to this Message

Martin Trautmann - 28 Jul 2008 21:32 GMT

> However, I have dutifully continued using my lookups. Is the
> standard now that lookups are just a legacy function?
>
> Is there any reason to use them now, except that in some cases they are
> simpler?

Some people claim that there is no more use for them.
Personally, I prefer them on occasions, such as

- data from remote files: relations may get lost when files are
moved/deleted/renamed. lookuped values will be kept.
Relations are better while taking data from tables within
the same file

- lookups provide simple mechanisms to use next best matching values.

- lookups are good in order to keep values, while related data may
get changed.

There's not much need to change working lookups. But there's no need to
find workarounds as shown within your problem, where relations are
suited much better.

- Martin

Reply to this Message