Hi. I have an address field that often contains more than 1 line. I wish to split it into 3 separate fields (as no record has more than 3 lines). I have managed to get the first line out (see below for calculation - prob. v. clumsy) but can not work out how to do the other 2. Any ideas? Thanks

If(Position(Home Address 1, "¶", 1, 1)=0, Home Address 1, Left(Home Address 1, Position(Home Address 1, "¶", 1, 1)))

First off should you know that you indeed are on the right track, but that boolean logic differs a little from what you learned in math-class. In filemaker everything not zero logical true so your calc can be rewritten this way:

If(

Position(Home Address 1; "¶"; 1; 1);

Left(Home Address 1; Position(Home Address 1; "¶"; 1; 1));

Home Address 1)

Then to your question regarding the second half of the data, here could you not exploit GetValue( yet, so we have to do it with the tools availiable in your version:

If(

Position(Home Address 1; "¶"; 1; 1);

Replace ( Home Address 1 ; 1; Position(Home Address 1; "¶"; 1; 1) ; "");

"")

...so what it does is if a pilcrow exists replace everything to the left including the pilcrow with void.

--sd

Thanks very much.

So I use that formula for Address 2?

And then something similar for Address 3 (substituting Address 2 for Address 1 in the calculation).

No danger of overwriting the Address 1 field? Replace sounds dangerous to me!!

Danger is if the field value is stored, but nothing requires a calculated value to be stored as long as it can be shown as unstored. But the basis of the calc is a 1NF violation, requirering atomic values stored in fields - so to abide must the fields be broken out ...exported to a related table although it might seem silly in this case.

If a third address exist do you need to right truncate address 2 further. But address 3 should not be based upon the previous calculation, but instead of the original data. What should be avoided here is calc's depending on other calc's values.

--sd

Just as a precaution whenever you are looking to change data you should:

Back up!

Back up!

Back up!

Then change!

It is just a good habit to get into.

There's no danger when the original field is left untouched, and the parsing is done in calculation fields referencing the original field. I fail to see any difference between a stored calculation and an unstored one in this aspect.

I would make the calculations stored, and after being satisfied that the transformation was succesful, I would change them into regular text fields - thus making the values permanent. Then the original field can be deleted.

Of course, backup is always good strategy.

TY all. I think the field that picks out needs amending (to lose not only the text before the first para mark, but also after the second one) but I am not sure how to do that.

Nor the field to pick out the third line if it exists.

Any ideas welcomed - ty.

I fail to see any difference between a stored calculation and an unstored one in this aspect.

So do I when reading my scriplings, Indeed the intention was not to touch the original data in the first place!

--sd

I think the field that picks out needs amending (to lose not only the text before the first para mark, but also after the second one) but I am not sure how to do that.

There is no need to have this amendment in the original text it could be added to the "argument" in the calc! Say some pilcrows here and there and the original thing pops up in a different field.

--sd