Jump to content
Sign in to follow this  
ericb

Urgent help with lookup lists

Recommended Posts

ericb

Hi all

 

I am new to Filemaker, comming from an Access, MSSQL background. I was wondering about creating drop down lists related to another table. In access for instance, you would create the drop down list using a relationship between the main table and the lookup table, the main table would then store the id number of the lookup value.

 

Is this the same in Filemaker or is ther another way to do it. If this is how, then how do you have the lookup table related to two diffent tables via relationships (i.e. Suburb field in clients and staff table).

 

Many thanks for any help.

 

Eric

Share this post


Link to post
Share on other sites
Maarten Witberg

I'm not sure whether you are using the filemaker meaning of "lookup". A lookup is based on a relationship but values are copied from fields in the related file to fields in the current file at record creation. That means if values change in the related file, they will not be updated unless you give fm orders to do a relookup.

You can also choose to display fields from the related file into the current file. In that case, the fields are automatically updated when they change in the related file, and this works both ways: if you change a value in a related field in the current file, it is updated in the related file.

 

If you want related fields or lookup fields from two files in a third one, you must define two separate relationships. related fields or portals only show records from one relationship, that means one other file.

I've never tried to use one drop down list for two relationships, it can probably be done but I don't immediately see a rocksolid way to do that.

 

but you want to see a match between clients and staff that live in the same neighborhood and display that match in a third file?

what's the third file for?

 

you work with v6 or v7?

the principles, as i understand them, are the same for both versions but the setup is different.

if you use v6 I can probably help you. let me know.

 

smile.gif

 

PS [edited post] I only just now see that you posted in the fmpro 7 forum. Well then, I must assume that it's seven you work with. I hope I got you under way.

Share this post


Link to post
Share on other sites
ericb

I am using v7

 

Typically I would have one table for clients, one for staff and one for suburb, the suburb (id and suburb fields) table would then be related to both the clients and staff table and provide a lookup for the other two and then store just the id field of the suburb table in the other two tables.

 

The only way I can see of doing this is to create a staff, client and suburb table, have no relationship between either the staff or client tables and the suburb table and use the suburb table to form the basis of a value list that can be selected on the layout from an edit box.

 

I hope that makes sense

 

Thanks

 

Eric

Share this post


Link to post
Share on other sites
Maarten Witberg

you could make two portals in the suburb table, one showing clients and the other staff.

one record = one suburb.

i'm guessing the clients live in suburbs and staff gets assigned to suburbs yes? so the client to suburb relationship could be on a postal code match.

can one staff member be assigned to more than one suburb? you'd need a checkbox field format rather than a dropdown list for that.

 

smile.gif

Share this post


Link to post
Share on other sites
ericb

No the suburb is there to simplify the entry of data and to stop errors in data entry. I suppose it is there to create a dynamic value list that needs to be used on more than one table

Share this post


Link to post
Share on other sites
Maarten Witberg

so the suburb table would be just suburb names and an ID?

if that's it, why not make a value list in the staff table based on suburbs the clients live in? you can do that by defining a value list in the staff db that takes its values from a field in the clients db. no relationship necessary.

 

otherwise, I'm afraid you've lost me.

 

confused.gif

Share this post


Link to post
Share on other sites
CobaltSky

Hi,

I believe that the issue here is that although a FileMaker value list can be defined to use values from a field - in the same or another table (even in another file), there is no obvious way to define a value list to simultaneously present a combined list of values from target fields in two or more related tables.

 

On the face of it, that is a limitation with the default value list options that FileMaker provides, I'm afraid. However If you want to proceed, there are at least a couple of options.

 

One would be to prepare a script which retrieves the relevant values to populate the list on the fly each time the pop-up is called. This will require a bit of additional work in the set-up and will not readily work with a tab-through data interface (ie unless you are using an appropriate plug-in, the user will have to click on the relevant field in order to launch the script).

 

Another option would be to reconsider your structure and opt for a model where all the suburbs from all tables are stored in a single suburbs table (via relationships from other tables such as your clients and staff tables). In this model, each record in the staff table and each record in the clients table would have a 1:1 relationship to a corrresponding record in the suburbs table. This arrangement can be made to work automatically and seamlessly (ie so that as soon as a suburb is entered on the layout for either table, the required record in the suburbs table is automatically created and populated). Then the value list of all suburbs for both/all tables can easily be drawn forth from the suburbs field in the table.

 

Either approach will give you a combined list of suburbs entered in both related tables but, as you can see, the approaches are divergent. Not uncommonly FileMaker will permit a variety of approaches to any given challenge. wink.gif

Share this post


Link to post
Share on other sites
ericb

Sorry I may not have been clear. But the second option that CobaltSky is suggesting is kind of what I am after, the only difference being that the list of suburbs is set and can only be added to when an administrator adds to the suburb table (to stop user just entering any old suburb that like). The problem I have with this is I cannot create a relationship between the clients table and the suburb table, and the staff table and the suburb table without creating a duplicate of the suburb table. Just to note both the staff and clients tables are linked to other tables in the database.

 

I think it may be me that has stuffed it up.

 

Thanks for everyones help to date, it is very much appreicated.

Share this post


Link to post
Share on other sites
CobaltSky

Hi Eric,

You don't need to create a duplicate of the table itself, just an additional reference to it in the relationships graph.

 

The table objects in the graph are named pointers to the underlying tables - often called 'table occurrences' (TOs). You can have as many of them as you like on the graph all pointing to the same underlying table. The reason for this is it enables you to set up different relationships to each TO and then, from elsewhere in the application, specify which 'context' you want to use to reference the data in a given table, by choosing the name of the appropriate TO.

 

In thgis case, there should be no problem in adding a further TO of the suburbs table so that you can reference it from more than one other table. Think of the TOs as being like file shortcuts or aliases... wink.gif

Share this post


Link to post
Share on other sites
ericb

Ray

 

Just to make sure I have got this correct.

 

I create my three tables (client, staff, suburb), in the staff and client tables I have a field for suburbid.

 

I then create a relationship between suburbID of the suburb and suburbID of the clients table.

 

When I got to create the same relationship between the suburb and the staff table I get a message telling me that I can only have one relational path between any two tables in the graph and asks me to create another occurance of one of the tables. This occurance still refers to the original table and doesn't make a copy of the same table (two different tables with the same fields)?

 

Also with this type of thing am I better to use the table to populate a value list or as a lookup? Typically I would use the suburb table to add the id to the clients or staff table to reduce the size of the database, if I use the suburb table as a lookup am I not just storing the suburb text in the client/staff table and therefore not saving any space?

 

Thank you for your help Ray, it is kind of unnerving moving out of your comfort zone and onto a new application.

 

Again many thanks, you have been a big help.

 

Eric

Share this post


Link to post
Share on other sites
This thread is quite old. Please start a new thread rather than reviving this one.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  



×
×
  • Create New...

Important Information

Terms of Use