Looking up from multiple databases

[iMarcW]

I have a shipping database where I want to be able to pull recipient address information from any of several different databases (different types of clients, etc.). Each of them has different types of keyfields, and I was hoping to have a single foreign-key recipient ID field in the shipping table to store whichever one was looked up, so it could be used in the client databases to display related shipments in a portal.

I can imagine having 3-4 separate "picker" menus that pull the key into the same recipient ID field but use different value lists based on separate relationships. But for the address/city/state/zip fields in the shipment table that I want to populate with the looked-up values, I can only specify one relationship for the lookup.

Is there a kind of relationship that can join these tables into one for the purposes of looking up, or would it be better to have separate sets of ID/address fields in the shipping database for each related table, and use a calculation to decide which set of fields to use for the shipping info.

I hope I am making sense here. If this has been covered elsewhere, feel free to post the link. Thanks.

[MarkWilson]

Back in 5.5 I built a proof of concept file that pulled address data from two sets of fields, home and work, into default address fields.

I used a radio button value and a GetField() function to put the field name together for a calculated source data value. I then used a lookup to set the default address fields. (All the address field where in the same table.) In theory, the same thing could be done to put the full calculated reference to the other databases together and a lookup to set the data.

This topic comes up often. Is there a reason why all the contacts cannot be in the same table with a type of contact field?

[iMarcW]

In theory, they could (and should) all be together in one database, but the complexity of making sure people only saw applicable records in particular contexts with a setup like that is a little beyond the scope of someone like me who only does this as 5-10 percent of his job.

Your concept sounds intriguing, but I think what I'm going to do is to use separate buttons, scripts and "picker" windows to establish a match with the related record, then use a series of "set field" script steps to get the related-table information into the native address and foreign key fields.

[Fenton]

Personally I think creating separate tables for different "types" of clients is not a good idea. Unless almost all of the fields are different, and all operations are different, it is likely in the long run to create more problems than it solves. You say that showing the proper set of records is beyond your skill level. So you decide instead to break up the relational structure.

Well, I wouldn't. But if you must. It is possible in later versions of FileMaker to create an auto-enter calculation which can lookup from 2 or more locations, in cases where the first may fail, but a 2nd may not. You may want to uncheck the [ ] Do not replace existing contents.

Lookup ( relationship1::field; relationship2::field; relationship3::field )

Or, if you just want to show the related field on a layout you can either:

1. Stack all the related fields on top of each other, fill transparent; only one of them will be valid and show.

2. Create a calculation field, similar to the Lookup.

Case (

not IsEmpty ( relationship1::field ); relationship1::field;

not IsEmpty ( relationship2::field ); relationship2::field;

not IsEmpty ( relationship3::field ); relationship3::field

)

The context, the current layout's table occurrence, would need to tie to all 3. But this would normally be true.

The real downside of this structure is that the above would need to be considered every place that it was used, every related field that was used. Any extensions of functionality would need to include this.

The IDs of the client tables must NEVER overlap. They would need a unique prefix or something. Because you will be putting them (one of them) into a single ID field in other tables, and it will not be able to tell the difference otherwise; simple numbers will not do.

Personally I think I'd rather keep it all in one table and help the people see the records they want.

[MarkWilson]

Could a few buttons or a field with a value list and a button be set up with the types of clients. Attached to the button is a find script to perform a find for a type of client or a find to enter find mode, set the ClientType field with the value list selection, and perform the find. Show all records would need to be removed from the menu set and you would need to perform the find to show the type of client when ever the user came to the layout. Would that not be an easier with all the contacts in one table various contacts in various tables with various layouts.

I have some contacts that are both vendors and customers of mine. They can remain in the same single record for dual purposes.

[Fenton]

Basically this is one of the downsides to separating tables. You've got 1 field you want to populate. You want to choose from 3 tables. You can only attach 1 value list to a field.

So, you could put the field on the layout 3 times. Then you could attach the 3 value lists. You could have a global radio button field to say what the "type" is, then have a script go to the instance of the field which has that value list. Of course in 8 you can't tell it which one. So you'd have to go to the 1st, then go to the next. PITA. With 8.5 you could give each an Object name, then go to it.

Like I said, separating the tables causes more work. With 1 table, there could be 1 filtered value list, so the radio button would filter it to the type chosen. Less muss, less fuss.

You could also consider pop-up windows (new window), if you've got a lot to choose from. We don't know that.

Once again, 1 table would be 1 window, 3 tables would have to be 3 windows, or 3 tabs with different portals.