Text Merge Calculation

[Basso Tim]

I have been trying to create a calculation field without success- I would like to have the first and last names in a contact table appear in a FirstLastName field. The calculation that I have tried is: "FirstName" & "LastName"

but that does not seem to work.

Can you help?

[Raybaudi]

The right calc ( result: text) is:

FirstName &" "& LastName

(if you wish a space between)

[Lee Smith]

Hi Daniele,

I'll clarify this just a tad so that a Novice can see it clearer.

[color:blue]FirstLastName ([color:red]calc, [color:orange]Text Result) [color:blue]=

FirstName &" "& LastName

Note to Tim:

When you use Fields in a calculation, do not use quotes like you did.

I.e. you wrote:

"FirstName" & "LastName"

should have been like this:

Firstname & LastName

And as Daniele said, to add a space you add the " "& to it, or like this:

FirstName &" "& LastName

HTH

Lee

[Basso Tim]

That is great- thanks!

The other problem was (and I am still trying to understand how it is done) how do I populate the field? I added this formula to a pre-existing database and the field remained empty.

Chuck Melton at Filemaker tech help has been kind enough to help me through some of this. His response was:

The set up is fine. The issue is that your auto enter calc does not have any values populated into it. A quick fix it to change it to a calculation field and then back to an auto enter calc.

Can you help me understand how to flush the database in this way?

[Lee Smith]

It shouldn't be an Auto Enter Field, only a normal Calculation Field.

I sent along a Sample for you to look at

4_BassoTim_.fp7.zip

Edited April 22, 2006 by Guest

[Basso Tim]

I think I got it! I was going crazy looking at the options for a text type and did not think about the type being changed to calculation.

But going forward as I enter names wouldn't I want it to be an auto-enter field?

Lee- where would the sample be? I'm new to this forum, sorry.

Edited April 22, 2006 by Guest

[comment]

The only reason (that I can think of) to make it auto-enter is if you need the option to manually override the calculation's result.

[Raybaudi]

But going forward as I enter names wouldn't I want it to be an auto-enter field?

Not for this type of calc...I mean FirstName&LastName.

AutoEnter a calc into a text field gives you the possibility to EDIT the result.

[Raybaudi]

Hi comment ;)

[Lee Smith]

Sorry, I forgot to click the ADD button and Clicked Finished instead.

Slow night huh boys

[Basso Tim]

Thanks again for your help!

In this case I don't need to alter the end result because I am using it to populate a value list which will display both first and last names, and then because the ContactID (from the reference table) = FirstLastName the portal will be able to choose a record from the contact table. Have I got that right?

What do you call a reference table in FileMaker, i.e. the middle table which references two other tables making a many-to-many relationship?

I will want the ability to auto fill in the field because the list is long.

[LaRetta]

The only reason (that I can think of) to make it auto-enter is if you need the option to manually override the calculation's result.

In this example yes. But I believe there is another merit to using a standard field over calculation (in some instances). Auto-Enter (Do not Replace) is the ONLY option which will plant the result and then never change even if data in any referenced field(s) changes. Both Auto-Enter (Replace) and calculated field (whether indexed or unstored) WILL change if referenced values change (if within the same record).

So if you wish to plant data based upon other fields' ORIGINAL value and then remain static, Auto-Enter (Do Not Replace) is unique in that aspect and that distinction can come in quite handy.

LaRetta :wink2:

Edited April 22, 2006 by Guest

[Basso Tim]

I would want the values to change only when they are changed in the contact table not when they are referenced through the portal. In this case which method is best?

[LaRetta]

You want a calculation. And you WANT it to change based upon changes made to FirstName or LastName; otherwise, when a User corrects spelling in the Contact table AND if you had it set as Auto-Enter (Do Not Replace), it wouldn't change! So again, you want a calculation (which can then be indexed and will change as either referenced field changes). Auto-Enter (Replace) would work also but, as has been mentioned, it isn't necessary here.

BUT this concerns me ...

... am using it to populate a value list which will display both first and last names, and then [color:blue]because the ContactID (from the reference table) = FirstLastName the portal will be able to choose a record from the contact table

The value list should be based upon the ContactID otherwise you may have duplicate Bill Brown or Joe Smith. If you base your value list on ContactID, you can (from the value list options) check 'Also Display Values from Second Field' and select your FullName here. And then check 'Show Values ONLY from second field.' This will protect from incorrect name selection if there are duplicates.

And by using drop-down LIST, you can click 'Auto-complete using Value List' and it will filter as User types first characters of the name.

LaRetta :wink2:

[Basso Tim]

Hi LaRetta,

Thanks for your input! This is fun problem solving on this forum.

I had a problem leaving the FirstLastName field a calculation- it didn't seem to populate the value list correctly, I'm not sure why. When I changed it back to a text field it did populate the value list.

As to your second suggestion (and it makes absolute sense to me) the problem is that what then displays on me photo layout after the contact name has been chosen is the ContactID which isn't what I had hoped for- I wanted the users to be able to see the name of the persons in the photo.

if I leave the ContactID to show FirstLastName that is what also shows up in the reference file...which is not what I expected...I had hoped that the ContactID would nevertheless show up there. I could change the settings so that the number is displayed there, but what is the actual data being stored in the reference file?

Suggestions?

Edited April 23, 2006 by Guest

[LaRetta]

Make sure that calculation is NOT unstored. FM needs to be able to index it. And also make sure the calc box shows 'Calculation result is TEXT.'

As to the ID displaying, there was a clever trick from Comment awhile back ... but I can't locate it now and I wanted to use it too. IIRC, it allowed selection of the ID then the REAL ID field took it and the popup field became the name (I think). But try as I might, I can't get the auto-enter calcs right when trying to replicate it. It looked so logical at the time and I KNOW I put it somewhere important so I would remember where I put it.

Well, until I find it, my old process is just placing the name next to the popup (and turning off entry to it in Browse). Maybe someone else remembers or maybe Comment can remember.

LaRetta

[comment]

IIRC, it allowed selection of the NAME into the ID field, then the ID field replaced the name by looked-up ID - but this is now a native feature of version 8.

[Basso Tim]

So how would that work in my example? The value list is populated with the calculation field FirstLastName from the contact table. When a name is chosen it that entry ends up in the reference table which should be the ContactID (and the PhotoID). What gets put there however is the the full name from FirstLastName.

It does reference the correct record because I can make the ContactID be show there instead- but what is the actual entry in the reference table?

I need it to be the ContactID so that I can export it to a mysql database.

[Basso Tim]

Upon experimenting with a looked-up ID, it now places the result of course into the portal of the Photos table. This is almost the same result as if I had created the two field value list option which then put the ContactID number into the reference table (which is one of the things I want!)

Now what I am thinking is that either of these methods are good to place the ContactID in the reference field and then set the display of the field to be FirstLastName from the contact table. This way the underlying data will be the ID and the displayed data will be the name associated with the ID. Sound good?

Sounds good, but it doesn't seem to work. For some reason the field does not display FirstLastName either as a calculation or a text field or even a last name from the contact record. Hmmm....

[LaRetta]

I believe you're getting twisted in the logic.

Relationship should be ID to ID. Value list should be based upon ID NOT FullName. When selecting the value list (as I've outlined in a few threads above where I quoted my concern), you THEN select a second field for display - which is your FullName calc (indexed and type text). Select only display the NAME checkbox at the bottom.

But I interpreted what you wanted was for the FullName to display where the ID displays. This would involve trickery (which is why Comment's idea came to mind, smile). If this is for printing, take the ID field and turn off display of it (select the ID field, then Format > Sliding/Printing and check 'Do not Print').

And just place the FullName next to the popup like everyone else does.

An aside ... my Users don't like seeing the ID either. FM was wonderful to add this feature of displaying only the second field but after leaving the field, the ID displays. This tells my Users nothing (and they also think it's broken). The name should CONTINUE to DISPLAY even though the ID is in the field. I hope FM adds an ability to field format (in value list setup portion) to check 'Display Second Field' instead of the ID.

LaRetta

[comment]

If the field is formatted as Pop-up Menu, the name will continue to display after leaving the field.

[Basso Tim]

This solves several issues- thank you!

I have been playing with creating a second portal next to the first which would display the name associated with the ID- not a bad idea, huh?

So I created a ContactName look-up in the reference table. Unfortunately this did not seem to work, either with the ID being a calculation or as a text field.

I have now changed it so that the ID and name displays from the value list- thanks!

It is too bad that the drop down/edit box cannot auto complete when there are two fields being referenced, but I understand why that option is greyed out.

I'm new to Filemaker so forgive me if I don't know its limitations or standard practices yet. :crazy2:

[LaRetta]

...so forgive me if I don't know its limitations or standard practices yet

Obviously neither do I.

Being Windows, I've always used popup menu. But I switched to popup lists because of the auto-fill when we moved to vs. 8. I didn't know popup menu continued to display the name.

But why are you using portals to display the name? Have you tried just placing the field directly? What you keep describing seems way over-complicated ... something I'm an expert on. And auto-complete doesn't work on menu - that's why it's greyed - not because you are displaying both ID and name.

[Basso Tim]

No, let me be clear- the auto-complete function works only when you have a single value list field (not ID and Name for example).

The problem with going at it directly and not through a portal is that there are several contacts which are identified in the photos and I wanted to try a way which would automatically display the IDs- I thought that a look-up field might work in this way. Is there a way to create a calculation which makes the new field:

calcultedname= FirstLastName from contact table using the photographed table's ContactID

Sounds crazy, no? I know I'm over-complicating!! Yikes.

Chuck Melton (from Filemaker tech help) also replied to a previous question:

"Another quick question which came up on the forum: someone said that I should leave the FirstLastName as a calculation in the contact (alumnus) table, but that didn't seem to populate the Value list correctly. You switched it back to a text field. Was there a reason for that?"

His response was: "The reference table is actually going to store the same value as FirstLastName because it is automatically creating the related records through the relationship. The reason I switched the value from text to calc to text was in order to get the autoenter data to populate."

[LaRetta]

Popups in vs. 8 ... ah, we jumped into 8 and I began using them. But I haven't gone to bed with them yet. I will do that today.

It would help to see your file. There has been so much on this thread that I'm unsure of 1) what you have, 2) what you need and 3) what changes you've made so far. So your file with brief explanation of the use (from User perspective) should suffice.

Two over-complicator's are a dangerous combination but even *I* can usually spot wonkies, convolutions and overkill ... it's only in MY stuff that I wear blinders. :giggle:

[Basso Tim]

Here is a clone of the database- the file was too big to send it with records. For the benefit of the forum I changed AlumnusID to ContactID in the discussion. It seemed to me that in its generic form it might be more relevant to others with similar problems.

This is a work in progress!

Thanks! If you would like the populated db just send me an email and I'll send it to you.

wvaadbClone.fp7.zip

[Basso Tim]

An interesting side note- I have been reading the Missing Manuals FileMaker Pro 8 book and they say

"Of course showing the first and last name is the best way to find the right customer but the dialog box lest you pick only one field"

I'm glad that I didn't think that what we did was not possible before we figured out how to do it! I'm going to go to their website and report our work-around.