Value Lists, portals, and lookups

[cschmitz]

=====

I have two databases, Contacts and Invoices. They're separate database files (I'm currently working on a solution that will contain all of the database tables into one file but for the time being I need to patch up the existing solution).

 

Contacts holds Customer information. I have a portal within contacts which is related by company name to a table called Addresses. In the portal there are fields for Address Type, Address1, Address2, City, State, Zip. The address type field has a value list that consists of Billing, Shipping, Mailing and has the Other option enabled so that the user can add any additional types of addresses.

 

The Invoice database holds sales information. When a new invoice is created the customer's addressing information has to be added in. Contacts and Invoices are related by Company name AND by address type. The address fields in Invoices are lookups to the Addresses Table. When the user types in the Company name and then selects the address type for that company it pulls in the needed address.

=====

 

This all works fine. There's just one thing I have a problem with.

 

The value list for the Address Type field in Invoices is set up to use the value list Address Type from the Contacts database. This is fine, but if a user uses the Other option in Contacts to add an additional address, the new address type does not show up as an option in the Invoices Address Type drop down list. Is there any way to get the additional value list types to show up??

[David Head]

The value list for the Address Type field in Invoices is set up to use the value list Address Type from the Contacts database. This is fine, but if a user uses the Other option in Contacts to add an additional address, the new address type does not show up as an option in the Invoices Address Type drop down list. Is there any way to get the additional value list types to show up??

First of all, let me clarify that you are not actually adding a new address for a Contact via the Invoice? Because all addresses should be added (and edited) via the Contact record.

 

So what I think you want is for all defined address types for any given contact to appear in a value list when creating an invoice. So this should be a separate value list to the one you have defined for contacts. It will be a value list based on a relationship. You should have relationships as follows:

 

Invoice >---- Contact ----

 

So the "Invoice_Contact_AddressType" value list should show all the address types that exist for the selected Contact. So you select values from Address::Type and Include only related values starting from Invoice.

 

See how that works for you.

[cschmitz]

First of all, let me clarify that you are not actually adding a new address for a Contact via the Invoice? Because all addresses should be added (and edited) via the Contact record.

 

So what I think you want is for all defined address types for any given contact to appear in a value list when creating an invoice. So this should be a separate value list to the one you have defined for contacts. It will be a value list based on a relationship. You should have relationships as follows:

 

Invoice >---- Contact ----

 

So the "Invoice_Contact_AddressType" value list should show all the address types that exist for the selected Contact. So you select values from Address::Type and Include only related values starting from Invoice.

 

See how that works for you.

 

Well, I tried it out. I think I'm getting the logic but for some reason its not working. The thing is, the address type has to be noted in the record for the address table, but outside of creating a script to populate a field in the contacts database with each new address type that is added, I'm not sure how to get that unique list of address types per contact name into the contact table. I can do the script, but I'd like to keep it as simple as possible if i can.

 

I tried creating a value list in contacts that shows data from the fields in the address table that are related to the contact. That works and it compiles a unique value list, but when I try to use that value list in the invoices database it shows up as a value list from an unrelated table even though it is related.

 

Blargh, here's the example databases that I built to test the solution out. I think seeing them (if you don't mind taking a look) will explain things better than trying to type it out.

[David Head]

Firstly, you have done a very bad thing - you are using CompanyName as a primary key. Never do this. instead, each record should have a unique ID such as a serial number to identify it. This also goes for your address records and you invoice records for which you have no unique identifier.

 

The problem you had was that in the Invoices file you were trying to use the value list from the other file. This should be a completely new value list based on a relationship.

 

However, the relationship you set up was not correct. Have a look at the relationship graph I have modified in the Invoices file. You don't need your address table occurrence that I have renamed.

 

Then have a look at the value list that I have modified.

 

This now works as expected. Hope it makes more sense.

[cschmitz]

Yes this makes a lot more sense and works beautifully. Thank you very much for the help. I went through my original example files and changed it so that I could better understand what you did and where I went wrong. I was assuming that the relationship between invoices-contacts-addresses was supposed to be in the contacts database which, of course, was wrong. I think after plugging away at it for a while and hitting brick walls my senses left me.

 

One thing, the table in the invoices that you renamed, adresseszzz, isn't that relationship necessary for the lookup of the addresses from the address table to work? Since the addresses being pulled in would be dependent on both the company name and the flag type, if that table and relationship are removed, the list of flag types still come though but the lookup of the addressees themselves have no relationship to be pulled from.

 

Normally I do put unique IDs in on the databases I create. I missed putting them in because I was trying to throw together an example quickly to get the idea across, but that is a basic design component that I shouldn't forget about even in examples. Thanks for pointing that out, I hadn't even realized I didn't put any into the example until you pointed it out.

 

Since we're on the subject, as far as unique IDs go I normally name a field contactID (or whatever the name of the table is) and then just leave it as a number that is incremented by one on creation and is not modifiable. Do you think this is an ok way of generating the ID or should it be a bit more descriptive (the number itself) to the table that its coming from? I'm still pretty new to filemaker (and to database construction itself) so I'm not sure what the best method would be other than starting at 1 and incrementing from there.

 

Thanks again for the help.

[David Head]

One thing, the table in the invoices that you renamed, adresseszzz, isn't that relationship necessary for the lookup of the addresses from the address table to work? Since the addresses being pulled in would be dependent on both the company name and the flag type, if that table and relationship are removed, the list of flag types still come though but the lookup of the addressees themselves have no relationship to be pulled from.

Yes you are correct - that is a reasonable setup to get the correct address looked up into the invoice record. smiley_cool Have you considered using auto-entered calculations instead of lookups? Interesting possibilities and the same result.

 

Since we're on the subject, as far as unique IDs go I normally name a field contactID (or whatever the name of the table is) and then just leave it as a number that is incremented by one on creation and is not modifiable. Do you think this is an ok way of generating the ID or should it be a bit more descriptive (the number itself) to the table that its coming from? I'm still pretty new to filemaker (and to database construction itself) so I'm not sure what the best method would be other than starting at 1 and incrementing from there.

I have some views on the setup and use of primary keys. smiley-wink First of all with naming, I think it is reasonable to name every primary key "ID". So every table you create has an ID field. The reasoning here is that the name is simple and whenever you see it referred to externally from the table it has the table occurrence name e.g. CONTACT::ID or INVOICE::ID. Then the foreign key in the other table can be named contactID to identify it accurately. I don't subscribe to the use of kp or kf in the field name - I don't see it as necessary. But I can see where a double underscore and single underscore is useful for prefixing primary and foreign keys respectively. This helps in the field sort order: __ID, _contactID.

 

As for using an incrementing serial number - absolutely. But don't bother with any prefixes to make it more descriptive. If you do that, then you will be tempted to use it in some meaningful way like as an invoice number. Resist that urge! A primary key should be anonymous/meaningless and not visible to the user. My two cents on the topic. So Cool!

[cschmitz]

Have you considered using auto-entered calculations instead of lookups? Interesting possibilities and the same result.

 

Hmm, no I hadn't though of using auto enter calculations. It seems like that would allow for a lot more options as far as info it brings in. What kind of stuff did you have in mind?

 

 

I have some views on the setup and use of primary keys. smiley-wink First of all with naming, I think it is reasonable to name every primary key "ID". So every table you create has an ID field. The reasoning here is that the name is simple and whenever you see it referred to externally from the table it has the table occurrence name e.g. CONTACT::ID or INVOICE::ID. Then the foreign key in the other table can be named contactID to identify it accurately. I don't subscribe to the use of kp or kf in the field name - I don't see it as necessary. But I can see where a double underscore and single underscore is useful for prefixing primary and foreign keys respectively. This helps in the field sort order: __ID, _contactID.

 

ah, the underscoring would definitely make the id stand out more (and I imagine always put it at the top of the field list when sorting by name). I think I will start just naming them just ID if they're primary. Now that I look at my record IDs they seem a bit redundant as far as naming