Conditional lookups/auto generation of join tables

[bdenny]

I am completely new to Filemaker Pro. I am more familiar with writing code to control an interface and then writing files as a consequence. This gives complete control.

 

I am creating a contacts/inventory/invoicing database. Contacts can be suppliers, agents or customers. I either need to have a type field, or join tables to identify the type of contact.

 

1) If I use a type field, how do I get a value list that includes only contacts of that type?

 

2) If I use join tables (one each for supplier, agent and customer which allows me to easily create value lists for each type), how do I automatically generate the join table records from the contact entry screen? I cannot see a way of attaching checkboxes that are not linked to fields or generated from value lists, that will allow the contact to be added to or deleted from the various join tables.

 

Any help that anyone can offer will be gratefully appreciated.

[LingoJango]

Hi,

 

For the answer to 1), search in the forums for "conditional value lists". The key element in the value list dialog box is "Include only elements starting from", where you decide the context from which the value list is obtained. In other words, your relationship graph will determine how the filtering happens.

 

I would only use separate join tables if you need to track a lot of highly specific attributes for each type of contact. Otherwise, you'll either need a lot of stored calc fields - so no space gains - or you'll end up needing to reproduce the same relationship several times over in different parts of your relationships graph, if it has any complexity.

 

A third option would be separate Boolean fields for each type of contact. Makes things simpler if a single contact can be both a customer and a supplier, for instance. Also, it's less language-dependent.

 

FileMaker doesn't have a Boolean field type setting; just make it a number field, create a value list called Boolean or One with a single value of "1", and require the field to validate as a member of this value list.

[bdenny]

Thanks for your speedy response.

 

I've searched conditional value lists, but can't find what I'm looking for. The condition expects a relationship with another table.

 

I want to set a contact type of say "supplier" on my contact. I then want to be able to attach this contact to a product table in a field called "supplier". I therefore want a dropdown or pop up on the supplier field on the product entry screen to select only those contacts with a type of "supplier". This seems like a very simple request, but I just cannot see a way to do it.

[LingoJango]

Thing about FileMaker is, you can have several instances of the same underlying table in the relationship graph. Hence, even if you don't have another table, you create a second table occurrence with a relationship to the first.

 

Here's an example of how to set it up using Boolean fields. You have a table called Contacts. There's already a table occurrence called Contacts in your relationship graph.

 

Add a global calc field (i.e. a calculation field with global storage) called ConstantOne, with a calculated value of 1. (This is because global fields can be reset; a calculated field with a constant calculated value is more reliable.)

 

Add three Boolean fields, IsCustomer, IsSupplier, IsAgent.

 

Go to the relationship graph. Add three table occurrences of Contacts: Customers, Suppliers, and Agents.

 

Create your three relationships:

Contacts::ConstantOne = Customers::IsCustomer

Contacts::ConstantOne = Customers::IsSupplier

Contacts::ConstantOne = Customers::IsAgent

 

et voilà.

 

Aside from searching the forum, here's some recommended reading.

[LingoJango]

The advantage of using Boolean fields for this sort of purpose is that I can use a single global field ConstantOne to create any number of significant relationships. Also, calcs testing for that condition are very straightforward, and sorting is fast. The disadvantage is storage.

 

The alternative is a single stored text field that allows multiple values from a value list of:

Customer

Supplier

Agent

 

Then you have three global calc fields with text output (important!) with a calculated value of "Customer", "Supplier" and "Agent" respectively, and set up the relationship that way. Calcs testing for the condition could get tricky but you'll get help out here.

[bdenny]

Thanks so much. That's really helpful.

 

I just couldn't get my head around these global fields, as they feel like constants, but i couldn't see how to give them a value! Your example makes it much clearer. I'll give it a go.

[LingoJango]

You would use a regular global (i.e. not a calculated one) for a relationship that was not constant. For instance, if you didn't have a separate Addresses table (which I would recommend), you could have a global field called GlobalFindCountry where you might enter a country name, so that you could use a portal to display all records where AddressCountry = GlobalFindCountry

 

To enter data in a regular global field you can simply type or use whatever you would use to enter data in a regular field.

[bdenny]

That's great.

 

The server seems to be down on that additional reading material. I was hoping not to have to bother you again.

 

I've set up my database as you suggested, but I'm a bit confused about what I now put on my contacts screen. The three "Is_" fields need to be added, but I want them to be check boxes rather than edit fields with values of 1 or blank. To do this I guess I need a value list, but what do I link it to? Is there another way of providing a neat solution to this?

 

Cheers. . .

 

Bruce.

[bdenny]

I misread the following;-

 

Go to the relationship graph. Add three table occurrences of Contacts: Customers, Suppliers, and Agents.

 

Create your three relationships:

Contacts::ConstantOne = Customers::IsCustomer

Contacts::ConstantOne = Customers::IsSupplier

Contacts::ConstantOne = Customers::IsAgent

 

as:

 

Create your three relationships:

Contacts::ConstantOne = Customers::IsCustomer

Contacts::ConstantOne = Suppliers::IsSupplier

Contacts::ConstantOne = Agents::IsAgent

 

Which seemed to make more sense, but it doesn't seem to work. I don't quite understand why all the relationships should be to the Customers occurrence of the contacts table.

[LingoJango]

Duh. My mistake, of course.

[bdenny]

OK I've got it working now. I forgot to put the "Only include related records" criteria on the value list.

 

I would still like to know how to tidy up the contact screen though.

 

cheers. . .

[LingoJango]

That's great.

 

The server seems to be down on that additional reading material. I was hoping not to have to bother you again.

 

Too bad. Keep trying. You'll still be coming back to the forum, though - but when you've read the text you'll have a better grasp of how FileMaker works.

 

The three "Is_" fields need to be added, but I want them to be check boxes rather than edit fields with values of 1 or blank. To do this I guess I need a value list, but what do I link it to? Is there another way of providing a neat solution to this?

 

As I pointed out earlier,

 

FileMaker doesn't have a Boolean field type setting; just make it a number field, create a value list called Boolean or One with a single value of "1", and require the field to validate as a member of this value list.

 

You can use this same value list for your checkbox. All you need to do is make the field small enough on the layout to hide the number 1.

[bdenny]

That's really cool. . . although not at all obvious or logical.

 

Thanks so much for all your help. I've learnt so much. I really appreciate it.

 

Cheers. . .

 

Bruce.

[LingoJango]

What I've learned about IT in general is that flexibility and simplicity usually trade off each other. FM provides a lot of flexibility in terms of field content, but that can make data quality somewhat poor, so if you want to make sure your db will work properly you have to tighten it up yourself with validation requirements, auto-enter calculations and the like.