Entering data in a calculated field in a portal

[pingswept]

Hi all,

I've got a database of people, and I want to display a list of addresses (home, work, etc) for each person, related by an ID number. Because addresses have lots of different formats, it seems like the best way to do this is to enter the data in several fields, and then concatenate them with a calculation that does some formatting and leaves out empty fields. I've got this working, and I can display addresses in a portal nicely.

Here's the problem:

To enter data, I have to switch to my addresses layout, remember and enter the related person's ID number, and enter a new address.

How can I enter a new address on the original page with the portal if only the calculated field is shown? I tried adding each individual field in a separate box labeled "Add new address." That works for the first address, but I can't add a second.

Any suggestions?

[dwins]

A script can take you to the address layout, add a new record, set the key field, make your coffee and whatever else. Nothing to remember - just click.

However, since a calc field will have to be stretched to size to accommodate all the multiple address data anyway, why have it. Why not just put the the address fields themselves into the portal.

That said, if you are concatenating, consider just merging the address fields into a text box - same result, no extra calc field.

And I'll go a little farther here. I realize that on this discussion board this suggestion is blasphemy. But, You have given no other reason as to why your address data is in separate fields to begin with. If all you want to do is to see it, and it's convenient to see it together in one field, why don't you just type it all into one field? You'll greatly simplify your database, speed up data entry, make virtually unlimited the number of adresses a person can have. Think of the design possibilities - one address showing from the text box, then click and all ten addresses, and some driving directions, unroll. I hope I don't get hit by lightning.

David

[pingswept]

Using a script to move to the other layout is pretty good. I hadn't thought to use a script to set the key field-- that's great.

I don't know much about this discussion board, so I don't really get what was blasphemous about your suggestion. However, the reason I created the calculation field was that I want to be able to search and sort by town (for example). Can I still do that if the address is just a block of text? It seems like FM would have to scan through all of the text in every address, which I fear would be slow. Am I mistaken here?

[Ender]

Hi pingswept,

It looks like you found one of the reasons it's preferable to use separate fields for the parts of an Address: that of being able to find and sort by one specific part, like Town. If the street name, town, state, etc were all in one field, this would not be possible. There are also times when you may only need one part of that address to show, or you may want to format the address in one line for a list view, but several lines for a mailing label.

As for solving your current issue, I'm afraid your structure is not entirely clear to me, so it's hard to give a quick answer. I'll just suggest that a good way to handle multiple Addresses is to use an Address table related to your Contact table by ContactID, and have only one set of address fields in that Address table, along with an Address Type field to distinguish between those that are Home, Work, etc.

Since you can't enter your addresses into a calc field, have the data entry portal use the separate Address, Town, State, etc, fields. If necessary, use a calculated field (that trims out the extra spaces) for those view only layouts and print forms. By entering the Addresses directly in the data entry portal, the creation script may not be necessary (just have the 'Allow creation of records in this table...' option selected for the relationship.)

[pingswept]

Hi there Ender,

Thanks for the explanation. Using the calculated field just in view only layouts and print forms is a decent idea. It makes the data entry portal a little ugly, but what the hell-- you can't have everything.

Maybe I'll try that, and also try the script option and see which is easier to use.

Pingswept