Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.filemaker > #2956

Re: Using Data From an Unrelated Table

Show all headers | View raw

On 2018-01-13 19:45:17 +0000, Charles H. Sampson said:
> 
> In another post, I described a problem I'm having with two portals into
> related tables. This is not the main problem however.
> 
> What I really want to do is access data from an unrelated table in a
> layout. That is, the layout is tied to table A. In that layout, I want
> to use data from table B (along with data from table A, of course). The
> data in table B are there and I simlply want to use them. That seems
> reasonable to me.
> 
> To try to clarify, suppose that table B contains the names of states and
> their capitals. In a layout for table A I want to use some state/capital
> combinations, say states where the businesses of Table A have a
> presense. I know that that can be set up through  relationship, but this
> would be a many-to-many relationship and that opens up a can of worms in
> FMP8. So here's another question: Are many-to-many relationships handled
> better in FMP16 than in FMP8?

The usual way to obtain data from a different Table is via a 
Relationship. The one exception I can think of is when using a Value 
List, which can take it's values from a Field in another Table ... but 
that lists the Field data from ALL the other Table's records.

For your example, you would have two Tables:

  StateCapitalsTable
     State             Text
     Capital           Text

  BusinessTable
     BusinessName      Text
     ContactPerson     Text
     etc. (other fields for the business dat, such as phone number)
     BusinessState     Text
     BusinessCapital   Calculation   = rell_StateCapitals:Capital


You would also need a Relationship link to look up the BusinessCapital 
based on the data in the BusinessState Field:

   rel_StateCapitals
      match records BusinessTable:BusinessState to StateCapitalsTable:State

and a Value List defined for the BusinessState Field so it can list all 
the States to pick from:

    valuelist_AllStates
       use value from Field StateCapitalsTable:State

On a BusinessTable Layout you can put all Business data fields and set 
the BusinessState to use the valuelist_AllStates (either as a pop-up 
menu or pop-up list).

Now, when entering BusinessTable, you choose a BusinessState from teh 
pop-up and the BusinessCpaital will automatically calculate its data 
via the Relationship.

You can of course not use the Value List at all and simply type in the 
BusinessState data (or use state codes to make that easier), but it 
does mean there's a possibility of a data entry typo causing a problem 
- e.g. "Oiho" instead of "Ohio".

Either way, you still need the Relationship to obtain the individual 
BusinessCapital data.

You could also avoid the Relationship entirely by doing the 
BusinessCaptial as another Value List as well, but for this example 
that would be a bit of a waste of data entry time. It makes more sense 
for the database to do that automatically, especially if entering lots 
of records

Helpful Harry  :o)

Back to comp.databases.filemaker | Previous | Next — Previous in thread | Find similar

Thread

Using Data From an Unrelated Table csampson@inetworld.net (Charles H. Sampson) - 2018-01-13 11:45 -0800
  Re: Using Data From an Unrelated Table Helpful Harry <HelpfulHarry@BusyWorking.com> - 2018-01-14 09:42 +1300

csiph-web