Field that takes info from other fields

[topaznz]

Hi there Forum

Here is my conundrum:

I have a field called "Full name" will takes info from "Title", "First name", "Middle name", "Last name". So in the "Full name" field I have the text calculation:

"Title & " " & First name & " " & Middle name & " " & Last name

Some of our clients do not have a middle name, but with the calculation as it stands, a space appears between the first and last names.

Very petty, but would be interesting to see if its fixable?

Cheers for taking the time!

Tim

[mr_vodka]

Look into TrimAll ()

[Raybaudi]

Try:

Substitute ( List ( Title ; First name ; Middle name ; Last name ) ; ¶ ; " " )

[LaRetta]

John is right, Tim, except all you need is to add Trim(), I think, so it could be:

Trim ( Title & " " & First name & " " & Middle name & " " & Last name )

Using Substitute() and List() is overkill.

[mr_vodka]

TrimAll is needed since it is in the middle of the string. :(

[LaRetta]

Oh right, of course, John! :laugh2:

[topaznz]

Thank you so much Daniele, LaRetta and John. In the end I pasted in Daniele's solution and away it went. There are many parts of my system I can implement the same formula.

Many thanks again everyone.

[Raybaudi]

c = click

dc = double click

l = Length ( formula )

----------

Substitute ( List ( Title ; First name ; Middle name ; Last name ) ; ¶ ; " " )

dc + dc + dc + c + dc + c + dc + c + dc + c + dc + c + c + c = 7 * dc + 7 * c

[color:red]l = 78

----------

TrimAll ( Title & " " & First name & " " & Middle name & " " & Last name ; 1 ; 1 )

dc + dc + dc + dc + c + c + dc + dc + dc + c + c + dc + dc + dc + c + c + dc + c + c + c + c = 11 * dc + 10 * c

[color:red]l = 82

----------

Now, which is quicker ?

I suspect ( I'm NOT going to try ) that the TrimAll function is much slower than the Substitute.

[David Jondreau]

I'm baffled.

[bruceR]

Speed? When will it ever matter? Either calc is trivial and will be performed instantly.

[Raybaudi]

Suppose that we have a 100.000 records DB.

Now we create that calc field...

Which one ends the evaluation quicker ?

BTW: this is the unique test that ( eventually ) can give right to who writed: "Using Substitute() and List() is overkill"

[LaRetta]

You cannot time when FM stops evaluating a calculation. But you CAN time FM setting a field with either calculation.

Your example that it takes more clicks to create TrimAll() calc vs Substitute() just doesn't hold water. Either way, you must type a semi-colon (on yours) or & " " & on the other. Either way, you would copy and then just paste between each field insertion.

But you DO have a point about speed. Attached is a file just finished which shows a comparison and using Substitute() makes a bit of difference in 25,000 records.

Well I have a test file to prove it but forums will not accept it. Results on 25,000 records ... Trim() all takes 21 seconds; Substitute() takes 18.

I saw your additional sentence, Daniele. No need to be sassy; simply giving me time to check your facts is sufficient.

Edited April 10, 2009 by Guest
Removed dead file

[David Jondreau]

1) I don't know which is actually faster. I don't understand your analysis with clicks and double clicks. One calc uses one function, the other two functions. I think the only way to see would be to test. And I don't think it's worth my time to test. In fact, I'm surprised I'm spending time writing this post!

2) If you're developing locally, on a db with a large data set, after creating the calc, one may take, maybe, 30 seconds longer. And then any new record or change to a current record, will, as Bruce says, take a trivial amount of time to update.

Personally, I like TrimAll(), it's my function of the month, thanks to comment. But, Daniele, I think your solution is fine too.

There is, after all, more than one way to skin a concat.

Edit: Looks like this is a busy thread! No intention of stepping on LaRetta's toes.

Edited April 10, 2009 by Guest

[LaRetta]

There is, after all, more than one way to skin a concat.

Well phrased! I thought like you, DJ, about using two functions vs. one and that's why I said it was overkill. But I'm glad it was brought up because if I was setting a field instead of using a straight calculation field, I would surely use Daniele's method!

UPDATE: The file shows it clearly; wish I could attach it. Oh. And I don't have toes, only a desire for truth. :wink2:

Edited April 10, 2009 by Guest
Added update

[comment]

I suspect ( I'm NOT going to try ) that the TrimAll function is much slower than the Substitute.

Maybe you SHOULD try it, before starting unfounded rumors. I believe you will find it's better to count the number of evaluations instead of the number of clicks it takes to enter a formula.

IMHO TrimAll() is a better choice here, simply because the purpose of the calculation is clear at first glance.

[comment]

Busy thread indeed. LaRetta, I look forward to tearing apart your test - because TrimAll() **is** faster (though not significantly).

[LaRetta]

I was quite surprised as well, Michael, because Daniele's would certainly take more evaluations. And as calculation, I would still use TrimAll(). And there is no way to test evaluations on a calculation, ie, create one calc and then leave Field Definitions ... I can't figure out how to timestamp between when saying OK to the calc and FM returning from Field definitions.

I'll attempt to attach again but with only 5,000 records. To get a large enough set, you could export as Mer and keep importing; duplicates wouldn't matter. If you can find another reason my results are different than expected, I'd be happy either way. Test considerations: Both scripts are identical except for the calculation. Indexing is at none on all fields and the new field being set has indexing off and unchecked 'Auto create index'. Regardless, both times I signed off of file before executing either one. I didn't freeze the window because I wanted the time differences (if any) to be exaggerated and identifiable with smaller set of records.

Is there something in my testing theory that I am missing?

TrimAllvsSubst.zip

[LaRetta]

Maybe it is adding all the spaces between each field which is slower than List(). Maybe my test is twisted because we each have TWO actions happening and not one:

A) Add space between each field then TrimAll()

Add carriage return then Substitute()

I will adjust my test criteria and split the actions into two pieces so we can isolate TrimAll() and Substitute(). I think this may be my thinking error in the test.

[LaRetta]

That is exactly the case. If I use TrimAll() on the concatenated field which already has the spaces, it is faster than applying Substitute() to a field which already has the carriage returns.

TrimAll() ***is*** faster. But what does that tell us overall when used in this context particularly if setting a field with the original calculations?

UPDATE: I neglected to give you time results. On 30,000 records TrimAll() by itself took 17 seconds and Substitute() by itself took 18 seconds on repeated identical tests.

Edited April 10, 2009 by Guest
Added update

[comment]

I ran your test a couple of times, and got identical results for both (not counting the odd second difference here and there due to rounding). But when I ran just the calculation in total isolation (see attached), TrimAll() was consistently faster - though only by a hair.

OTOH, it could be said that my test is skewed towards the specific data I have picked, while yours is more realistic, taking a variety of data combinations into account.

I'd call it 'too close to call', and leave it at that.

TrimAllVsSubstituteList.fp7.zip

[Raybaudi]

This is in reply to all my friends ...

"There is, after all, more than one way to skin a concat."

This is so true expecially with FileMaker but, in this post, LaRetta said something against my calculation that gives however an exact result.

I have anything against the Trim ( ) function but, as I said, I thought that TrimAll ( ) was slower than the Substitute ( ) because it has to evaluate:

1) the last char of each word

2) how many spaces there are before another char and ( eventually ) reduce them to only one

I believe blindly in comment's test.

[LaRetta]

I believe blindly in comment's test.

Comment's test did NOT take into account multiple fields nor the volume of records mine did. I supported your calculation.

But not to worry. I will never support you again regardless of the outcome but I will STILL speak up against ANYONE when I believe I disagree with them.

Comment, you have been sucked up to. I hope you enjoy it. I have been trashed, not because I disagreed, but because someone does not like me. So be it. If you would have repeated MY test with MY file instead of creating your own, you would have gotten the same results.

I stand by my stance ... TrimAll() is indeed faster than Substitute() as you have said. But Daniele's overall calculation in THIS instance is faster.

[comment]

If you would have repeated MY test with MY file

Ahm... let me clarify something. When I said:

I ran your test a couple of times, and got identical results for both

I meant I ran YOUR test using YOUR file.

[Raybaudi]

But not to worry. I will never support you again regardless of the outcome but I will STILL speak up against ANYONE when I believe I disagree with them.

I agree and wish.

That is the only way to learn more and to enjoy togheter.