Merging DB fields...

[etiennelj]

Hi!

I just began using FM9 on WinXP and I have a real newbie question...

How can I take two DB fields and merge their content into only one new field? I want a solution that affects the DB for once and for all, not only the display.

Only one of both fields contains data for each entry, so there will be no data overlap issues.

Thank you!

[aldipalo]

Welcome to the Forum.

Not sure I totally understand what you want to do. If you have fieldA and fieldB and you want to display them as fieldC then you would concatenate the field. Set up fieldC as a calculated field and enter the calculation:

fieldA & fieldB. So now fieldA = John and fieldB = smith. FieldC = JohnSmith.

If you want to display John Smith the calc would be:

fieldA &" "& fieldB.

You can search on a calculated field, but you can not enter data into a calculated field.

You can also set the field to a Text or number field and enter an 'Auto Enter calculated value' which you can change from your layout.

If this is not what you are looking for please explain further.

hth

[mr_vodka]

If this is a one time thing and you have fields with sample data such as FieldA (Bob) and FieldB(blue) and you want to get rid of one field by merging it with onother field, you can do a replace field contents.

Replace Field contents of FieldA with a calc like.

FieldA & Case ( not IsEmpty ( FieldB ); ¶ & FieldB )

Be careful when you use Replace field contents as it is not undoable.

[etiennelj]

Thanks!

I think the second option could be the correct one, since it's a "one-time" only operation... Not a display thing, but a DB "once and for all" modification...

Ex:

Actual DB:

Id - - - - -Vegetables - - Fruits

0 - - - - - potato

1 - - - - - turnip

2 - - - - - - - - - - - - - - -apple

3 - - - - - celery

4 - - - - - - - - - - - - - - -orange

5 - - - - - - - - - - - - - - -grapefruit

6 - - - - - onion

Wished DB:

Id - - - -Vegetables&Fruits

0 - - - - potato

1 - - - - turnip

2 - - - - apple

3 - - - - celery

4 - - - - orange

5 - - - - grapefruit

6 - - - - onion

Voilà!

I think this explanation is more visual and easier to understand. How can I achieve that?

Thanks!

[mr_vodka]

I wouldnt use an ampersand between Vegetables&Fruits if possible. Why not a comma or a space?

Trim ( FieldA & " " & FieldB )

BTW What is the purpose of combining these fields as such?

[comment]

If I read this right, it should be just =

Vegetables & Fruits

[etiennelj]

Let's just call the new field "Vegetables_Fruits"...

The veg and fruit example is just an example... I need to create this new field combining the two old fields because they contain the same kind of data...

How could I achieve this?

This formula would work?

FieldA & Case ( not IsEmpty ( FieldB ); ¶ & FieldB )

Thanks!

Edited June 27, 2008 by Guest

[mr_vodka]

We are going around in circles here. If you need to create a new field, then you can do as Al & Michael said. If you need to replace one of your fields, then you can do a replace as I said. The replace calc that I posted used a pilcrow (carriage return) as the delineator between the two values.

[comment]

Since you say each record has data only in ONE of the two fields, you don't need any delimiters or conditions.

Here's a safe way to try this: define a new calculation field (result is Text) =

FieldA & FieldB

Put this field on the layout and examine the results. Once you're happy, change the field type from Calculation to Text. Check again, and if everything is OK, you can delete the original fields.

[mr_vodka]

Only one of both fields contains data for each entry, so there will be no data overlap issues.

Ah I missed this...

[etiennelj]

Thanks guys!