Merging Fields

[npaadmin]

Hi Guys,

I have another question for you all.

I want to have one field that pulls information in from 10 other fields withing my database.

How could I create something to do that?

I've been tooling with it, but haven't had any luck.

So basically, something like this.

FIeld A - First Name

FIeld B - Last Name

Field C - SSN

Field D - Phone Number

Then, Field E would be show all that information together in one text box.

First Name, Last Name, SSN, Phone Number.

How would one get a field to grab all that information and display it?

Thanks in Advance,

[dreamingmind]

npaadmin,

An auto enter calculation that will replace field content can be used here. And it will update itself whenever one of the referenced fields changes (as long as the field is in the same table as the calculation.

FieldA & " " & FieldB & "¶" & FieldC & "¶" & FieldD

You can concatenate any extra stuff you want. In the example above you would get something like:

John Doe

555-555-5555

(555) 555-5555

You can also use case statements to verify that certain parts have content, then build your concatenation appropriately.

Regards,

Don

[comment]

An auto enter calculation that will replace field content can be used here.

Yes it can, but why should it be, when the content is not likely to be edited? A calculation field would be more appropriate.

If there's no need to watch for empty fields, a text object with merged fields can provide a solution with no extra fields required.

[dreamingmind]

Well, there's no particular requirement that it be done in the specific way I described. My suggestion was based on many assumptions that may not be correct in a given situation.

I only suggest the following justification based on my sense of FM behavior, not from an under-the-hood expertise:

The auto enter calc described will outperform a live calculation when a large found set is pulled because the data in the calc field is stored and is stable until a referenced field changes causing it to update. A live, unstored calc would cause all the found records to evaluate.

And:

Given the data in the example, though change may not be common, people do change their names and phone numbers do change, so allowing the calc data to change would be desirable.

Depending on the situation, a merged text object on the layout as you describe might be just the ticket.

[comment]

A calculation field can be stored or unstored. If it's stored (as it will be by default), there will be no performance difference between it and a text field with auto-entered calculation. If you deliberately choose to make it unstored, you will save on storage space at the expense of a slightly slower screen redraw. I don't think the difference will be noticeable unless you're displaying a very large amount of records at once.

people do change their names and phone numbers do change

Yes, of course they do - and the place to record such change is in the original fields. A field with auto-entered calculation allows a person to have one name in the Name field and another in the concatenated field. This is sometimes desirable (auto-enter some default value and allow user to modify it), but it hardly seems a good choice in this case.